CODEWITHSUNDEEP
mysqlsqlvb.netwinformsexception
Mohamed Amine
Mohamed Amine

Reputation: 582

vb.net execute sql command with variables

I want to insert into the table from a windows form when I run and I try to use this functionality an exception occurs which is

field 'the string i wrote ' unknown in field list" "pointing towards "resultat = cmd.ExecuteNonQuery"

 Sub maj()
        Dim cnx As New MySqlConnection("datasource=localhost;database=bdgeststock;username=root;password=")
        Dim cmd As MySqlCommand = cnx.CreateCommand
        Dim resultat As Integer
        If ConnectionState.Open Then
            cnx.Close()
        End If
        cnx.Open()
        cmd.CommandText = "insert into utilisateur(login,password,type)value(" & TextBox3.Text & "," & TextBox4.Text & "," & ListBox1.SelectedItem() & ")"
        resultat = cmd.ExecuteNonQuery
        If (resultat = 0) Then
            MessageBox.Show("error")
        Else
            MessageBox.Show("success")

        End If
        cnx.Close()
        cmd.Dispose()
    End Sub

Upvotes: 1

Views: 1812

Answers (2)

Mary
Mary

Reputation: 15091

The Using...End Using blocks will close and dispose your database objects even if there is an error. In this code both the connection and the command are included. Note the comma at the end of the Using line.

Always use parameters to avoid Sql injection. The .Add method is preferable to the .AddWithValue. I had to guess at the datatype and size of the fields. Check your database for the actual values.

Sub maj()
    Dim resultat As Integer
    Using cnx As New MySqlConnection("datasource=localhost;database=bdgeststock;username=root;password="),
            cmd As New MySqlCommand("insert into utilisateur(login,`password`,type) values(@Login, @Password, @Type);", cnx)
        cmd.Parameters.Add("@Login", MySqlDbType.VarChar).Value = TextBox3.Text
        cmd.Parameters.Add("@Password", MySqlDbType.VarChar).Value = TextBox4.Text
        cmd.Parameters.Add("@Type", MySqlDbType.VarChar).Value = ListBox1.SelectedItem
        cnx.Open()
        resultat = cmd.ExecuteNonQuery
    End Using
    If resultat = 0 Then
        MessageBox.Show("error")
    Else
        MessageBox.Show("success")
    End If
End Sub

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

The string values you're concatenating aren't surrounded by quotes, so the database identifies them as column names - and then fails since there are no such columns. Moreover, this leaves your program vulnerable to SQL Injection attacks.

A better approach would be to use bind variables:

cmd.CommandText = "insert into utilisateur (login, password, type) value (@P1, @P2, @P3)"
cmd.Parameters.AddWithValue("@P1", TextBox3.Text)
cmd.Parameters.AddWithValue("@P2", TextBox4.Text)
cmd.Parameters.AddWithValue("@P3", ListBox1.SelectedItem())
cmd.ExecuteNonQuery()

Upvotes: 1

Related Questions