Updating MySQL record not working using parameters

Question

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)
        MsgBox(SQLstr)
        Try
            PConn.Open()
            Pcomm.ExecuteNonQuery()
            PConn.Close()
            PConn.Dispose()
        Catch ex As MySqlException
            MsgBox(ex.Message.ToString())
            PConn.Close()
        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. Thanks

Thanks Steve, it worked. Need to spend more time learning and using .NET I keep spending more of my time in VB6. — Crome Bash
– Crome Bash, Commented Dec 28, 2013 at 16:39

Steve · Accepted Answer · 2013-12-26 17:35:18Z

1

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.

edited Dec 26, 2013 at 17:35

answered Dec 26, 2013 at 17:29

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating MySQL record not working using parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related