SQL insert, problems

[Fernando]

Hi there,

I am having problems inserting some data, when I put more then 3 values,
happen the following problem :

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.


and that is the code :

SQLquery = "Insert into hotels(hotel_name,state," &_
"city,single,"&"double,extra_nights"


SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
Set oRS=Server.CreateObject("ADODB.connection")
oRS.Open "DSN=santours"
Set oRScom = Server.CreateObject("ADODB.Command")
Set oRScom.ActiveConnection = oRS

oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
oRScom.CommandType = adCmdText
oRScom.Execute
oRS.Close
Set oRScom = Nothing


If somebody can help, would be appreciated.

[Glenn]

The first thing I'd do if I were you would be to see exactly what the SQL
string looks like. So after you finish building it, write it out, like this:

>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"

Response.write ("Query = " & oRScom.CommandText & "<br>")
Response.end

[Bruna]

I'm having the same problem.
But I don't understand the explanation....
Thanks for a help

[Bruna]

"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>
I'm having the same problem.
If anyone have a solution...
Thanks
Bruna

[Dirk]

Hi,

I think your problem is that your statement is missing a ' or " somewhere.
Each alphanumeric value must be in ' ' ! If you are inserting numeric values
(like integer, long, byte) or boolean values, you don't use ' ' !
Look at the database setup. The easiest is, if you setup the database fields
all as TEXT. Then you don't need to care about inserting the right value
types in the database fields.

Does this help?

Dirk




"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>

[Kris Eiben]

Response.write your SQL statement, then run it through the DB directly.
Hopefully you've got a tool that will give you a better indication of where
the error lies.

Dirk wrote in message <39f66002$1@news.devx.com>...
>
>Hi,
>
>I think your problem is that your statement is missing a ' or " somewhere.
>Each alphanumeric value must be in ' ' ! If you are inserting numeric
values
>(like integer, long, byte) or boolean values, you don't use ' ' !
>Look at the database setup. The easiest is, if you setup the database
fields
>all as TEXT. Then you don't need to care about inserting the right value
>types in the database fields.
>
>Does this help?
>
>Dirk
>
>
>
>
>"Fernando" <fernando@wbrasil.com> wrote:
>>
>>Hi there,
>>
>>I am having problems inserting some data, when I put more then 3 values,
>>happen the following problem :
>>
>>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.
>>
>>
>>and that is the code :
>>
>>SQLquery = "Insert into hotels(hotel_name,state," &_
>>"city,single,"&"double,extra_nights"
>>
>>
>>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"&
CStr(Request.Form("state"))
>>& "','"& CStr(Request.Form("city"))&"',"&
CStr(Request.Form("single"))&","&
>>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>>Set oRS=Server.CreateObject("ADODB.connection")
>>oRS.Open "DSN=santours"
>>Set oRScom = Server.CreateObject("ADODB.Command")
>>Set oRScom.ActiveConnection = oRS
>>
>>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>>oRScom.CommandType = adCmdText
>>oRScom.Execute
>>oRS.Close
>>Set oRScom = Nothing
>>
>>
>>If somebody can help, would be appreciated.
>>
>

[Paul Brady]

Fernando,
I think the answer is simple. You need to close your parentheses after extra_nights.
Otherwise, you are including your values list into your list of fields.
Make sense?
The SQL code should read as follows:

-------------
SQLquery = "Insert into hotels(hotel_name,state," &_
"city,single,"&"double,extra_nights) "


SQLquery2= "Values('" & CStr(Request.Form("hotel_name"))
-------------
The remainder of the code is unchanged. Try that.
Good Luck!
PB

"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>

[Matt]

Fernando-

I know you wrote this about a month ago, but just in case you still didn't
understand why you were getting that error, I thought I'd write you. Well,
this is what I think the problem is. You have fields in your database named
single and double. The Microsoft Jet Database Engine Driver that I assume
you're using to access the database, has what are called "Reserved Words."
These are words that the Access driver has reserved for specific uses.
Single and double are both reserved words. Assuming the rest of your code
is correct, you will have to name your fields in your database something
else. To find out more about reserved words, within the contents of the
help feature in MS Access, type in reserve, and then look for Microsoft Jet
Database Engine reserved words for SQL.

Hope this helps.

Matt





"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>
"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>

[matt]

I'd recommend something like the following approach. This has the
additional benefit of allowing embedded quotes in the various values.

' include adovbs.inc for ado constants such as adCmdText, adExecuteNoRecords
dim conn, cmd, sql

set conn = server.createobject("adodb.connection")
set cmd = server.createobject("adodb.command")

conn.open "Provider=MSDASQL;DSN=MyDSN;"
' Check for connection open errors here of course!

set cmd.activeconnection = conn
sql = "insert into hotels "
sql = sql & "(hotel_name,state,city,single,double,extra_nights) "
sql = sql & "values (?,?,?,?,?,?)"

