What is the problem here?

[tzachari]

Hi All,
I am trying to update a field in an access table via ADO and SQL. I am getting a syntax error which I am not able to resolve. Basically, the field is updated based on the quantity in the text box and the item selected in the list box. I am getting an error message when I include the WHERE clause of the SQL statement and I don’t get any error messages when I don’t have it. So, the problem is in how I am representing the item selected in the list box. Any help appreciated.

Public objCmd As New ADODB.Command

objCmd.CommandText = "Update PO_Table Set Quantity=" & txtQty & "Where PO_number=" & "'" & lstPO.Text & "'"

Also, could you recommend any sites/books that specifically talk about SQL syntax in a VB environment.

Thanks
Zack

[netSurfer]

Put a space in front of the WHERE - also might as well put the ' inside - make it look nicer:

objCmd.CommandText = "Update PO_Table Set Quantity=" & txtQty & " Where PO_number= '" & lstPO.Text & "'"


That should work.

That is assuming the the PO_number is a text field and not a number field - if it is a number field ten yo uhave to remove the ' and change the lstPO.text to that number type (CINT, CLNG etc).

Edited by netSurfer on 03-13-2000 at 11:29 AM

[tzachari]

Thanks Netsurfer, the space in front of the where clause worked.
Zack