"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OwEOd2pGFHA.2740@TK2MSFTNGP12.phx.gbl...
> Gtbntgar wrote:
> > Please can someone look at my SQL command it is not working.  It is
> > giving me a syntax error
> >
> > Act = request.form("Act")
> > NT = request.form("NT")
> >
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > DSNtest="DRIVER={Microsoft Access Driver (*.mdb)}; "
> > DSNtest=dsntest & "DBQ=" & Server.MapPath("/db/invent.mdb")
>
> Nothing to do with your problem, but ODBC is obsolete. Use OLE DB:
> DSNtest="Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("/db/invent.mdb")
>
> > Conn.Open DSNtest
> > Conn.execute ("INSERT INTO Table2 (TNumb, LogAct) VALUES (" & NT & ",
> > '" & Act & "')")
> >
> > The TNumb field is numeric and LogAct is text.  If I remove the TNumb
> > info it works.
>
> Nobody can debug a sql statement without knowing what it is. Do this:
> Dim sSQL
> sSQL = "INSERT INTO Table2 (TNumb, LogAct) " & _
> "VALUES (" & NT & ",'" & Act & "')"
>
> 'comment out these two statements when finished debugging:
> Response.Write sSQL
> Response.End
>
> DSNtest="Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("/db/invent.mdb")
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open DSNtest
> conn.execute sSQL,,129
> conn.close: set conn=nothing
>
> (the 129 is the combination of two constants: adCmdText (1) and
> adExecuteNoRecords (128). You should always tell ADO what the command type
> is, and whether or not the command needs to create a recordset to retrieve
> data, which it does by default unless you tell it not to)
>
> Look at the response-written string in the browser winow. If the problem
is
> not evident, copy it to the clipboard, open your database in Access,
create
> a new query in Design View, switch to SQL View, paste your statement in
and
> try to run it. You may get a better error message.
>
> I prefer this technique for running sql statements built in code:
>
> dim conn, sSQL, cmd,DSNtest,Act,NT
> Act = request.form("Act")
> NT = request.form("NT")
> sSQL = "INSERT INTO Table2 (TNumb, LogAct) VALUES (?,?)"
>
> 'validate Act and NT at this point. Then:
>
> DSNtest="Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("/db/invent.mdb")
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open DSNtest
> Set cmd= Server.CreateObject("ADODB.Command")
> cmd.CommandType=1
> cmd.CommandText=sSQL
> Set cmd.ActiveConnection=conn
> cmd.execute ,array(NT,Act),128
>
> conn.close: set conn=nothing
>
> This technique has several advantages:
> 1. No need to worry about apostrophes
> 2. No need to worry about sql injection
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>