«

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.

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 & "')")

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 (12. 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.

Thank you very much for your detailed information.


"Bob Barrows [MVP]" <> wrote in message
news:...
> 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 (12. 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.
>
>