473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
3 4227
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Jack | last post by:
I have a text file of data in a file (add2db.txt) where the entries are already entered on separate lines in the following form: INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
7
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
6
by: harborboy76 | last post by:
Hi, I am trying to insert a large number of rows into a table inside a SPL. But every time, I run the SPL, the table is locked because of the INSERT. When I tried to issue a COMMIT, right after...
8
by: 73blazer | last post by:
Hello, I'm looking for a way to make some of my insert templates more readable by placing comments in between the values. I cannot seem to find a way to do this with DB2, is there a way? I'm...
3
by: MP | last post by:
Hi Posted this several hours ago to another ng but it never showed up thought i'd try here. using vb6, ado, .mdb, jet4.0, no access given table tblJob with field JobNumber text(10) 'The...
3
by: mahajanvit | last post by:
Hi one and all I got this problem during my project. So in order to solve this I made a very small application. I am trying to insert using SP and sqldatasource control. I know that while using...
9
by: anachronic_individual | last post by:
Hi all, Is there a standard library function to insert an array of characters at a particular point in a text stream without overwriting the existing content, such that the following data in...
2
by: technocraze | last post by:
Hi guys, I have encountered this error when updating the values to the MS Acess table. Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
6
by: rn5a | last post by:
During registration, users are supposed to enter the following details: First Name, Last Name, EMail, UserName, Password, Confirm Password, Address, City, State, Country, Zip & Phone Number. I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.