CODEWITHSUNDEEP
mysqlvb.net
user1914165
user1914165

Reputation: 11

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

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.

Upvotes: 1

Views: 2455

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112682

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.

Upvotes: 1

Steve
Steve

Reputation: 216343

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)

Upvotes: 1

NYCdotNet
NYCdotNet

Reputation: 4647

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.)

Upvotes: 0

Related Questions