Results 1 to 16 of 16

Thread: [RESOLVED] Syntax error in UPDATE statement

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Resolved [RESOLVED] Syntax error in UPDATE statement

    I seem to be unable to execute this query and am unable to see the error of my ways. When run, I keep getting this error message, "Syntax error in UPDATE statement."

    I run the following routine to save data that has been input to a form. I am completely unable to see the syntax error in my query. I therefore believe that the error is actually something else, but I have no idea what that could be.

    Code:
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            AddParams()
            UpdateRecord()
            SetState("View")
        End Sub

    Code:
        Private Sub AddParams()
            MasterBase.AddParam("@recno", lblEmployeeID.Text)
            MasterBase.AddParam("@first", txtFirstName.Text)
            MasterBase.AddParam("@middle", txtMiddleName.Text)
            MasterBase.AddParam("@last", txtLastName.Text)
            MasterBase.AddParam("@dept", cboDepartment.Text)
            MasterBase.AddParam("@title", txtJobTitle.Text)
            MasterBase.AddParam("@description", txtJobDescription.Text)
            MasterBase.AddParam("@hire", txtEmployed.Text)
            MasterBase.AddParam("@terminate", txtDischarged.Text)
            MasterBase.AddParam("@user", txtUser.Text)
            MasterBase.AddParam("@pass", txtPassword.Text)
            MasterBase.AddParam("@active", chkActive.Checked)
        End Sub
    Code:
        Private Sub UpdateRecord()
            MasterBase.MasterBaseQuery("UPDATE empEmployeeInformation " &
                                       "SET EmployeeID=@recno,FirstName=@first,MiddleName=@middle,LastName=@last," &
                                       "Department=@dept,JobTitle=@title,JobDescription=@description," &
                                       "Hire=@hire,Terminate=@terminate,UserName=@user,Password=@pass,Active=@active " &
                                       "WHERE EmployeeID=@recno")
            If NoErrors(True) = False Then Exit Sub
    Code:
            Public Sub MasterBaseQuery(MyQuery As String)
                RecordCount = 0
                Exception = ""
                Try
                    MasterBaseConnection.Open() 'Open connection
                    ListCommand = New OleDbCommand(MyQuery, MasterBaseConnection) 'Database Command
                    Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                    Params.Clear() 'Clear params list
                    ListTable = New DataTable
                    ListAdapter = New OleDbDataAdapter(ListCommand)
                    RecordCount = ListAdapter.Fill(ListTable)
                Catch ex As Exception
                    Exception = ex.Message
                End Try
                If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
            End Sub
            End Sub
    Code:
            Public Sub AddParam(Name As String, Value As Object)
                Dim NewParam As New OleDbParameter(Name, Value)
                Params.Add(NewParam)
            End Sub
    Last edited by gwboolean; Oct 13th, 2021 at 03:25 PM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Syntax error in UPDATE statement

    Long shot, but put "Password" in brackets in your query: "[Passwrod]=..."

    Password is generally a reserved word and has special meaning in databases. For what ever reasons databases will still let you create objects with those names though, but tio get around it, you just need to put object identifiers around them.

    -0tg
    * 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??? *

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Syntax error in UPDATE statement

    What field types are your database fields for Hire, Terminate, and Active?

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by jdc2000 View Post
    What field types are your database fields for Hire, Terminate, and Active?
    Hire and terminate are date fields. Active is a boolean field. The database is access. Additionally, I am using parameters, as you can see. I believe that I have done all that correctly, including order of the parameters.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by techgnome View Post
    Long shot, but put "Password" in brackets in your query: "[Passwrod]=..."

    Password is generally a reserved word and has special meaning in databases. For what ever reasons databases will still let you create objects with those names though, but tio get around it, you just need to put object identifiers around them.

    -0tg
    I went a step further and got rid of the word password from everything but the textbox on the form, which is named lblPassword. I forgot about reserved words. No joy though. Same error.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in UPDATE statement

    I think that this was addressed in another of your threads but while you can use parameter names in an Access query, Access ignores them and just uses positions to match parameters. That means that you must add the same number of parameters to your command and in they same order as they appear in your SQL code. I'm not sure whether it was mentioned explicitly but it should be obvious that that also means that you cannot add a parameter with a particular name and then use that name multiple times in the SQL and expect the same parameter to be used each time. Count how many parameters you have added to your command and then count how many @ symbols there are in your SQL code. Are they the same? No they are not. I don't think that would generate a syntax error but it is still a problem.

    Of course, one has to wonder why you're even trying to use the same parameter twice at all. What's the point of setting a column to a value when you're using that value in that column to identify the record to begin with? That isn't changing the value of that column so why include it at all?

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by gwboolean View Post
    the textbox on the form, which is named lblPassword.
    I hate Hungarian Notation to begin with but if you're going to use the wrong prefixes then it goes from useless to downright misleading. The prefix is supposed to indicate the type but the "lbl" prefix obviously doesn't indicate a TextBox.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by gwboolean View Post
    I went a step further and got rid of the word password from everything but the textbox on the form, which is named lblPassword. I forgot about reserved words. No joy though. Same error.
    Try escaping all singe-word columns, just in case there's another reserved word we don't realise.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in UPDATE statement

    Recent VB versions support multiline String literals and VB has always supported XML literals, so there's no reason to write nasty, hard-to-read (and therefore error-prone) code like this:
    Quote Originally Posted by gwboolean View Post
    Code:
        Private Sub UpdateRecord()
            MasterBase.MasterBaseQuery("UPDATE empEmployeeInformation " &
                                       "SET EmployeeID=@recno,FirstName=@first,MiddleName=@middle,LastName=@last," &
                                       "Department=@dept,JobTitle=@title,JobDescription=@description," &
                                       "Hire=@hire,Terminate=@terminate,UserName=@user,Password=@pass,Active=@active " &
                                       "WHERE EmployeeID=@recno")
            If NoErrors(True) = False Then Exit Sub
    I don't think you have made any errors due to the concatenation but I've seen plenty of people who have. Just avoid the possibility:

    XML literal:
    vb.net Code:
    1. Dim sql = <sql>
    2.             UPDATE  MyTable
    3.             SET     Column1 = @Column1
    4.                     Column2 = @Column2
    5.             WHERE   Id - @Id
    6.           </sql>.Value
    String literal:
    vb.net Code:
    1. Dim sql = "UPDATE  MyTable
    2.            SET     Column1 = @Column1
    3.                    Column2 = @Column2
    4.            WHERE   Id - @Id"
    Note that you will end up with some whitespace in your SQL code but that has no effect on the query and, generally speaking, the code window is the only place you'll be reading it so it doesn't matter at all.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Syntax error in UPDATE statement

    Code:
    WHERE   Id - @Id
    I assume that's a typo,

    Code:
    WHERE   Id = @Id

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by jmcilhinney View Post
    I think that this was addressed in another of your threads but while you can use parameter names in an Access query, Access ignores them and just uses positions to match parameters. That means that you must add the same number of parameters to your command and in they same order as they appear in your SQL code. I'm not sure whether it was mentioned explicitly but it should be obvious that that also means that you cannot add a parameter with a particular name and then use that name multiple times in the SQL and expect the same parameter to be used each time. Count how many parameters you have added to your command and then count how many @ symbols there are in your SQL code. Are they the same? No they are not. I don't think that would generate a syntax error but it is still a problem.

    Of course, one has to wonder why you're even trying to use the same parameter twice at all. What's the point of setting a column to a value when you're using that value in that column to identify the record to begin with? That isn't changing the value of that column so why include it at all?
    My effort to interpret what you are saying is that I should not use the @recno as both an update and the where part of the query.

    Interestingly, applying, what I believe you to be suggesting, does eliminate the errors. However, the record is still not updated.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by jmcilhinney View Post
    I hate Hungarian Notation to begin with but if you're going to use the wrong prefixes then it goes from useless to downright misleading. The prefix is supposed to indicate the type but the "lbl" prefix obviously doesn't indicate a TextBox.
    I am sorry to annoy you with what you call Hungarian notation. Am I supposed to know what that is?

    Anyway, while I do not always stick to, or even know, any particular naming protocol, the "lbl" prefix refers to a label and not a textbox. It is not clear to me what the significance of this is.

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by gwboolean View Post
    My effort to interpret what you are saying is that I should not use the @recno as both an update and the where part of the query.

    Interestingly, applying, what I believe you to be suggesting, does eliminate the errors. However, the record is still not updated.
    You CAN.. but you have to add the parameter TWICE... in the RIGHT location... as it currently is, you're only added it once.

    -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??? *

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by gwboolean View Post
    My effort to interpret what you are saying is that I should not use the @recno as both an update and the where part of the query.

    Interestingly, applying, what I believe you to be suggesting, does eliminate the errors. However, the record is still not updated.
    It's pretty simple stuff. When using Access, you can and, in my opinion, should name your parameters but those names are ignored by the database, so you MUST add the same number of parameters to your command and in the same order as appear in your SQL. The count and the order have to be the same. If you were using SQL Server then you would be able to add one parameter named "@MyParameter" and then use "@MyParameter" multiple times in your SQL code in any location and have each instance replaced with the same parameter value. As you're using Access, you can't do that. If you want to use the same value in multiple places in your SQL code then you have to add multiple parameters with the same value and different names. The count and the order have to be the same. This would be OK for SQL Server:
    vb.net Code:
    1. myCommand.CommandText = "UPDATE MyTable SET Column1 = @MyParameter, Column2 = @MyParameter WHERE Id = @Id"
    2. myCommand.Parameters.Add("@MyParameter", SqlDbType.VarChar, 50).Value = myParameter
    3. myCommand.Parameters.Add("@Id", SqlDbType.Int).Value = id
    The equivalent for Access:
    vb.net Code:
    1. myCommand.CommandText = "UPDATE MyTable SET Column1 = @MyParameter, Column2 = @MyParameter WHERE Id = @Id"
    2. myCommand.Parameters.Add("@MyParameter", OleDbType.VarChar, 50).Value = myParameter
    3. myCommand.Parameters.Add("@Id", OleDbType.Int).Value = id
    would not work, because there are three parameter placeholders in the SQL code and only two parameters added to the command. The count and the order have to be the same:
    vb.net Code:
    1. myCommand.CommandText = "UPDATE MyTable SET Column1 = @MyParameter1, Column2 = @MyParameter2 WHERE Id = @Id"
    2. myCommand.Parameters.Add("@MyParameter1", OleDbType.VarChar, 50).Value = myParameter
    3. myCommand.Parameters.Add("@MyParameter2", OleDbType.VarChar, 50).Value = myParameter
    4. myCommand.Parameters.Add("@Id", OleDbType.Int).Value = id

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in UPDATE statement

    Quote Originally Posted by gwboolean View Post
    I am sorry to annoy you with what you call Hungarian notation.
    Your concern should not be whether or not you annoy me, which I'm sure it isn't. Your concern should be whether you're writing good code, which I'm not sure is the case.
    Quote Originally Posted by gwboolean View Post
    Am I supposed to know what that is?
    It should be fairly easy to infer from the rest of the post but you could always spend 30 seconds with a search engine to find out.
    Quote Originally Posted by gwboolean View Post
    while I do not always stick to, or even know, any particular naming protocol
    You should. Even if it's one that I don't like, you should determine what you think is a good convention and stick to it. Consistency is important when writing code. If you do the same thing in multiple places to mean two different things or do different things to mean the same thing then that's a great way to create confusion. It's easy to confuse yourself even, if you put some code down for a while and pick it up later. We've all been there.
    Quote Originally Posted by gwboolean View Post
    the "lbl" prefix refers to a label and not a textbox. It is not clear to me what the significance of this is.
    You said that it was a TextBox, so that's what misled me. That said, it should be very rare that you use a Label for a password. Entering a password into a TextBox is one thing but a Label is for display only. Passwords should pretty much always be masked by default, which is why a TextBox has that ability built in. Displaying a password in clear text by default is a security risk. Using a Label for storage rather than display is an abuse of a UI element.

    As for Hungarian Notation, using prefixes on variables made sense back when we all used simple text editors to write code. In a tool like Visual Studio though, it serves little useful purpose. I could go into much more detail but I don't feel inclined so will leave it at that.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Syntax error in UPDATE statement

    Your concern should not be whether or not you annoy me, which I'm sure it isn't. Your concern should be whether you're writing good code, which I'm not sure is the case.
    My concern is that the code I write is effective, and when possible efficient. I write code as an interesting hobby and the code I write is for no one but me. My code being, "good" is not a factor, or a requirement.

    It should be fairly easy to infer from the rest of the post but you could always spend 30 seconds with a search engine to find out.
    If what you state is true then I would not be wasting my time trying to cajole occasionally useful information from you.

    You should. Even if it's one that I don't like, you should determine what you think is a good convention and stick to it. Consistency is important when writing code. If you do the same thing in multiple places to mean two different things or do different things to mean the same thing then that's a great way to create confusion. It's easy to confuse yourself even, if you put some code down for a while and pick it up later. We've all been there.
    What you say is true. However, I would also suggest to you that I am merely a hobbyist and do not have the time or resources to spend learning all of the current jargon and/or the numerous, and often confusing, conventions.

    You said that it was a TextBox, so that's what misled me. That said, it should be very rare that you use a Label for a password. Entering a password into a TextBox is one thing but a Label is for display only. Passwords should pretty much always be masked by default, which is why a TextBox has that ability built in. Displaying a password in clear text by default is a security risk. Using a Label for storage rather than display is an abuse of a UI element.
    Sorry if I confused you. There is a label, lblEmployed, There is a checkbox, chkActive, and all of the rest are textboxes.

    Having said all of that, and after spending a couple of hours digging through some old material I had, it became clear to me that, in spite of your unwillingness to clearly state what was wrong, you had indeed provided the answer to the problem and that it was indeed the fact that the parameter, @recno was in the wrong order and should have been the last parameter and not the first. I suppose you were just providing me with a, "teaching moment". While I find teaching moments to be irritating, that is fair enough.

    However, there was an additional problem and that was with the two date fields that required update. I handled that, rather poorly, in the following manner.

    Code:
        Private Sub AddParams()
            BldCmd = ""
            MasterBase.AddParam("@firstname", txtFirstName.Text)
            MasterBase.AddParam("@midname", txtMiddleName.Text)
            MasterBase.AddParam("@lastname", txtLastName.Text)
            MasterBase.AddParam("@department", cboDepartment.Text)
            MasterBase.AddParam("@jobtitle", txtJobTitle.Text)
            MasterBase.AddParam("@description", txtJobDescription.Text)
            If Not String.IsNullOrWhiteSpace(txtEmployed.Text) Then
                MasterBase.AddParam("@hire", txtEmployed.Text)
                BldCmd += "Hire=@Hire,"
            End If
            If Not String.IsNullOrWhiteSpace(txtDischarged.Text) Then
                MasterBase.AddParam("@term", txtDischarged.Text)
                BldCmd += "Terminate=@term,"
            End If
            MasterBase.AddParam("@user", txtUser.Text)
            MasterBase.AddParam("@pass", txtPassword.Text)
            MasterBase.AddParam("@active", chkActive.Checked)
            MasterBase.AddParam("@recno", lblEmployeeID.Text)
        End Sub
    Code:
        Private Sub UpdateRecord()
            MasterBase.MasterBaseQuery("UPDATE empEmployeeInformation " &
                                       "SET FirstName=@firstname,MiddleName=@midname,LastName=@lastname," &
                                       "Department=@department,JobTitle=@jobtitle,JobDescription=@description," &
                                       BldCmd & "UserName=@user,Pass=@pass,Active=@active " &
                                       "WHERE EmployeeID=@recno")
            If NoErrors(True) = False Then Exit Sub
        End Sub
    I am not particularly happy with this, but it is effective. It is unlikely that it is even close to efficient. As for good, I would not know.

Tags for this Thread

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