...

View Full Version : converting a select statement to a update statement



bianx_07
09-29-2011, 09:23 AM
Hi there

I am trying to convert this line to a update query:
SELECT Purchasing.PurchaseNo, Purchasing.NoLicences, Purchasing.Cost, Purchasing.DateOrder, Purchasing.TypePayment, Purchasing.Supplier, Purchasing.DateRenewal, Purchasing.Company, Purchasing.Branch FROM Purchasing WHERE Purchasing.ID =116

This is my update query so far:
Set rsUpdatePurchase = dbUpdatePurchase.Execute("UPDATE Purchasing Version = " & intVersionID & ", PurchaseNo = " & strPurchase & ", NoLicences = " & strLicences & ", Cost = " & strCost & ", DateOrder = #" & strDateo & "#, TypePayment = '" & strPayment & "', Supplier = '" & strSupplier & "', DateRenewal = #" & strDater & "#, Company = " & intDivision & ", Branch = " & intCity & " WHERE (((ID)=" & intID &"));")

This update query gives me this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/wspintranet/InformationTechnology/IT_Help_Desk_New/HelpDesk_AssetManagement/AssetManagement_Functions/Asset_Functions.asp, line 5327

All the help will be appreciated.
Thank you in advance.

Old Pedant
09-29-2011, 09:07 PM
Start by writing your code so it can be debugged.


SQL = "UPDATE Purchasing Version = " & intVersionID & ", PurchaseNo = " & strPurchase _
& ", NoLicences = " & strLicences & ", Cost = " & strCost _
& ", DateOrder = #" & strDateo & "#, TypePayment = '" & strPayment & "'" _
& ", Supplier = '" & strSupplier & "', DateRenewal = #" & strDater & "#," _
& " Company = " & intDivision & ", Branch = " & intCity _
& " WHERE ID=" & intID
Response.Write "<hr>DEBUG SQL: " & SQL & "<hr>" & vbNewLine
Set rsUpdatePurchase = dbUpdatePurchase.Execute(SQL)

(The idea being that you can easily comment out the debug line when it starts working.)

Now look at the output that this produces.

Notice anything missing?

The format of UPDATE is

UPDATE tablename SET field=value, field=value, ... WHERE id=someid

So:


SQL = "UPDATE Purchasing SET Version = " & intVersionID & ", PurchaseNo = " & strPurchase _
& ", NoLicences = " & strLicences & ", Cost = " & strCost _
& ", DateOrder = #" & strDateo & "#, TypePayment = '" & strPayment & "'" _
& ", Supplier = '" & strSupplier & "', DateRenewal = #" & strDater & "#," _
& " Company = " & intDivision & ", Branch = " & intCity _
& " WHERE ID=" & intID
Response.Write "<hr>DEBUG SQL: " & SQL & "<hr>" & vbNewLine
Set rsUpdatePurchase = dbUpdatePurchase.Execute(SQL)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum