Crome Bash Crome Bash - 2 years ago 74 Question

Updating MySQL record not working using parameters

Good day, I'm having a problem updating records in MySQL. The following code is in VB.Net. Whenever I execute the code I get no errors, but the information is not saved in the table. Please tell me what I am doing wrong.

SQLstr = "UPDATE mainfinancials SET charge1=?charge1, charge2=?charge2, charge3=?charge3, charge4=?charge4, charge5=?charge5" _
& " WHERE acct='?acct';"
Pcomm.CommandText = SQLstr
If IsNumeric(txtCharge1.Text) Then Pcomm.Parameters.AddWithValue("?charge1", CDbl(txtCharge1.Text))
If IsNumeric(txtCharge2.Text) Then Pcomm.Parameters.AddWithValue("?charge2", CDbl(txtCharge2.Text))
If IsNumeric(txtCharge3.Text) Then Pcomm.Parameters.AddWithValue("?charge3", CDbl(txtCharge3.Text))
If IsNumeric(txtCharge4.Text) Then Pcomm.Parameters.AddWithValue("?charge4", CDbl(txtCharge4.Text))
If IsNumeric(txtCharge5.Text) Then Pcomm.Parameters.AddWithValue("?charge5", CDbl(txtCharge5.Text))
Pcomm.Parameters.AddWithValue("?acct", txtAcct.Text)
Catch ex As MySqlException
End Try

I have deliberately place error in the the code and it would return an error message.
can some one help me with this.

Answer Source

Do not enclose a parameter placeholder with single quotes

" WHERE acct=?acct;"

Enclosing the parameter placeholder in single quotes trasform its name in a literal value. So you query is searching a record where the acct column contains the literal value '?acct'.
Of course it finds nothing and nothing is updated

By the way, once a parameter placeholder is present in the query string, you should supply a parameter for it. If you forget to add the parameter an error occurs when you execute the command stating that an expected parameter is missing.

You place a test for IsNumeric and only if it succeds you add the parameter. I think you should test this error condition before and abort the update if something is not correct in your charge parameters.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download