Results 1 to 5 of 5

Thread: syntax error in update statement

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2015
    Posts
    3

    Resolved syntax error in update statement

    Hey All.

    I'm having an issue with the Update Statement.
    This is what it is.

    Code:
    sql = "UPDATE Control SET [Date Issued] ='" & TestStr & " @ " & TestStrt _
                & "',[Ship Status] ='" & "Queued" _
                & "',[Job Number] ='" & MainMenu.JobNumber.Text _
                & "',[Customer] ='" & MainMenu.CustomerName.Text _
                & "',[DS Foreman] ='" & MainMenu.ForemanDS.Text _
                & "',[DS Foreman Phone] ='" & MainMenu.ForemanDSPhone.Text _
                & "',[NS Foreman] ='" & MainMenu.ForemanNS.Text _
                & "',[NS Foreman Phone] ='" & MainMenu.ForemanNSPhone.Text _
                & "',[Ship Date] ='" & MainMenu.DateTimePicker1.Text _
                & "',[Ship Time] ='" & MainMenu.ComboBox1.Text _
                & "',[On Site Date] ='" & MainMenu.DateTimePicker2.Text _
                & "',[On Site Time] ='" & MainMenu.ComboBox2.Text _
    ' ***** ERROR CODE FOUND IN THIS LINE *****
                & "' WHERE [Control Number] = '" & CONTROLNUMBER & " '"
            mysqlctd(sql)
    I have narrowed it down to the following command which has been removed from the above.

    Code:
     sql = "UPDATE Control SET [Ship Via]) ='" & MainMenu.Carrier.Text & "' WHERE [Control Number] = '" & CONTROLNUMBER & " '"
            mysqlctd(sql)
    My DB is access 2007
    the tiles is Ship Via
    the Combobox is Carrier.
    Everything is text as string

    I was able to verify the information is correct. ie

    Code:
    MsgBox("CARRIER UPDATE " & CarrierString)
    When I create the information it inserts the information, but I can not change the information, update.
    It updates everything else, without any issue.

    I have replaced the MainMenu.Carrier.Text with CarrierString and this comes back with the same error.
    I have also changed the DB name to Ship_Via, didn't work.
    Access Ship Via Column is Text Format

    If i'm missing anything, let me know.
    Last edited by sysop; Jan 19th, 2015 at 04:59 PM.

  2. #2

    Thread Starter
    New Member
    Join Date
    Jan 2015
    Posts
    3

    Re: syntax error in update statement

    Fixed it

    Code:
    sql = "UPDATE Control SET [Ship Via]) ='" & MainMenu.Carrier.Text & "' WHERE [Control Number] = '" & CONTROLNUMBER & " '"
            mysqlctd(sql)
    [Ship Via])

    removed ")"

    You get blind from looking at code to long.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,952

    Re: syntax error in update statement

    A couple things:
    1) generally speaking spaces in field or table names is a bad deal... may not be an issue today, but more often than not, it's going to cause heartburn for someone down the line.
    2) You say it's an Access database but treat the dates as strings... please tell me that you're not storing dates as strings... normally the date delimiter in Access is the pound sign.
    3) Parameters - learn how to use parameters... at some point you'll be glad you did. Likely the first time the DS Foreman's name is O'Bannion - the ' will break your SQL.
    4) Also if for nothing else, to avoid Little Bobby Tables issue.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2015
    Posts
    3

    Re: syntax error in update statement

    Thanks for the info
    #1 - I thought the bread box would have solved the spacing issue. I can change this over.
    #2 - this is just for testing right now. Date and time
    #3 - I have no idea on how to do this. I had to use the ` instead of the '. errors on 2' x 4' x 8'. are you able to help me on this.
    #4 - The tables are to insure that each person does there part.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,952

    Re: syntax error in update statement

    example of a parameterized query:

    Code:
    mySQL = "UPDATE someTable Set Field1 = ?, Field2 = ?, Field3 = ? Where KeyField = ?"
    'myCmd is a command object 
    myCmd.CommandText = mySQL
    
    ' Here the name doesn't matter (@P1) ... it's just used to satisfy the parameters to AddWithValue 
    ' What is important is the type of the variable/value passed in... as it will dictate how it is handled by the command object
    ' so if it is a date, make sure the type is a date, and not a string that looks like a date
    myCmd.Parameters.AddWithValue("@P1", {variable for the value of Field1}) 
    'Let's say that field2 is your date:
    myCmd.Parameters.AddWithValue("@P2", MainMenu.DateTimePicker2.Value)  'Note: Use value as that is a Date typed value... .Text will return a string
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width