cmd.commandtext = sql
cmd.commandtype = adCmdText
cmd.parameters.refresh

cmd.parameters(0).value = CStr(Request.Form("hotel_name"))
cmd.parameters(1).value = CStr(Request.Form("state"))
cmd.parameters(2).value = CStr(Request.Form("city"))
cmd.parameters(3).value = CStr(Request.Form("single"))
cmd.parameters(4).value = CStr(Request.Form("double"))
cmd.parameters(5).value = CStr(Request.Form("extra_nights"))

cmd.execute ,,adExecuteNoRecords


Matt

[Michael T. Gercevich]

Forget trying to write a SQL Command String - instead just use the ADO equivalent
commands to do the same thing...

Dim objConn
Dim objRS
Set objCon = Server.CreateObject("ADODB.Connection")
'Setup your connection here....
'objCon.Provider = "Microsoft.Jet.OLEDB.4.0"
'objCon.Properties("DSN") = "santours"
objCon.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objCon
objRS.CursorType = 3 'adOpenStatic
objRS.LockType = 3 'adLockOptimistic
objRS.Source = "Hotels"
objRS.Open
objRS.AddNew
objRS("hotel_name") = Request.Form("hotel_name")
objRS("state") = Request.Form("state")
objRS("city") = Request.Form("city")
objRS("single") = Request.Form("single")
objRS("double") = Request.Form("double")
objRS("extra_nights")= Request.Form("extra_night")
objRS.Update
'Do additional code here
If NOT objRS.State = 0 Then objRS.Close
Set objRS = Nothing
Set objConn = Nothing

Hope this helps,
-Michael T. Gercevich

"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>

[Basch]

Memo fields should always be the last fields which are being inserted into
a SQL Query.


If this is not the problem, try this right before you execute the command:
response.write(SQLquery &")" & SQLquery2 & ")")


Bas

http://www.vdsluis.net




"Michael T. Gercevich" <mgercevich@hotmail.com> wrote:
>
>Forget trying to write a SQL Command String - instead just use the ADO equivalent
>commands to do the same thing...
>
>Dim objConn
>Dim objRS
>Set objCon = Server.CreateObject("ADODB.Connection")
>'Setup your connection here....
>'objCon.Provider = "Microsoft.Jet.OLEDB.4.0"
>'objCon.Properties("DSN") = "santours"
>objCon.Open
>Set objRS = Server.CreateObject("ADODB.Recordset")
>objRS.ActiveConnection = objCon
>objRS.CursorType = 3 'adOpenStatic
>objRS.LockType = 3 'adLockOptimistic
>objRS.Source = "Hotels"
>objRS.Open
>objRS.AddNew
>objRS("hotel_name") = Request.Form("hotel_name")
>objRS("state") = Request.Form("state")
>objRS("city") = Request.Form("city")
>objRS("single") = Request.Form("single")
>objRS("double") = Request.Form("double")
>objRS("extra_nights")= Request.Form("extra_night")
>objRS.Update
>'Do additional code here
>If NOT objRS.State = 0 Then objRS.Close
>Set objRS = Nothing
>Set objConn = Nothing
>
>Hope this helps,
>-Michael T. Gercevich
>
>"Fernando" <fernando@wbrasil.com> wrote:
>>
>>Hi there,
>>
>>I am having problems inserting some data, when I put more then 3 values,
>>happen the following problem :
>>
>>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>>
>>
>>and that is the code :
>>
>>SQLquery = "Insert into hotels(hotel_name,state," &_
>>"city,single,"&"double,extra_nights"
>>
>>
>>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) &
""
>>Set oRS=Server.CreateObject("ADODB.connection")
>>oRS.Open "DSN=santours"
>>Set oRScom = Server.CreateObject("ADODB.Command")
>>Set oRScom.ActiveConnection = oRS
>>
>>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>>oRScom.CommandType = adCmdText
>>oRScom.Execute
>>oRS.Close
>>Set oRScom = Nothing
>>
>>
>>If somebody can help, would be appreciated.
>>
>

[Mohan]

Hi

I think there are two things to be looked in ur code.

One is being covered by Matt i.e. MS-Access gives errors while using reserve
words. Same problem was encountered by me when i used date as a field name
in my table.

The second one
I understood from your code that the first three fields are string fields
and last three are numeric fields(single,double and extra_nights). U need
to validate numeric fields before using in the query as field values sent
by html form are string types. If user does not enter anything in the numeric
fields in html form, a space will be sent to the server which results in
the syntax error described by u.


Hope these will solve ur problem


Mohan


"Matt" <wex3@aol.com> wrote:
>
>Fernando-
>
>I know you wrote this about a month ago, but just in case you still didn't
>understand why you were getting that error, I thought I'd write you. Well,
>this is what I think the problem is. You have fields in your database named
>single and double. The Microsoft Jet Database Engine Driver that I assume
>you're using to access the database, has what are called "Reserved Words."
> These are words that the Access driver has reserved for specific uses.

