vb.net execute sql command with variables

Question

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

Mureinik · Accepted Answer · 2020-03-22 19:45:33Z

1

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

answered Mar 22, 2020 at 19:45

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mohamed Amine Over a year ago

well thanks that worked tha way but actually i am using in as a sub and then call it when needed and i change the sql command according to the need so i dont think it will work

maj("insert into utilisateur(login,password,type) values (" & TextBox3.Text & "," & TextBox4.Text & ","  & ListBox1.SelectedItem() & ")")

Chris Dunaway Over a year ago

@MohamedAmine - You can pass the value for CommandText into your method also, along with a list of the parameter values.

Mary · Accepted Answer · 2020-03-22 20:38:15Z

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

Collectives™ on Stack Overflow

vb.net execute sql command with variables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related