CODEWITHSUNDEEP
mysqlvb.net
Tim Rowley
Tim Rowley

Reputation: 13

MySQL Data NOT Deleting from table VB.NET

Why won't this delete the data in my MySQL database!?

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click

 Dim dbCon As MySqlConnection      
 Dim strQuery As String = ""           
 Dim SQLCmd As MySqlCommand            
 Dim DR As MySqlDataReader            
 Try               
 dbCon = New MySqlConnection("Server=Localhost;Database=myusers;Uid=root;Pwd=Mypassword")

        strQuery = "DELETE settings FROM settings WHERE user=" & Me.loginuser.Text
        '* FROM settings WHERE user = "Testuser"'
        SQLCmd = New MySqlCommand(strQuery, dbCon)

        ' OPEN THE DB AND KICKOFF THE QUERY 
        dbCon.Open()

        DR = SQLCmd.ExecuteReader

        While DR.Read
            req1.Text = "" And exlink.Text = ""
        End While

        ' DONE! Close DB
        DR.Close()
        dbCon.Close()
    Catch ex As Exception
        TextBox8.Text = ("Fail" & vbCrLf & vbCrLf & ex.Message)
    End Try

Here is a picture of my database:

enter image description here

Alternatively I could somehow make it replace what is already in the database, in which case please help me with that.

Upvotes: 0

Views: 3424

Answers (3)

Steve
Steve

Reputation: 216293

I will try to change your code in this way

Using con = New MySqlConnection("Server=.....")
     con.Open()
     Dim sqlText = "DELETE * FROM settings WHERE user = @ulogin"
     Using cmd = new MySqlCommand(sqlText, con)
        cmd.Parameters.AddWithValue("@ulogin",  Me.loginuser.Text)
        cmd.ExecuteNonQuery()
     End Using
End Using

First and foremost, do not use string concatenation to create command texts to pass to the database engine. In that way you risk Sql Injections, also, if the user name contains a single quote (i.e. O'Hara) your code will fail with a syntax error (Same problems arise for date formatting, parsing numeric decimals and other globalization issues). Instead a parametrized query like the one in code above will avoid all of these problems.

In a parametrized query, the text of the query doesn't contains the actual value for the search condition or the update or insert data. Instead it contains placeholders ( in our case it is called @ulogin). The correct value to insert at the placeholders position is specified using one or more MySqlParameter added to the Parameters collection of the MySqlCommand. In this case I have used the AddWithValue method that derives the correct datatype directly from the datatype of the value. Because Me.loginuser.Text is a string value then the parameter will be treated as a string value replacing incorrect single quotes and removing extraneus characters usually used to Mount Sql Injections Attacks. The engine will do the rest inserting the correct value at the placeholder at execution time

EDIT: Seeing your comment about the MySql connector used, I will try to update my answer to show a semi-equivalent version for NET 1.

Try
     Dim con As MySqlConnection = New MySqlConnection("Server=.....")
     con.Open()
     Dim sqlText as String = "DELETE * FROM settings WHERE user = @ulogin"
     Dim cmd As MySqlCommand = new MySqlCommand(sqlText, con)
     Dim par As MySqlParameter = New MySqlParameter("@ulogin", MySqlDbType.VarChar)
     par.Value = Me.loginuser.Text
     cmd.Parameters.Add(par)
     cmd.ExecuteNonQuery()
Finally
     con.Close()
End Try

I am not sure if the connector 1.0.10 supports the parameter name with the @ prefix or just the : prefix

Upvotes: 1

Reverend Jim
Reverend Jim

Reputation: 31

Try

strQuery = "DELETE FROM settings " _
         & " WHERE user = '" & Me.loginuser.Text & "'"

but as was stated earlier, you should be using parameterized queries. If you had a user named O'Brien then your query (as composed above) would fail because of the embedded single quote. When you use DELETE, you are deleting entire records and you already specify the table name in the FROM clause.

Upvotes: 1

bizzehdee
bizzehdee

Reputation: 21003

i dont think you can use double quotes in mysql, i think its single quotes only. try

Query = "DELETE * FROM settings WHERE user = '" & Me.loginuser.Text & "'"

Upvotes: 0

Related Questions