Syntax error in UPDATE statement

Question

Hi all, I am trying to finish a final project in my VB2 class. I am getting the syntax error in the UPDATE statement. I have tried everything I can think of and yet still get the same error.

This is my code as is now....

Please help me figure this out I have to present in 12 hours.

Dim UpdateQuery = "UPDATE Employee SET FirstName=?, LastName=?, Position=?, Salary=? WHERE ID=?"

        Dim UpdateCommand As New OleDb.OleDbCommand(UpdateQuery, CasinoModule.Connection)

        UpdateCommand.Parameters.AddWithValue("@ID", txtID.Text)
        UpdateCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
        UpdateCommand.Parameters.AddWithValue("@LastName", txtLastName.Text)
        UpdateCommand.Parameters.AddWithValue("@Position", txtPosition.Text)
        UpdateCommand.Parameters.AddWithValue("@Salary", txtSalary.Text)

Answer 1

Are you sure all columns are text columns? You are assigning the Text property, which is of type String, to the parameters, so the columns would have to be text, too. You must convert the input to the type of the column in the database. For example, if Salary is of a numeric type, you can write

    UpdateCommand.Parameters.AddWithValue("@Salary", Decimal.Parse(txtSalary.Text))

Note that this does not contain input validation, i.e. invalid input that cannot be converted to a Decimal value would lead to an exception. For this purpose call Decimal.TryParse instead. It returns whether the input was valid or not (True or False).

Decimal is only an example.

---

Armin

Answer 2

Thank you Armin, I followed your advice but now it is throwing this error.

An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll

Additional information: Input string was not in a correct format.

Answer 3

How did you change the code and what are the types of the columns in the database? Did you make the change for all required columns. Mine was only an example. The ID is probably numeric, too.

---

Armin

Answer 4

Also try the next variant:

    Dim UpdateQuery = "UPDATE Employee SET FirstName=@FirstName, LastName=@LastName, Position= @Position, Salary=@Salary WHERE ID=@ID"

Answer 5

Your code is for OleDB, the parameters in that have to be given in the order like in the SQL transact code therefore id as the last one. 

Although you can use names, are those not used to recognize the parameters.

UpdateCommand.Parameters.AddWithValue("?", txtSalary.Text)

Be aware that using an @ is just a convention, there can be used every character by that. It does not denote really a parameter.

I assume by this reply, that the fields in the database are all varchar (strings)

---

Success
Cor

Answer 6

The order in which you add the parameters matters when working with an OleDbCommand: http://stackoverflow.com/questions/2407685/oledbparameters-and-parameter-names

Try this:

Dim UpdateQuery = "UPDATE Employee SET FirstName=?, LastName=?, Position=?, Salary=? WHERE ID=?"

         Dim UpdateCommand As New OleDb.OleDbCommand(UpdateQuery, CasinoModule.Connection)

         UpdateCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
         UpdateCommand.Parameters.AddWithValue("@LastName", txtLastName.Text)
         UpdateCommand.Parameters.AddWithValue("@Position", txtPosition.Text)
	UpdateCommand.Parameters.AddWithValue("@Salary", Decimal.Parse(txtSalary.Text))
         UpdateCommand.Parameters.AddWithValue("@ID", txtID.Text)

Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question.