[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
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
Re: Syntax error in UPDATE statement
What field types are your database fields for Hire, Terminate, and Active?
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
jdc2000
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.
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
techgnome
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.
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?
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
gwboolean
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.
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
gwboolean
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.
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
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:
Dim sql = <sql>
UPDATE MyTable
SET Column1 = @Column1
Column2 = @Column2
WHERE Id - @Id
</sql>.Value
String literal:
vb.net Code:
Dim sql = "UPDATE MyTable
SET Column1 = @Column1
Column2 = @Column2
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.
Re: Syntax error in UPDATE statement
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
jmcilhinney
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.
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
jmcilhinney
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.
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
gwboolean
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
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
gwboolean
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:
myCommand.CommandText = "UPDATE MyTable SET Column1 = @MyParameter, Column2 = @MyParameter WHERE Id = @Id"
myCommand.Parameters.Add("@MyParameter", SqlDbType.VarChar, 50).Value = myParameter
myCommand.Parameters.Add("@Id", SqlDbType.Int).Value = id
The equivalent for Access:
vb.net Code:
myCommand.CommandText = "UPDATE MyTable SET Column1 = @MyParameter, Column2 = @MyParameter WHERE Id = @Id"
myCommand.Parameters.Add("@MyParameter", OleDbType.VarChar, 50).Value = myParameter
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:
myCommand.CommandText = "UPDATE MyTable SET Column1 = @MyParameter1, Column2 = @MyParameter2 WHERE Id = @Id"
myCommand.Parameters.Add("@MyParameter1", OleDbType.VarChar, 50).Value = myParameter
myCommand.Parameters.Add("@MyParameter2", OleDbType.VarChar, 50).Value = myParameter
myCommand.Parameters.Add("@Id", OleDbType.Int).Value = id
Re: Syntax error in UPDATE statement
Quote:
Originally Posted by
gwboolean
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
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
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
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.
Re: Syntax error in UPDATE statement
Quote:
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.
Quote:
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.
Quote:
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.
Quote:
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.
