How to fix syntax error in update statement vb.net?-CodePudding

My Code are:

Dim cmdUpdate As New OleDbCommand("Update Account Name'" & txtAccountName.Text & "', Age='" & txtAccountAge.Text & "', Degress='" & cboAccountDegress.Text & "', Birthday='" & txtAccountBirthday.Text & "', Address='" & txtAccountAddress.Text & "', Number='" & txtAccountNumber.Text & "', Gender='" & txtAccountNumber.Text & "' where ID=" & txtAccountID.Text & "", conn)

            cmdUpdate.Parameters.AddWithValue("@ID", txtAccountID.Text)
            cmdUpdate.Parameters.AddWithValue("@Name", txtAccountName.Text)
            cmdUpdate.Parameters.AddWithValue("@Age", txtAccountAge.Text)
            cmdUpdate.Parameters.AddWithValue("@Degress", cboAccountDegress.Text)
            cmdUpdate.Parameters.AddWithValue("@Birthday", txtAccountBirthday.Text)
            cmdUpdate.Parameters.AddWithValue("@Address", txtAccountAddress.Text)
            cmdUpdate.Parameters.AddWithValue("@Number", txtAccountNumber.Text)
            cmdUpdate.Parameters.AddWithValue("@Gender", cboAccountGender.Text)

            conn.Open()
            cmdUpdate.ExecuteNonQuery()
            conn.Close()

            bind_data()

CodePudding user response：

Your UPDATE should make use of Parameter place holders like:

Dim cmdUpdate As New OleDbCommand("Update Account SET Name=@Name, Age=@Age, Degress=@Degress, Birthday=@Birthday, Address=@Address, Number=@Number, Gender=@Gender where ID=@ID", conn)

Your UPDATE statement also had a missing SET keyword

And then alter the order in which you add parameters to put the Id parameter last:

cmdUpdate.Parameters.AddWithValue("@Name", txtAccountName.Text)
cmdUpdate.Parameters.AddWithValue("@Age", txtAccountAge.Text)
cmdUpdate.Parameters.AddWithValue("@Degress", cboAccountDegress.Text)
cmdUpdate.Parameters.AddWithValue("@Birthday", txtAccountBirthday.Text)
cmdUpdate.Parameters.AddWithValue("@Address", txtAccountAddress.Text)
cmdUpdate.Parameters.AddWithValue("@Number", txtAccountNumber.Text)
cmdUpdate.Parameters.AddWithValue("@Gender", cboAccountGender.Text)
cmdUpdate.Parameters.AddWithValue("@ID", txtAccountID.Text)

For connections based on OleDb, the parameter order in the SQL statement should match the order the parameters are added to the Parameters collection

CodePudding user response：

You should use SQL Server Update Function

UPDATE TableName

Set the column name you want to update

SET Column1 = @Column1 ,Column2 = @Column2

And define the condition using where

Where Column3 = ‘Condition’