SQL Update command

[oobern]

I am trying to do an update of a database record but i keep getting an error message saying " datatype mismatch in criteria expression". I have checked my datatypes in database against my variable datatypes and things match up, but still get the error.

Table Columns
ProductID - Autonumber - long id - long
PRODUCTNAME - text prodname - string
DESCRIPTION - text proddesc - string
Price - currency price - currency
quantity - integer qty - integer
category - text cat - text
image - text prodimage - string

The left is the database columns and datatypes, to the right are the variable names and datatypes. I can't see how there could be a mismatch???
Below is the code using for the SQL command:
Set ors = New ADODB.Recordset
ors.LockType = adLockOptimistic

SQL = "UPDATE tblProducts "
SQL = SQL & "SET PRODUCTNAME = '" & prodname & "', DESCRIPTION = '" & proddesc & "', CATEGORY='" & cat & "', PRICE= '" & price & "', QUANTITY='" & qty & "' "
SQL = SQL & "WHERE PRODUCTID = '" & id & "'"
Text1.Text = SQL

Set ors = oConn.Execute(SQL).

this should work(i think), but doesn't. I can't seem to find any error in the SQL statement. Can someone help me out again here. Thanks again in advance.

oobern

[szlamany]

Do not use a RS variable with an UPDATE clause - in an EXECUTE.

I would personnally create a COMMAND object, and then EXECUTE that.

I'm not at the office right now so I cannot give you any code snippets - try the online help.

[mendhak]

Use objconn.Execute, and if Quantity and ID are numeric fields, remove the single quotes from around them.