Gtbntgar said:
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
