VB.net update query is not giving errors and not updating my sql database

Question

Dim conntps As MySqlConnection
Dim myconnstringtps As String
conntps = New MySqlConnection()
Dim mycommand As New MySqlCommand
Dim Updatepayments As String = "update payments set payments.payorname='" & _
    epayorname.Text & "', payments.cardnumber='" & eccnumber.Text & _
    "', payments.bankname='" & ebankname.Text & "', payments.checkaccountnumber='" & _
    eaccountnumber.Text & "', payments.checkroutingnumber='" & _
    erouting.Text & "', payments.cardexpirationdate='" & eexpmonth.Text & "/" & _
    eexpireyear.Text & "', payments.cardexpirationmonth='" & _
    eexpmonth.Text & "', payments.cardexpirationyear='" & eexpireyear.Text & _
    "', payments.cardaddress='" & eaddy.Text & "', payments.cardzipcode='" & _
    ezip.Text & "', payments.threedigitnumber='" & ecvv.Text & _
    "' where payments.filenumber='" & TextBox1.Text & "' and paymentstatus='PENDING';"
myconnstringtps = "server=localhost; user id=root; " & _
                  "password=1C0cac0la; database=collectionsmax"
Try
    conntps.Open()
    Try
        mycommand.Connection = conntps
        mycommand.CommandText = Updatepayments
        mycommand.ExecuteNonQuery()
        conntps.Close()
        mycommand.Dispose()
    Catch myerror As MySqlException
        MsgBox("error connecting:" & myerror.Message)
    End Try
Catch myerror As MySqlException
    MsgBox("error connecting:" & myerror.Message)
Finally
    If conntps.State <> ConnectionState.Closed Then conntps.Close()
    MsgBox("Successfully Changed")
End Try

I am not getting any errors or exceptions when attempting to run the code.

I have tried to output the generated update query to a text box and running the code though mysql management studio, and it works perfectly. so im pretty sure its not an issue with the actual query being sent to the server.

I have used almost this exact same code to do insert into statements with no issues.

It is not updating the database when the code is ran through my VB.net application using the above outlined code.

My VB is a little rusty, but do you need 2 lines: "Dim conntps As SqlConnection" and "conntps = New MySqlConnection()"? — Melanie
– Melanie, Commented Dec 18, 2012 at 22:10

Steve · Accepted Answer · 2012-12-18 22:50:43Z

1

You don't set the connection string in the MySqlConnection

myconnstringtps = "server=localhost; user id=root; password=1C0cac0la;......"
conntps = New MySqlConnection(myconnstringtps)

apart from that, you need to use parametrized query to avoid problems with single quotes inside your strings and the Sql Injection Attack security problem

Dim Updatepayments As String = "update payments " & _
    "set payments.payorname=@name," & _
    "payments.cardnumber=@cnum," & _
    "payments.bankname=@bank," & _
    "payments.checkaccountnumber=@actnum," & _
    "payments.checkroutingnumber=@routing," & _
    "payments.cardexpirationdate=@monthyear," & _
    "payments.cardexpirationmonth=@month," & _
    "payments.cardexpirationyear=@year," & _
    "payments.cardaddress=@address," & _
    "payments.cardzipcode=@zip," & _
    "payments.threedigitnumber=@digits " & _
    "where payments.filenumber=@file and paymentstatus='PENDING'"

Dim mycommand As New MySqlCommand(Updatepayments, conntps)
mycommand.Parameters.AddWithValue("@name", epayorname.Text)
mycommand.Parameters.AddWithValue("@cnum", eccnumber.Text)
mycommand.Parameters.AddWithValue("@bank", ebankname.Text)
mycommand.Parameters.AddWithValue("@actnum", eaccountnumber.Text);
mycommand.Parameters.AddWithValue("@routing", erouting.Text)
mycommand.Parameters.AddWithValue("@monthyear", eexpmonth.Text & "/" &  eexpireyear.Text)
mycommand.Parameters.AddWithValue("@month", eexpmonth.Text)
mycommand.Parameters.AddWithValue("@year", eexpireyear.Text)
mycommand.Parameters.AddWithValue("@address", eaddy.Text)
mycommand.Parameters.AddWithValue("@zip", ezip.Text)
mycommand.Parameters.AddWithValue("@digits", ecvv.Text)
mycommand.Parameters.AddWithValue("@file", TextBox1.Text)

Other problematic point: Are you sure that your fields are all of string type? You pass for every field a string and surround the value with single quotes. This could fail if someone of your fields are not of string type. (these fields in particular could be not of string type payments.cardnumber, payments.checkaccountnumber, payments.cardexpirationmonth,payments.cardexpirationyear,payments.threedigitnumber)

edited Dec 18, 2012 at 22:50

answered Dec 18, 2012 at 22:18

Steve

217k22 gold badges242 silver badges296 bronze badges

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

5 Comments

user1914165 Over a year ago

steve i updated as you have advised. same result no update to actual server. i am running a select * from table query in mysql studio running program with code adjusted msg box shows that its successful and no errors caught then re-running select * from table query no change in data.

Steve Over a year ago

Strange, a possibility is the WHERE clause not finding the record to update. Another one (very improbable) is the database is not the same. The fields are all of type TEXT?

Steve Over a year ago

Also change the ExecuteNonQuery to Dim cnt = mycommand.ExecuteNonQuery() and check how may rows are affected by the query

user1914165 Over a year ago

No steve i got it, it works perfectly! i forgot to put mycommand.excutenonquery() at the end of the parameters! THANK YOU SOOOOO SOOOO MUCH!

Steve Over a year ago

Well, glad to be of help, please remember to accept the answer when the site allows you. Good work.

Olivier Jacot-Descombes · Accepted Answer · 2012-12-19 15:50:20Z

1

Use command parameters. This makes it both safer (SQL injection) and easier to handle.

Dim Updatepayments As String = "UPDATE payments SET payments.payorname=@1, " & _
    "payments.cardnumber=@2, ..." & _
    "WHERE payments.filenumber=@11 AND paymentstatus='PENDING';"

mycommand.Parameters.AddWithValue("@1", epayorname.Text);
mycommand.Parameters.AddWithValue("@2", eccnumber.Text);
...

You can also use parameter names like @epayorname with SQL-Server but some connection types (like ODBC) only allow positional parameters.

edited Dec 19, 2012 at 15:50

answered Dec 18, 2012 at 22:29

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Comments

NYCdotNet · Accepted Answer · 2012-12-18 22:22:16Z

0

Red alert You are obviously dealing with credit card information here and yet you are leaving yourself and your customers vulnerable to SQL injection attacks!

Also you have a password in your code that you posted on the public Internet!

(And Steve seems to have the right answer.)

answered Dec 18, 2012 at 22:22

NYCdotNet

4,6771 gold badge27 silver badges31 bronze badges

1 Comment

user1914165 Over a year ago

its a fake connection string edited for posting... password, database what have you are NOT REAL. The issue here is not my vulnerability nycdotnet. but thank you for pointing that out.

Collectives™ on Stack Overflow

VB.net update query is not giving errors and not updating my sql database

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related