>Single and double are both reserved words. Assuming the rest of your code
>is correct, you will have to name your fields in your database something
>else. To find out more about reserved words, within the contents of the
>help feature in MS Access, type in reserve, and then look for Microsoft
Jet
>Database Engine reserved words for SQL.
>
>Hope this helps.
>
>Matt
>
>
>
>
>
>"Fernando" <fernando@wbrasil.com> wrote:
>>
>>Hi there,
>>
>>I am having problems inserting some data, when I put more then 3 values,
>>happen the following problem :
>>
>>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>>
>>
>>and that is the code :
>>
>>SQLquery = "Insert into hotels(hotel_name,state," &_
>>"city,single,"&"double,extra_nights"
>>
>>
>>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) &
""
>>Set oRS=Server.CreateObject("ADODB.connection")
>>oRS.Open "DSN=santours"
>>Set oRScom = Server.CreateObject("ADODB.Command")
>>Set oRScom.ActiveConnection = oRS
>>
>>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>>oRScom.CommandType = adCmdText
>>oRScom.Execute
>>oRS.Close
>>Set oRScom = Nothing
>>
>>
>>If somebody can help, would be appreciated.
>>
>"Fernando" <fernando@wbrasil.com> wrote:
>>
>>Hi there,
>>
>>I am having problems inserting some data, when I put more then 3 values,
>>happen the following problem :
>>
>>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>>
>>
>>and that is the code :
>>
>>SQLquery = "Insert into hotels(hotel_name,state," &_
>>"city,single,"&"double,extra_nights"
>>
>>
>>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) &
""
>>Set oRS=Server.CreateObject("ADODB.connection")
>>oRS.Open "DSN=santours"
>>Set oRScom = Server.CreateObject("ADODB.Command")
>>Set oRScom.ActiveConnection = oRS
>>
>>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>>oRScom.CommandType = adCmdText
>>oRScom.Execute
>>oRS.Close
>>Set oRScom = Nothing
>>
>>
>>If somebody can help, would be appreciated.
>>
>

[Ghannodahn]

Perhaps you have an apostrophe or some reserved character that is causing
the problem? For SQL statements, the ADOCommand object solves these. If
you've ever worked in a parameterized query, then you'll have no trouble
figuring it out. If not, I believe that DevX has some resources on using
the ADOCommand object.

"Bruna" <bfraga@saude.gov.br> wrote:
>
>"Fernando" <fernando@wbrasil.com> wrote:
>>
>>Hi there,
>>
>>I am having problems inserting some data, when I put more then 3 values,
>>happen the following problem :
>>
>>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>>
>>
>>and that is the code :
>>
>>SQLquery = "Insert into hotels(hotel_name,state," &_
>>"city,single,"&"double,extra_nights"
>>
>>
>>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) &
""
>>Set oRS=Server.CreateObject("ADODB.connection")
>>oRS.Open "DSN=santours"
>>Set oRScom = Server.CreateObject("ADODB.Command")
>>Set oRScom.ActiveConnection = oRS
>>
>>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>>oRScom.CommandType = adCmdText
>>oRScom.Execute
>>oRS.Close
>>Set oRScom = Nothing
>>
>>
>>If somebody can help, would be appreciated.
>>
>I'm having the same problem.
>If anyone have a solution...
>Thanks
>Bruna

[Paul]

I had a similar problem recently and it did turn out to be reserved words
as field names. I simply put the reserved word field names in square brackets
[] and it worked.

[Peter]

I'll agree with Glenn here, try output your query just before it executes
to see what it looks like.

Cut the query and open you're access database and create a new query from
SQL, using the cut query, this will get you closer to a solution. Sometimes
it'll 'focus' on the problem clause.


"Fernando" <fernando@wbrasil.com> wrote:
>
>Hi there,
>
>I am having problems inserting some data, when I put more then 3 values,
>happen the following problem :
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
>
>
>and that is the code :
>
>SQLquery = "Insert into hotels(hotel_name,state," &_
>"city,single,"&"double,extra_nights"
>
>
>SQLquery2= "Values('" & CStr(Request.Form("hotel_name")) & "','"& CStr(Request.Form("state"))
>& "','"& CStr(Request.Form("city"))&"',"& CStr(Request.Form("single"))&","&
>CStr(Request.Form("double"))&","& CStr(Request.Form("extra_nights")) & ""
>Set oRS=Server.CreateObject("ADODB.connection")
>oRS.Open "DSN=santours"
>Set oRScom = Server.CreateObject("ADODB.Command")
>Set oRScom.ActiveConnection = oRS
>
>oRScom.CommandText = SQLquery &")" & SQLquery2 & ")"
>oRScom.CommandType = adCmdText
>oRScom.Execute
>oRS.Close
>Set oRScom = Nothing
>
>
>If somebody can help, would be appreciated.
>