Help with SQL syntax, please!

I have this ASP script, I know the syntax is probably wrong, but this line does not update the record. Can anyone help me with proper Syntax?

conn.Execute "Update Cart Set Qty='"&Qty&"' Where ProdID='"&ProdID&"' and CustID='"&CustID&"'"

Code (markup):

Here is the complete code if you need it.

<%
CustID=Session.Contents("CustID")
ProdID=Request.QueryString("ProdID")
Qty=Request.QueryString("Qty")
Dsc=Request.QueryString("Dsc")
Ech=Request.QueryString("Ech")
Shp=Request.QueryString("Shp")
Set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open ("c:/inetpub/wwwroot/HawksWeb/Data/WebData.mdb")
Set OrderData = Server.CreateObject("ADODB.RecordSet")
OrderData.ActiveConnection = conn
OrderData.Open("Select OrderID From Orders where CustID='"&CustID&"'")
If OrderData.RecordCount < 1 Then
OrderData.Close
Set OrderData = nothing
conn.Execute "Insert Into Orders (CustID) Values ('"&CustID&"')" 
Else
OrderData.Close
Set OrderData = nothing
End If
If Qty > 0 Then 
Set Items = Server.CreateObject("ADODB.RecordSet")
Items.ActiveConnection = conn
Items.Open("Select ProdID From Cart where ProdID='"&ProdID&"' and CustId='"&CustID&"'")
If Items.RecordCount < 1 Then
conn.Execute "Insert Into Cart Values ('"&CustID&"','"&Qty&"','"&Dsc&"','"&ProdID&"','"&Ech&"','"&Shp&"')"
Else
conn.Execute "Update Cart Set Qty='"&Qty&"' Where ProdID='"&ProdID&"' and CustID='"&CustID&"'"
End If
End If
Items.Close
Set Items = nothing
Conn.Close
Set conn = nothing
Response.Write(RespStr)
%>

Code (markup):

 

i don't know asp but i'll take a shot, is your Qty String? If not should this be without the '
HTH

 

It is a string or a number depends on how the last language treated it. lol In this case it is a string.

 

Well your database is going to treat Qty='1' differently to Qty=1, so you need to be consistent.
Is Qty an integer or string field on your DDL?

 

It is a string, however without due care it will be interpreted as a number in many circumstances, that due care is the single enclosing quotes. Please note the preceding insert statement does just fine, with identical syntax.

 

May I ask what database product you are using?

 

Access, Yes I have to, this site will interface to an office management system using Access and the Database manager must understand what is going on to that point.

Could it be that I need to use a client side cursor, hmmm that might be it! nope silly me, that would only apply to a recordset.

 

Its been a few years since I last used Access, so I'm not sure.
Is Access case sensitive with column names?

See:
Items.Open("Select ProdID From Cart where ProdID='"&ProdID&"' and CustId='"&CustID&"'")
conn.Execute "Update Cart Set Qty='"&Qty&"' Where ProdID='"&ProdID&"' and CustID='"&CustID&"'"


On another note, can you write in a line of debugging just before the problem line in question, to test if that "Else...End if" ever gets executed.

 

No it is not, please note the insert statement above the update statement.

 

Yes it is being executed in fact at one point ProdID was null and it did a surprising thing, it created a new record with the ProdID field blank!
In case it is not obvious this server code is called by 2 different pages. Page one adds a new product to cart so that prodID will not exist for that CustID.
Page two edits an existing cart entry, so the logic is If product not in cart, add... else if product in cart, edit.

 

That would be a lack of database integrity in Access.

I noticed that update is the only query (in the provided block of code) naming the Qty column. Did you name the column correctly?

 

Insert also names it. I will also have a delete statement if Qty=0.

 

Put it in a string variable and print the SQL, then run it in access SQL editor and run it from there just to see that your sql is correct...

 

I guess where we are at is, the syntax is a sucess, but the patient died.

 

Update Cart Set Qty='2' Where ProdID='937846' and CustID='87089085095804'

 

And what happens when you run that statement directly in an Access SQL editor?