update statement

[bearfish]

Hi, i got syntax error in the update statement message when i tried to update all the field in my form. below are my codes:
can anyone tell me where went wrong and where is the syntax error?thank alot.

moCommand.CommandType = adCmdText
moCommand.ActiveConnection = moConnection

sSQL = _
"UPDATE Inventory SET EqpSerialNo = " & txtEqpSerialNo _
& ", BrandName = " & txtBrandName & ", ModelNo = " & txtModelNo _
& ", Specs = " & txtSpecs & ", Notebook = " & chkNotebook.Value _
& ", PurcDate = " & txtPurcDate & ", NetworkType = " & txtNetworkType _
& ", OsType = " & txtOsType & ", Nodename = " & txtNodename _
& ", IpAddress = " & txtIpAddress & ", HubLocation = " & txtHubLocation _
& ", NetworkCableNo = " & txtNetworkCableNo & ", AreaLocated = " & txtAreaLocated _
& ", RespDept = " & txtRespDept & ", RespUser = " & txtRespUser _
& ", Workgroup = " & txtWorkgroup & ", MsOffice = " & chkMsOffice.Value _
& ", AntivirusSoftware = " & txtAntivirusSoftware & ", OtherSoftware = " & txtOtherSoftware _
& ", Opi = " & chkOpi.Value & ", Ecs = " & chkEcs.Value & ", Mcs = " & chkMcs.Value _
& ", Wsp = " & chkWsp.Value & ", SehukConnection = " & chkSehukConnection.Value _
& ", ScannerAttach = " & chkScannerAttach.Value & ", WriteOff = " & chkWriteOff.Value _
& ", Remarks = " & txtRemarks & " WHERE InventoryID = " & sInputDat & " "

moCommand.CommandText = sSQL
Set rsMain = moCommand.Execute

 

[johnwm]

Hi and welcome to Tek-Tips. Take some time to read faq222-2244 as it gives some good tips on getting better answers, and on acknowledging answers received. I notice that so far you've only acknowledged one of the answers given, and you haven't found any of them helpful yet.

For this pronlem try this:

You need single quote marks round any string values. So assuming that EqpSerialNo is a numeric datatype and Brandname is a string datatype, the first 2 lines should read:

"UPDATE Inventory SET EqpSerialNo = " & txtEqpSerialNo _
& ", BrandName = '" & txtBrandName & "', ModelNo = " & txtModelNo

and so on all through the statement

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[MakeItSo]

Hi bearfish!

WHERE InventoryID = " & sInputDat & " "

1.) Why do you add that space at the end? Did you mean & ";"?
2.) Is sInputDat the correct control on your form? Sounds like a Date to me instead of an ID.
And if Inventory ID should mean Inventory-InputDate, then you must surround sInputdat with hashes:
WHERE InventoryID = #" & sInputDat & "#"

Plus: Somewhere in the middle you start using ".Value". You don't need to. It might even cause trouble, so just remove that.

Besides the above mentioned, do as johnwm says by surrounding your strings with single quotes.
;-)
Take care,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[bearfish]

Sorry to cause confusion, but sInputDat is actually a variable to keep the InventoryID for me to do SQL query. Thank alot, I get it to work by add single quotes around the string. I use .Value because its checkbox, not a textbox, so I add it whenever its a checkbox, so far I havent encounter any problem. Thanks for your suggestions.

 

[johnwm]

Glad it worked!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'