SQL Error on INSERT
am 19.03.2005 04:06:31 von westernnord
I am using Windows XP with IIS and Access 2000 DB and getting the following
error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/xmlListener.asp, line 67
Here are the values I get using Response.Write statements with a
Response.End:
422,6,0
1,4.95,131150 09 BA,Men's Leather Slippers,09 / 43 BLACK,2,150.00
The first three numbers are defined as "Number" in the DB. The other
variables are defined as "Text".
Here is the code:
<%
FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION
Dim ProductID
Dim GiftWrap(40)
Dim GiftWrapPrice(40)
Dim ItemNumber(40)
Dim ItemName(40)
Dim ItemAttributes(40)
Dim ItemQuantity(40)
Dim ItemPrice(40)
Dim objReq
Set objReq = Server.CreateObject("Microsoft.XMLDOM")
objReq.Load Request
StoreName = objReq.getElementsByTagName("StoreName").item(0).text
UserID = objReq.getElementsByTagName("UserID").item(0).text
NumberOfItems = objReq.getElementsByTagName("NumberOfItems").item(0).text
ProductID = 0
' Open Database Connection
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" &
server.mappath("\_database\storeDB.mdb")
sqlString = "SELECT store_id FROM store WHERE store_name=" & "'" & StoreName
& "'"
SET RS = Con.Execute( sqlString )
Response.Write (userID & ",")
Response.Write (RS("store_id") & ",")
Response.Write (ProductID & "
")
FOR k = 0 TO NumberOfItems - 1
GiftWrap(k) = objReq.getElementsByTagName("GiftWrap").item(k).text
GiftWrapPrice(k) = objReq.getElementsByTagName("GiftWrapPrice").item(k).text
ItemNumber(k) = objReq.getElementsByTagName("ItemNumber").item(k).text
ItemName(k) = objReq.getElementsByTagName("ItemName").item(k).text
ItemAttributes(k) =
objReq.getElementsByTagName("ItemAttributes").item(k).text
ItemQuantity(k) = objReq.getElementsByTagName("ItemQuantity").item(k).text
ItemPrice(k) = objReq.getElementsByTagName("ItemPrice").item(k).text
Response.Write (GiftWrap(k) & ",")
Response.Write (GiftWrapPrice(k) & ",")
Response.Write (ItemNumber(k) & ",")
Response.Write (ItemName(k) & ",")
Response.Write (ItemAttributes(k) & ",")
Response.Write (ItemQuantity(k) & ",")
Response.Write (ItemPrice(k))
Response.End
NEXT
FOR k = 0 TO NumberOfItems - 1
sqlString = "INSERT INTO cart ( " &_
"cart_user_id, " &_
"cart_store_id, " &_
"cart_product_id, " &_
"cart_gift_wrap, " &_
"cart_gift_wrap_price, " &_
"cart_item_number, " &_
"cart_item_name, " &_
"cart_item_attributes, " &_
"cart_item_quantity, " &_
"cart_item_price " &_
") VALUES ( " &_
UserID & ", " &_
RS( "store_id" ) & ", " &_
ProductID & ", " &_
GiftWrap(k) & ", " &_
GiftWrapPrice(k) & ", " &_
" '" & fixQuotes( ItemNumber(k) ) & "', " &_
" '" & fixQuotes( ItemName(k) ) & "', " &_
" '" & fixQuotes( ItemAttributes(k) ) & "', " &_
ItemQuantity(k) & ", " &_
ItemPrice(k) & " )"
SET RScart = Con.Execute( sqlString )
NEXT
RS.Close
SET RS = Nothing
RScart.Close
SET RScart = Nothing
Response.Write (StoreName)
Response.Write (UserID)
Response.Write (NumberOfItems)
Response.End
%>
Line 67 is:
SET RScart = Con.Execute( sqlString )
I just don't get it. Can someone help me out with this error?
Regards, Richard
Re: SQL Error on INSERT
am 19.03.2005 04:22:08 von reb01501
westernnord@webtv.net wrote:
> I am using Windows XP with IIS and Access 2000 DB and getting the
> following error:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /xmlListener.asp, line 67
>
> Here are the values I get using Response.Write statements with a
> Response.End:
>
> 422,6,0
> 1,4.95,131150 09 BA,Men's Leather Slippers,09 / 43 BLACK,2,150.00
This does not help. You need to response.write the variable containing the
sql statement.
Here's some food for thought:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Re: SQL Error on INSERT
am 19.03.2005 05:59:47 von westernnord
Here is the new error I am getting after some code changes:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '131150 09 BA'.
/xmlListener.asp, line 69
Here is the Response.Write of the "sqlString" variable:
INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
cart_gift_wrap, cart_gift_wrap_price, cart_item_number, cart_item_name,
cart_item_attributes, cart_item_quantity, cart_item_price ) VALUES ( 423, 6,
0, 1, 4.95, 131150 09 BA, Men's Leather Slippers, 09 / 43 BLACK, 2, 150.00 )
I have changed the code to:
<%
FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION
Dim ProductID
Dim GiftWrap(40)
Dim GiftWrapPrice(40)
Dim ItemNumber(40)
Dim ItemName(40)
Dim ItemAttributes(40)
Dim ItemQuantity(40)
Dim ItemPrice(40)
Dim objReq
Set objReq = Server.CreateObject("Microsoft.XMLDOM")
objReq.Load Request
StoreName = objReq.getElementsByTagName("StoreName").item(0).text
UserID = objReq.getElementsByTagName("UserID").item(0).text
NumberOfItems = objReq.getElementsByTagName("NumberOfItems").item(0).text
ProductID = 0
' Open Database Connection
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" &
server.mappath("\_database\storeDB.mdb")
sqlString = "SELECT store_id FROM store WHERE store_name=" & "'" & StoreName
& "'"
SET RS = Con.Execute( sqlString )
Response.Write (userID & ",")
Response.Write (RS("store_id") & ",")
Response.Write (ProductID & "
")
FOR k = 0 TO NumberOfItems - 1
GiftWrap(k) = objReq.getElementsByTagName("GiftWrap").item(k).text
GiftWrapPrice(k) = objReq.getElementsByTagName("GiftWrapPrice").item(k).text
ItemNumber(k) = objReq.getElementsByTagName("ItemNumber").item(k).text
ItemName(k) = objReq.getElementsByTagName("ItemName").item(k).text
ItemAttributes(k) =
objReq.getElementsByTagName("ItemAttributes").item(k).text
ItemQuantity(k) = objReq.getElementsByTagName("ItemQuantity").item(k).text
ItemPrice(k) = objReq.getElementsByTagName("ItemPrice").item(k).text
Response.Write (GiftWrap(k) & ",")
Response.Write (GiftWrapPrice(k) & ",")
Response.Write (ItemNumber(k) & ",")
Response.Write (ItemName(k) & ",")
Response.Write (ItemAttributes(k) & ",")
Response.Write (ItemQuantity(k) & ",")
Response.Write (ItemPrice(k))
NEXT
FOR k = 0 TO NumberOfItems - 1
sqlString = "INSERT INTO cart ( " &_
"cart_user_id, " &_
"cart_store_id, " &_
"cart_product_id, " &_
"cart_gift_wrap, " &_
"cart_gift_wrap_price, " &_
"cart_item_number, " &_
"cart_item_name, " &_
"cart_item_attributes, " &_
"cart_item_quantity, " &_
"cart_item_price " &_
") VALUES ( " &_
UserID & ", " &_
RS( "store_id" ) & ", " &_
ProductID & ", " &_
GiftWrap(k) & ", " &_
GiftWrapPrice(k) & ", " &_
ItemNumber(k) & ", " &_
ItemName(k) & ", " &_
ItemAttributes(k) & ", " &_
ItemQuantity(k) & ", " &_
ItemPrice(k) & " )"
Response.Write (sqlString)
Response.End
SET RScart = Con.Execute( sqlString )
NEXT
RS.Close
SET RS = Nothing
RScart.Close
SET RScart = Nothing
Response.Write (StoreName)
Response.Write (UserID)
Response.Write (NumberOfItems)
Response.End
%>
"Bob Barrows [MVP]" wrote in message
news:%23vZxTLDLFHA.3616@TK2MSFTNGP09.phx.gbl...
> westernnord@webtv.net wrote:
>> I am using Windows XP with IIS and Access 2000 DB and getting the
>> following error:
>>
>> Error Type:
>> Microsoft JET Database Engine (0x80040E14)
>> Syntax error in INSERT INTO statement.
>> /xmlListener.asp, line 67
>>
>> Here are the values I get using Response.Write statements with a
>> Response.End:
>>
>> 422,6,0
>> 1,4.95,131150 09 BA,Men's Leather Slippers,09 / 43 BLACK,2,150.00
>
> This does not help. You need to response.write the variable containing the
> sql statement.
>
> Here's some food for thought:
>
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
Re: SQL Error on INSERT
am 19.03.2005 11:03:12 von John Blessing
wrote in message
news:OqNE$BELFHA.2420@TK2MSFTNGP12.phx.gbl...
> Here is the new error I am getting after some code changes:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression '131150 09 BA'.
> /xmlListener.asp, line 69
>
> Here is the Response.Write of the "sqlString" variable:
>
> INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
> cart_gift_wrap, cart_gift_wrap_price, cart_item_number, cart_item_name,
> cart_item_attributes, cart_item_quantity, cart_item_price ) VALUES ( 423,
> 6, 0, 1, 4.95, 131150 09 BA, Men's Leather Slippers, 09 / 43 BLACK, 2,
> 150.00 )
Any text values must be wrapped in single quotes. If your text value
already containsa single quote you need to replace every occurrence with
two, so your sql looks like:
NSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
cart_gift_wrap, cart_gift_wrap_price, cart_item_number, cart_item_name,
cart_item_attributes, cart_item_quantity, cart_item_price ) VALUES ( 423, 6,
0, 1, 4.95, '131150 09 BA', 'Men''s Leather Slippers', '09 / 43 BLACK', 2,
150.00 )
--
John Blessing
http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
Re: SQL Error on INSERT
am 19.03.2005 13:49:46 von reb01501
westernnord@webtv.net wrote:
> Here is the new error I am getting after some code changes:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression '131150 09 BA'.
> /xmlListener.asp, line 69
>
> Here is the Response.Write of the "sqlString" variable:
>
> INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
> cart_gift_wrap, cart_gift_wrap_price, cart_item_number,
> cart_item_name, cart_item_attributes, cart_item_quantity,
> cart_item_price ) VALUES ( 423, 6, 0, 1, 4.95, 131150 09 BA, Men's
> Leather Slippers, 09 / 43 BLACK, 2, 150.00 )
John Blessing wrote:
>
> Any text values must be wrapped in single quotes. If your text value
> already containsa single quote you need to replace every occurrence
> with two, so your sql looks like:
>
>
> NSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
> cart_gift_wrap, cart_gift_wrap_price, cart_item_number,
> cart_item_name, cart_item_attributes, cart_item_quantity,
> cart_item_price ) VALUES ( 423, 6, 0, 1, 4.95, '131150 09 BA',
> 'Men''s Leather Slippers', '09 / 43 BLACK', 2, 150.00 )
Yes, isn't it frustrating to deal with delimiters and embedded quotes? You
really should look at the post I cited in my first reply:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
Also, there is no need to open a recordset to get the store_id. You can
replace the VALUES clause with a SELECT statement that retrieves the
store_id from the store table (you might want to think about naming the
table "stores" since it probably contains more than one "store" record,
doesn't it?)
Your code can be made as simple as this:
dim con, sSQL, cmd, arParms
sSQL = "INSERT INTO cart ( cart_user_id, cart_store_id, " & _
"cart_product_id, cart_gift_wrap, cart_gift_wrap_price, " & _
"cart_item_number, cart_item_name, " & _
"cart_item_attributes, cart_item_quantity, cart_item_price )" & _
"SELECT ?,store_id,?,?,?,?,?,?,?,? " & _
" FROM store WHERE store_name=?"
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" &
server.mappath("\_database\storeDB.mdb")
'Then, in your loop:
..FOR k = 0 TO NumberOfItems - 1
arParms = array(UserID, ProductID, GiftWrap(k), _
GiftWrapPrice(k), ItemNumber(k), ItemName(k), _
ItemAttributes(k), ItemQuantity(k), ItemPrice(k), _
StoreName)
set cmd=createobject("adodb.command")
cmd.CommandText = sSQL
set cmd.ActiveConnection = con
cmd.Execute sSQL,,129
next
The code can be made even simpler still if you use a saved parameter query:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Re: SQL Error on INSERT
am 19.03.2005 17:28:15 von westernnord
The error I get now is the same error I got on my original post:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/xmlListener.asp, line 68
I have restored the code to my first post which included taking care of
quotes. The contents of sqlString is as follows, which matches John's post.
INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
cart_gift_wrap, cart_gift_wrap_price, cart_item_number, cart_item_name,
cart_item_attributes, cart_item_quantity, cart_item_price ) VALUES ( 424, 6,
0, 1, 4.95, '131150 09 BA', 'Men''s Leather Slippers', '09 / 43 BLACK', 2,
150.00 )
Here is the latest code. I really need to know what is wrong with the SQL
syntax. Once this is solved, I will simplify the code as Bob suggests.
<%
FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION
Dim ProductID
Dim GiftWrap(40)
Dim GiftWrapPrice(40)
Dim ItemNumber(40)
Dim ItemName(40)
Dim ItemAttributes(40)
Dim ItemQuantity(40)
Dim ItemPrice(40)
Dim objReq
Set objReq = Server.CreateObject("Microsoft.XMLDOM")
objReq.Load Request
StoreName = objReq.getElementsByTagName("StoreName").item(0).text
UserID = objReq.getElementsByTagName("UserID").item(0).text
NumberOfItems = objReq.getElementsByTagName("NumberOfItems").item(0).text
ProductID = 0
' Open Database Connection
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" &
server.mappath("\_database\storeDB.mdb")
sqlString = "SELECT store_id FROM store WHERE store_name=" & "'" & StoreName
& "'"
SET RS = Con.Execute( sqlString )
Response.Write (userID & ",")
Response.Write (RS("store_id") & ",")
Response.Write (ProductID & "
")
FOR k = 0 TO NumberOfItems - 1
GiftWrap(k) = objReq.getElementsByTagName("GiftWrap").item(k).text
GiftWrapPrice(k) = objReq.getElementsByTagName("GiftWrapPrice").item(k).text
ItemNumber(k) = objReq.getElementsByTagName("ItemNumber").item(k).text
ItemName(k) = objReq.getElementsByTagName("ItemName").item(k).text
ItemAttributes(k) =
objReq.getElementsByTagName("ItemAttributes").item(k).text
ItemQuantity(k) = objReq.getElementsByTagName("ItemQuantity").item(k).text
ItemPrice(k) = objReq.getElementsByTagName("ItemPrice").item(k).text
Response.Write (GiftWrap(k) & ",")
Response.Write (GiftWrapPrice(k) & ",")
Response.Write (ItemNumber(k) & ",")
Response.Write (ItemName(k) & ",")
Response.Write (ItemAttributes(k) & ",")
Response.Write (ItemQuantity(k) & ",")
Response.Write (ItemPrice(k))
NEXT
FOR k = 0 TO NumberOfItems - 1
sqlString = "INSERT INTO cart ( " &_
"cart_user_id, " &_
"cart_store_id, " &_
"cart_product_id, " &_
"cart_gift_wrap, " &_
"cart_gift_wrap_price, " &_
"cart_item_number, " &_
"cart_item_name, " &_
"cart_item_attributes, " &_
"cart_item_quantity, " &_
"cart_item_price " &_
") VALUES ( " &_
UserID & ", " &_
RS( "store_id" ) & ", " &_
ProductID & ", " &_
GiftWrap(k) & ", " &_
GiftWrapPrice(k) & ", " &_
" '" & fixQuotes( ItemNumber(k) ) & "', " &_
" '" & fixQuotes( ItemName(k) ) & "', " &_
" '" & fixQuotes( ItemAttributes(k) ) & "', " &_
ItemQuantity(k) & ", " &_
ItemPrice(k) & " )"
Response.Write (sqlString)
SET RScart = Con.Execute( sqlString )
NEXT
RS.Close
SET RS = Nothing
RScart.Close
SET RScart = Nothing
Response.Write (StoreName)
Response.Write (UserID)
Response.Write (NumberOfItems)
Response.End
%>
"Bob Barrows [MVP]" wrote in message
news:%23ddVfIILFHA.2736@TK2MSFTNGP09.phx.gbl...
> westernnord@webtv.net wrote:
>> Here is the new error I am getting after some code changes:
>>
>> Error Type:
>> Microsoft JET Database Engine (0x80040E14)
>> Syntax error (missing operator) in query expression '131150 09 BA'.
>> /xmlListener.asp, line 69
>>
>> Here is the Response.Write of the "sqlString" variable:
>>
>> INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
>> cart_gift_wrap, cart_gift_wrap_price, cart_item_number,
>> cart_item_name, cart_item_attributes, cart_item_quantity,
>> cart_item_price ) VALUES ( 423, 6, 0, 1, 4.95, 131150 09 BA, Men's
>> Leather Slippers, 09 / 43 BLACK, 2, 150.00 )
> John Blessing wrote:
>>
>> Any text values must be wrapped in single quotes. If your text value
>> already containsa single quote you need to replace every occurrence
>> with two, so your sql looks like:
>>
>>
>> NSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
>> cart_gift_wrap, cart_gift_wrap_price, cart_item_number,
>> cart_item_name, cart_item_attributes, cart_item_quantity,
>> cart_item_price ) VALUES ( 423, 6, 0, 1, 4.95, '131150 09 BA',
>> 'Men''s Leather Slippers', '09 / 43 BLACK', 2, 150.00 )
>
> Yes, isn't it frustrating to deal with delimiters and embedded quotes? You
> really should look at the post I cited in my first reply:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
>
> Also, there is no need to open a recordset to get the store_id. You can
> replace the VALUES clause with a SELECT statement that retrieves the
> store_id from the store table (you might want to think about naming the
> table "stores" since it probably contains more than one "store" record,
> doesn't it?)
>
> Your code can be made as simple as this:
>
> dim con, sSQL, cmd, arParms
>
> sSQL = "INSERT INTO cart ( cart_user_id, cart_store_id, " & _
> "cart_product_id, cart_gift_wrap, cart_gift_wrap_price, " & _
> "cart_item_number, cart_item_name, " & _
> "cart_item_attributes, cart_item_quantity, cart_item_price )" & _
> "SELECT ?,store_id,?,?,?,?,?,?,?,? " & _
> " FROM store WHERE store_name=?"
>
> Set Con = Server.CreateObject( "ADODB.Connection" )
>
> Con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" &
> server.mappath("\_database\storeDB.mdb")
>
> 'Then, in your loop:
>
> .FOR k = 0 TO NumberOfItems - 1
>
> arParms = array(UserID, ProductID, GiftWrap(k), _
> GiftWrapPrice(k), ItemNumber(k), ItemName(k), _
> ItemAttributes(k), ItemQuantity(k), ItemPrice(k), _
> StoreName)
>
> set cmd=createobject("adodb.command")
> cmd.CommandText = sSQL
> set cmd.ActiveConnection = con
> cmd.Execute sSQL,,129
> next
>
>
> The code can be made even simpler still if you use a saved parameter
> query:
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
Re: SQL Error on INSERT
am 19.03.2005 17:49:15 von reb01501
westernnord@webtv.net wrote:
> The error I get now is the same error I got on my original post:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /xmlListener.asp, line 68
>
> I have restored the code to my first post which included taking care
> of quotes. The contents of sqlString is as follows, which matches John's
> post.
> INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
> cart_gift_wrap, cart_gift_wrap_price, cart_item_number,
> cart_item_name, cart_item_attributes, cart_item_quantity,
> cart_item_price ) VALUES (
> 424, 6, 0, 1, 4.95, '131150 09 BA', 'Men''s Leather Slippers', '09 /43
> BLACK', 2, 150.00 )
>
Nothing sticks out to me, so:
Open your database in Access. Create a new query in Design view (without
choosing a table from the dialog). Switch to SQL View. Paste this sql
statement from te browser window into the sql window and try to run it. Do
you get a better error message?
When doing dynamic sql (ughh!), your goal is to generate a statement that
will run as-is in the Access query builder. So tweak the statement in the
query builder until it works. Then you will know what it needs to look like
when you generate it in vbscript.
The only exception to this rule is the use of wild cards in LIKE
comparisons. Access requires the use of * and ?. ADO requires the use of
ODBC wildcards: % and _.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Re: SQL Error on INSERT
am 19.03.2005 19:30:34 von westernnord
Thanks for your excellent help. I fixed the quotes on the remaining "text"
variables and all is well. Frustration has gone. Now to simplify the code.
Regards, Richard
"Bob Barrows [MVP]" wrote in message
news:uIlEUOKLFHA.1396@TK2MSFTNGP10.phx.gbl...
> westernnord@webtv.net wrote:
>> The error I get now is the same error I got on my original post:
>>
>> Error Type:
>> Microsoft JET Database Engine (0x80040E14)
>> Syntax error in INSERT INTO statement.
>> /xmlListener.asp, line 68
>>
>> I have restored the code to my first post which included taking care
>> of quotes. The contents of sqlString is as follows, which matches John's
>> post.
>> INSERT INTO cart ( cart_user_id, cart_store_id, cart_product_id,
>> cart_gift_wrap, cart_gift_wrap_price, cart_item_number,
>> cart_item_name, cart_item_attributes, cart_item_quantity,
>> cart_item_price ) VALUES (
>> 424, 6, 0, 1, 4.95, '131150 09 BA', 'Men''s Leather Slippers', '09 /43
>> BLACK', 2, 150.00 )
>>
>
> Nothing sticks out to me, so:
>
> Open your database in Access. Create a new query in Design view (without
> choosing a table from the dialog). Switch to SQL View. Paste this sql
> statement from te browser window into the sql window and try to run it. Do
> you get a better error message?
>
> When doing dynamic sql (ughh!), your goal is to generate a statement that
> will run as-is in the Access query builder. So tweak the statement in the
> query builder until it works. Then you will know what it needs to look
> like when you generate it in vbscript.
>
> The only exception to this rule is the use of wild cards in LIKE
> comparisons. Access requires the use of * and ?. ADO requires the use of
> ODBC wildcards: % and _.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>