Updating MYSQL records via VB.NET

Question

What I am trying to do is do the basic insert, refresh and update mysql querys in my vb.net application. The refresh, and the insert work perfect the only one I can't get is the update can anyone tell me what I am doing wrong? Here is a picture of the program layout:

https://i.stack.imgur.com/j0k96.png

and here is my source code:

Private Sub KnightButton3_Click(sender As Object, e As EventArgs) Handles KnightButton3.Click
    cn = New MySqlConnection
    cn.ConnectionString = "my info"

    Try
        cn.Open()
        Dim query As String
        Dim command As MySqlCommand
        query = "UPDATE Refers.exploitsociety SET Refferals='" + refupdate.Text + "' WHERE Refferals='" + DataGridView1.CurrentCell.Selected + "';"
        command = New MySqlCommand(query, cn)
        cmd.ExecuteNonQuery()

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        cn.Dispose()
    End Try
    cn.Close()
End Sub

The first thing I would do is put a break point on the "query =" line, then look at the value of DataGridView1.CurrentCell.Selected and make sure that value is actually in the table you want to update. Also side note, parameterize your query to protect against SQL injection exploits. — b.pell, Apr 03 '15 at 19:06
Any example of code? I have probably been on every topic and I just can't get it, I am new to this buddy, thanks for the reply! — Jake, Apr 03 '15 at 19:08
But did you have any error here? Any message in that exception that you would like to share? — Steve, Apr 03 '15 at 19:11
@steve Additional information: Conversion from string "UPDATE exploitsociety SET reffer" to type 'Double' is not valid. I have another thread here too his no success: http://stackoverflow.com/questions/29426103/updating-datagridview-with-mysql — Jake, Apr 03 '15 at 19:16

Steve · Accepted Answer · 2015-04-03T19:38:18.847

0

I think your problem is in the usage of the property Selected of the CurrentCell. This is a boolean value and it is not the content of that cell. I would use this kind of code with a parameterized query and a using statement around disposable objects

Try
     query = "UPDATE Refers.exploitsociety " & _ 
             "SET Refferals=@refs " & _ 
             "WHERE Refferals=@oldrefs"

    Using cn = New MySqlConnection(".... connection string ....")
    Using command = New MySqlCommand(query, cn)
        cn.Open()
        command.Parameters.Add("@refs", MySqlDbType.Int32).Value = _
                               Convert.ToInt32(refupdate.Text)
        command.Parameters.Add("@oldrefs", MySqlDbType.Int32).Value = _
                Convert.ToInt32(DataGridView1.CurrentCell.Value.ToString())
        command.ExecuteNonQuery()
    End Using
    End Using
Catch ex As MySqlException
    MessageBox.Show(ex.Message)
End Try

Notice that with a using statement you don't need to close/dispose the connection because this is automatically done when the code leaves the using block (also in case of exceptions)

edited Apr 03 '15 at 19:38

answered Apr 03 '15 at 19:19

Steve

213,761
22
232
286

can I ask what @refs is? Thank you, Could you also add one that isn't parameterized, cause I don't get that one bit. I am sorry./ – Jake Apr 03 '15 at 19:21
`@refs` and `@oldrefs` are placeholders for the parameters. Using this approach you don't need to put quotes around your string and avoid Sql Injection – Steve Apr 03 '15 at 19:22
Additional information: Connection must be valid and open. I get that when I replace my code with yours – Jake Apr 03 '15 at 19:25
@Jake I will not give you an answer without a parameters because it is a really wrong thing to do. First [look at this famous question](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) that explains Sql Injection. String concatenation is dangerous at the extreme. Second, with a string concatenation if your referrals valus contains a single quote the whole code will crash again with a syntax error. Third, your query text now is really simple to understand. – Steve Apr 03 '15 at 19:25
Additional information: Connection must be valid and open. I get that when I replace my code with yours – – Jake Apr 03 '15 at 19:27
Of course you set the connection string as before right? My code `....connection string....` is just a markup where you should put the correct details of your connection string. (yours `"my info"` ) – Steve Apr 03 '15 at 19:29
Please view the picture here: http://i.imgur.com/SiFPnG3.png also the Refferals is INT – Jake Apr 03 '15 at 19:30
Yes lol I set my connection string, I am dumb but not that dumb :D – Jake Apr 03 '15 at 19:30
Well, excuse me then, only there is no reason to get that error if the MySqlCommand constructor receives both the query string and the connection. Finally if the DataType of Referrals is an integer then the parameter should be of type integer and the value passed to the parameter should be of the same type. I will update the answer – Steve Apr 03 '15 at 19:33
I get this error: Additional information: Connection must be valid and open. – Jake Apr 03 '15 at 19:36
Okay so that worked! (kinda) This changed every cell to 20 of the Refferals column – Jake Apr 03 '15 at 19:40
NVM SIR IT WORKED! Thank you so much. I checked this answer, can you explain this code if not that is fine – Jake Apr 03 '15 at 19:41
Some links http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection http://www.4guysfromrolla.com/webtech/092601-1.shtml https://msdn.microsoft.com/en-us/library/htd05whh.aspx http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/ – Steve Apr 03 '15 at 19:52

Updating MYSQL records via VB.NET

1 Answers1