467,136 Members | 1,440 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,136 developers. It's quick & easy.

INSERT INTO error

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")
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.

Jul 22 '05 #1
  • viewed: 4109
Share:
3 Replies
Maybe this helps

Conn.execute ("INSERT INTO Table2 (TNumb, LogAct) VALUES (" & Clng(NT) & ",
'" & Act & "')")
or
Conn.execute ("INSERT INTO Table2 (TNumb, LogAct) VALUES (" & CDbl(NT) & ",
'" & Act & "')")

Jul 22 '05 #2
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.
Jul 22 '05 #3
Thank you very much for your detailed information.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ow**************@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.

Jul 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
6 posts views Thread by harborboy76@yahoo.com | last post: by
8 posts views Thread by 73blazer | last post: by
3 posts views Thread by MP | last post: by
3 posts views Thread by mahajanvit@gmail.com | last post: by
9 posts views Thread by anachronic_individual | last post: by
6 posts views Thread by rn5a@rediffmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.