Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    converting a select statement to a update statement

    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.
    Last edited by bianx_07; 09-29-2011 at 08:27 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Start by writing your code so it can be debugged.
    Code:
    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
    Code:
        UPDATE tablename SET field=value, field=value, ... WHERE id=someid
    So:
    Code:
    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)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •