mysql Update a column

Question

i am new to Mysql in vb.net.

what I want is when I click the save button, I want the mysql database is updated and that the collumn Wcoins get updated and it will get a value that is in Label2.Text

my code:

        Dim conn As MySqlConnection
    conn = New MySqlConnection("server=REMOVED;Port=REMOVED; user id=REMOVED; password=REMOVED; database=REMOVED")
    Dim username As Boolean = True
    conn.Open()
    Dim sqlquery As String = "UPDATE * FROM NewTable WHERE Wcoins=label2.text"
    Dim data As MySqlDataReader
    Dim adapter As New MySqlDataAdapter
    Dim command As New MySqlCommand
    command.CommandText = sqlquery
    command.Connection = conn
    adapter.SelectCommand = command
    data = command.ExecuteReader

    data.Close()
    conn.Close()

i made some mistakes but i was trying to update Wcoins so it will delete the old value and it will get the value that is displayed in label2.text

any help would be realy apreciated

i'm dutch so if you see spelling mistakes please correct them

I think you need to review a bit of SQL before. [Update Syntax](http://www.w3schools.com/SQl/sql_update.asp) — Steve, Sep 14 '13 at 19:58

score 0 · Answer 1 · edited May 23 '17 at 12:05

The correct syntax for the Update SQL statement is

 UPDATE <table> SET <field>=<value> WHERE <PrimaryKey> = <valueForPK>

So your correct command text should be something like

 UPDATE NewTable SET Wcoins = <value> WHERE ????? = ??????

I don't know what is the name of the Primary Key of your table and this is essential to give you a correct answer because without the WHERE part the UPDATE command will change every record of your table and I suppose that this is not your intention,

Finally, you code is wrong because an UPDATE command (like INSERT or DELETE) will be executed using the MySqlCommand.ExecuteNonQuery() and not using the ExecuteReader.
There are other point to underline in your code like the string concatenation to form the command text (Sql Injection and parsing errors) but at this point it is better to fix first the UPDATE problem.

EDIT Now that you have given more info on your column's name I could update my answer to show how I would write that code

Dim sqlquery = "UPDATE NewTable SET Wcoins=@newValue WHERE Name=@nameValue"
Using conn = New MySqlConnection(.........)
Using command = New MySqlCommand(sqlquery, conn)
    conn.Open()
    command.Parameters.AddWithValue("@newValue", label2.Text)
    command.Parameters.AddWithValue("@nameValue", label1.Text)
    command.ExecuteNonQuery()
End Using
End Using

Using Statement to be certain that the connection is Always closed even if you have an exception
Parameters to add the value for your command without using string concatenations (Sql Injection)
ExecuteNonQuery for an UPDATE query instead of using a DataAdapter and all the necessary code for it

score 0 · Accepted Answer · answered Sep 14 '13 at 21:08

Yahh i tried some codes and i found a working one.

Code:

        Dim conn As MySqlConnection
    conn = New MySqlConnection("server=REMOVED;Port=REMOVED; user id=REMOVED; password=REMOVED; database=REMOVED")
    Dim username As Boolean = True
    conn.Open()
    Dim sqlquery As String = "UPDATE NewTable SET Wcoins='" & Label2.Text & "' WHERE Name='" & Label1.Text & "';"
    Dim data As MySqlDataReader
    Dim adapter As New MySqlDataAdapter
    Dim command As New MySqlCommand
    command.CommandText = sqlquery
    command.Connection = conn
    adapter.SelectCommand = command
    data = command.ExecuteReader

    data.Close()
    conn.Close()

thank you all for helping me

mysql Update a column

2 Answers2