Inserting data into a MySQL table using VB.NET

Question

I have a problem inserting data into a table on a MySQL database using a VB.NET application. I have a simple form where when I set some data to the textboxes and I press a GO button, the code should execute a function called InsertCar() that takes all these values and insert them into the database and then return true if the transaction is done successfully or false otherwise. My problem is that nothing is being inserted into the table.

Imports MySql.Data.MySqlClient
Imports System.Data.Sql
Imports System
Imports System.Data

Public Class Form1

Dim connectionString As String = "Server=localhost; User Id=root; Password=123456; Database=uni_park_db"
Dim SQLConnection As MySqlConnection = New MySqlConnection
Dim oDt_sched As New DataTable()

//SOME CODE For other buttons//


//Code for a button where the InsertCar() function is called at the beginning//


Public Function InsertCar() As Boolean

    SQLConnection = New MySqlConnection()
    SQLConnection.ConnectionString = connectionString
    Dim sqlCommand As New MySqlCommand
    Dim str_carSql As String

    Try
        str_carSql = "insert into members_car (car_id, member_id, model, color, chassis_id, plate_number, code) values ('" + TextBox20.Text + "','" + TextBox20.Text + "','" + TextBox23.Text + "','" + TextBox24.Text + "','" + TextBox22.Text + "','" + TextBox21.Text + "','" + ComboBox1.SelectedItem + "')"
        MsgBox(str_carSql)
        sqlCommand.Connection = SQLConnection
        sqlCommand.CommandText = str_carSql
        sqlCommand.ExecuteNonQuery()

        Return True

    Catch ex As Exception
        Return False
        MsgBox("Error occured: Could not insert record")
    End Try

End Function


End Class

I am using this MsgBox(str_carSql) to test if the SQL statement is correct and it is correct.

Any help will be appreciated.

UPDATE

I did the following and it stills not working

Public Function InsertCar() As Boolean

    SQLConnection = New MySqlConnection()
    SQLConnection.ConnectionString = connectionString
    SQLConnection.Open()
    Dim sqlCommand As New MySqlCommand
    Dim str_carSql As String

    Try

        str_carSql = "insert into members_car (car_id, member_id, model, color, chassis_id, plate_number, code) values (@id,@m_id,@model,@color,@ch_id,@pt_num,@code)"
        sqlCommand.Connection = SQLConnection
        sqlCommand.CommandText = str_carSql
        sqlCommand.Parameters.AddWithValue("@id", TextBox20.Text)
        sqlCommand.Parameters.AddWithValue("@m_id", TextBox20.Text)
        sqlCommand.Parameters.AddWithValue("@model", TextBox23.Text)
        sqlCommand.Parameters.AddWithValue("@color", TextBox24.Text)
        sqlCommand.Parameters.AddWithValue("@ch_id", TextBox22.Text)
        sqlCommand.Parameters.AddWithValue("@pt_num", TextBox21.Text)
        sqlCommand.Parameters.AddWithValue("@code", ComboBox1.SelectedItem)
        sqlCommand.ExecuteNonQuery()

        Return True

    Catch ex As Exception
        Return False
        MsgBox("Error occured: Could not insert record")
    End Try

End Function

UPDATE

The Insert is not working either, I will post the entire code maybe a problem is found elsewhere

Imports MySql.Data.MySqlClient Imports System.Data.Sql Imports System Imports System.Data

Public Class Form1

Dim connectionString As String = "Server=localhost; User Id=root; Password=123456; Database=uni_park_db"
Dim SQLConnection As MySqlConnection = New MySqlConnection
Dim oDt_sched As New DataTable()

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try


        If SQLConnection.State = ConnectionState.Open Then
            If TextBox1.Text = "" Then
                MsgBox("Please Input a Valid ID")
            Else
                Dim myAdapter1 As New MySqlDataAdapter("select m.work_date as Work, m.time_in as Start, m.time_out as End from university_members as u inner join members_schedule as m on u.members_schedule_id=m.members_schedule_id where member_id = " & TextBox1.Text, SQLConnection)
                Dim myAdapter As New MySqlDataAdapter("select member_id, first_name, last_name, type from university_members, members_schedule where(university_members.members_schedule_id = members_schedule.members_schedule_id) AND member_id = " & TextBox1.Text, SQLConnection)
                Dim mydatatable As New DataTable()
                Dim dataset As New DataSet()
                myAdapter.Fill(mydatatable)

                If (mydatatable.Rows.Count > 0 And myAdapter1.Fill(dataset)) Then
                    TextBox2.Text = mydatatable.Rows(0).Item("first_name")
                    TextBox3.Text = mydatatable.Rows(0).Item("last_name")
                    TextBox4.Text = mydatatable.Rows(0).Item("type")
                    TextBox20.Text = mydatatable.Rows(0).Item("member_id")
                    DataGridView1.DataSource = dataset.Tables(0)
                    oDt_sched = dataset.Tables(0)

                Else
                    MsgBox("Check Error: ID Not Found! Enter a Valid ID")
                    TextBox1.Text = "Example 123456 "
                    TextBox2.Text = " "
                    TextBox3.Text = " "
                    TextBox4.Text = " "
                End If
            End If
        Else
            MsgBox("Database Connection Error: Database Connection Not Established. Please Connect First.")
        End If



    Catch ex As Exception
        MsgBox(ex.ToString)

    End Try


End Sub



Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
    Application.Exit()
End Sub


Private Sub DatabaseConnectToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatabaseConnectToolStripMenuItem.Click

    SQLConnection = New MySqlConnection()
    SQLConnection.ConnectionString = connectionString
    Try

        If SQLConnection.State = ConnectionState.Closed Then
            SQLConnection.Open()
            MsgBox("Database Connection Sccessfully Established")
        Else
            SQLConnection.Close()
            MsgBox("Database Connection Terminated")

        End If
    Catch ex As Exception
        MsgBox(ex.ToString)

    End Try

End Sub



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

    TextBox1.Text = "Example 123456 "
    TextBox2.Text = " "
    TextBox3.Text = " "
    TextBox4.Text = " "
    DataGridView1.Columns.Clear()
    DataGridView1.DataSource = Nothing


End Sub


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


    Dim str_sql As String = ""
    Dim obj_msadapter As MySqlDataAdapter
    Dim i_maxh As Integer
    Dim i_beginh As Integer
    Dim ods_avail As DataSet = New DataSet()
    Dim str_err As String = ""
    Dim i_strth As Integer
    Dim odt_avail As New DataTable()
    Dim odrcol_avail() As DataRow
    Dim str_range As String = ""

    Try

        'perform insert car here (boolean to see if the code continues running)

        ''''''''''''''''''''''''''

        If InsertCar() Then
            For Each odr As DataRow In oDt_sched.Rows
                i_maxh = odr(2)
                i_beginh = odr(1)
                i_strth = odr(1) + 2
                str_range = ""

                str_sql = "select * from parked_cars where pwork_date='" & odr(0).ToString() & "'"

                ods_avail = New DataSet()
                obj_msadapter = New MySqlDataAdapter(str_sql, SQLConnection)
                obj_msadapter.Fill(ods_avail)

                odt_avail = ods_avail.Tables(0)

                If odt_avail.Rows.Count < 210 Then
                    While (i_strth <= i_maxh)
                        odrcol_avail = odt_avail.Select("ptime_in='" + i_beginh.ToString() + "' and ptime_out='" + i_strth.ToString() + "'")

                        If odrcol_avail.Count < 30 Then
                            str_range += i_beginh.ToString() + ";" + i_strth.ToString()
                        Else
                            str_range += "0"
                        End If

                        i_strth += 2
                        i_beginh += 2
                    End While

                    FillSpots(str_range, odr(0).ToString())
                Else
                    str_err += "no place on day: " + odr(0).ToString() + ";"
                    MsgBox("No place is found on this day")
                End If

            Next
        End If
    Catch ex As Exception
        MsgBox("")

    End Try
End Sub

Public Function FillSpots(ByVal blowf As String, ByVal _day As String) As Boolean

    Dim str_unit As String
    Dim i_count As Integer = 0
    Dim str_i_strt As String
    Dim str_i_end As String
    Dim str_sql As String

    Try
        For Each str_unit In blowf.Split("0")
            If str_unit <> "" Then
                str_i_strt = str_unit.Split(";")(0)
                str_i_end = str_unit.Split(";")(str_unit.Split(";").Length - 1)
                str_sql = "insert into parked_cars values ('" + TextBox20.Text + "','" + _day + "','" + str_i_strt + "','" + str_i_end + "')"
            End If
        Next

        Return True
    Catch ex As Exception
        Throw ex
    End Try
End Function


Public Function InsertCar() As Boolean

    SQLConnection = New MySqlConnection()
    SQLConnection.ConnectionString = connectionString
    SQLConnection.Open()
    Dim sqlCommand As New MySqlCommand
    Dim str_carSql As String

    Try

        str_carSql = "insert into members_car (car_id, member_id, model, color, chassis_id, plate_number, code) values (?id,?m_id,?model,?color,?ch_id,?pt_num,?code)"
        sqlCommand.Connection = SQLConnection
        sqlCommand.CommandText = str_carSql
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.Parameters.AddWithValue("?id", TextBox20.Text)
        sqlCommand.Parameters.AddWithValue("?m_id", TextBox20.Text)
        sqlCommand.Parameters.AddWithValue("?model", TextBox23.Text)
        sqlCommand.Parameters.AddWithValue("?color", TextBox24.Text)
        sqlCommand.Parameters.AddWithValue("?ch_id", TextBox22.Text)
        sqlCommand.Parameters.AddWithValue("?pt_num", TextBox21.Text)
        sqlCommand.Parameters.AddWithValue("?code", ComboBox1.SelectedItem)
        sqlCommand.ExecuteNonQuery()

        Return True

    Catch ex As Exception
        Return False
        MsgBox("Error occured: Could not insert record")
    End Try

End Function



Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    ComboBox1.ResetText()
    TextBox21.Text = " "
    TextBox22.Text = " "
    TextBox23.Text = " "
    TextBox24.Text = " "
    DataGridView2.Columns.Clear()
    DataGridView2.DataSource = Nothing

End Sub

End Class

Thanks everyone for the help. This is the solution that worked for me

    Dim iReturn as boolean
    Using SQLConnection As New MySqlConnection(connectionString)
        Using sqlCommand As New MySqlCommand()
            With sqlCommand
                .CommandText = "INSERT INTO members_car (`car_id`, `member_id`, `model`, `color`, `chassis_id`, `plate_number`, `code`) values (@xid,@m_id,@imodel,@icolor,@ch_id,@pt_num,@icode)"
                .Connection = SQLConnection
                .CommandType = CommandType.Text // You missed this line
                .Parameters.AddWithValue("@xid", TextBox20.Text)
                .Parameters.AddWithValue("@m_id", TextBox20.Text)
                .Parameters.AddWithValue("@imodel", TextBox23.Text)
                .Parameters.AddWithValue("@icolor", TextBox24.Text)
                .Parameters.AddWithValue("@ch_id", TextBox22.Text)
                .Parameters.AddWithValue("@pt_num", TextBox21.Text)
                .Parameters.AddWithValue("@icode", ComboBox1.SelectedItem)

            End With
            Try
                SQLConnection.Open()
                sqlCommand.ExecuteNonQuery()
                iReturn = TRUE
            Catch ex As MySqlException
                MsgBox ex.Message.ToString
                iReturn = False
            Finally
                SQLConnection.Close()
            End Try
        End Using
    End Using

   Return iReturn

I don't see the SQLConnection.Open() happening, try adding that — Dan, Feb 10 '12 at 21:04
First, switch the order of lines in exception handler. Return False goes after MsgBox("Error occured: Could not insert record"). Then you'll see that you have an error. Use MsgBox(ex.Message) to see what's the error. Then we can help you further. Probably only connection. — Miroslav Zadravec, Feb 10 '12 at 21:08
try my answer below. Probably you missed `sqlCommand.CommandType = CommandType.Text` — John Woo, Feb 11 '12 at 11:19

score 5 · Answer 1 · answered Feb 10 '12 at 21:10

5

After instantiating the connection, open it.

  SQLConnection = New MySqlConnection()
  SQLConnection.ConnectionString = connectionString
  SQLConnection.Open()

Also, avoid building SQL statements by just appending strings. It's better if you use parameters, that way you win on performance, your program is not prone to SQL injection attacks and your program is more stable. For example:

 str_carSql = "insert into members_car 
               (car_id, member_id, model, color, chassis_id, plate_number, code) 
               values 
               (@id,@m_id,@model,@color,@ch_id,@pt_num,@code)"

And then you do this:

sqlCommand.Parameters.AddWithValue("@id",TextBox20.Text)
sqlCommand.Parameters.AddWithValue("@m_id",TextBox23.Text)
' And so on...

Then you call:

sqlCommand.ExecuteNonQuery()

answered Feb 10 '12 at 21:10

Icarus

63,293
14
100
115

I updated the function as you suggested, I am not sure if something is wrong somewhere, it is still not working. – user1176607 Feb 11 '12 at 10:53
How is "not working"? Do you get an exception? If so, what does it say? Be more descriptive, please. – Icarus Feb 11 '12 at 12:33
When I press the Button2_Click, I got a messagebox which is tracked to this code in the Button1_Click Catch ex As Exception MsgBox("") End Try – user1176607 Feb 11 '12 at 13:14

score 2 · Answer 2 · edited Feb 14 '17 at 01:58

Dim connString as String ="server=localhost;userid=root;password=123456;database=uni_park_db"
Dim conn as MySqlConnection(connString)
Dim cmd as MysqlCommand
Dim dt as New DataTable
Dim ireturn as Boolean

Private Sub Insert_Car()

Dim sql as String = "insert into members_car (car_id, member_id, model, color, chassis_id, plate_number, code) values (@car_id,@member_id,@model,@color,@chassis_id,@plate_number,@code)"

Dim cmd = new MySqlCommand(sql, conn)

    cmd.Paramaters.AddwithValue("@car_id", txtCar.Text)
    cmd.Paramaters.AddwithValue("@member_id", txtMember.Text)
    cmd.Paramaters.AddwithValue("@model", txtModel.Text)
    cmd.Paramaters.AddwithValue("@color", txtColor.Text)
    cmd.Paramaters.AddwithValue("@chassis_id", txtChassis.Text)
    cmd.Paramaters.AddwithValue("@plate_number", txtPlateNo.Text)
    cmd.Paramaters.AddwithValue("@code", txtCode.Text)

    Try
        conn.Open()
        If cmd.ExecuteNonQuery() > 0 Then
            ireturn = True
        End If  
        conn.Close()


    Catch ex as Exception
        ireturn = False
        conn.Close()
    End Try

Return ireturn

End Sub

When giving an answer it is preferable to give [some explanation as to WHY your answer](http://stackoverflow.com/help/how-to-answer) is the one. — Stephen Rauch, Feb 14 '17 at 01:59

score 2 · Answer 3 · answered Feb 10 '12 at 21:06

2

You need to open the connection first:

 SQLConnection.Open();

answered Feb 10 '12 at 21:06

Stelian Matei

11,553
2
25
29

(minor detail) I don't think he needs the `;` since it's VB.NET – Dan Feb 10 '12 at 21:07
I have a button that open connection for the application, that's why I am not using it as the connection is opened. – user1176607 Feb 11 '12 at 10:37
Yes, but in this method you create a new connection `SQLConnection = New MySqlConnection()`. If you have a connection already opened, you should use that instead, right? – Stelian Matei Feb 11 '12 at 10:41
I updated the problem after I re-wrote the function, still not working. – user1176607 Feb 11 '12 at 10:59
I saw the update, but you need to put ?param instead of @param when using named parameters with MySQL. – Stelian Matei Feb 11 '12 at 11:03
Do you mean like the following sqlCommand.Parameters.AddWithValue("?m_id",TextBox23.Text) – user1176607 Feb 11 '12 at 11:20
Yes, but you also need to update the query and use ?m_id. See the code in the answer below – Stelian Matei Feb 11 '12 at 11:22
I have posted the entire code within the class, Update is still not working at all. I am able to retrieve data from the MySQL but I am not able to insert. – user1176607 Feb 11 '12 at 11:29

score 1 · Answer 4 · edited Mar 10 '13 at 17:56

1

your str_carSql should be exactly like this:

str_carSql = "insert into members_car (car_id, member_id, model, color, chassis_id, plate_number, code) values (@id,@m_id,@model,@color,@ch_id,@pt_num,@code)"

Good Luck

edited Mar 10 '13 at 17:56

Eric J.

147,927
63
340
553

answered Mar 10 '13 at 17:34

nancy alajarmeh

121
1
2

Stelian Matei · Answer 5 · 2012-02-11T11:42:40.553

0

You need to use ?param instead of @param when performing queries to MySQL

 str_carSql = "insert into members_car (car_id, member_id, model, color, chassis_id, plate_number, code) values (?id,?m_id,?model,?color,?ch_id,?pt_num,?code)"
        sqlCommand.Connection = SQLConnection
        sqlCommand.CommandText = str_carSql
        sqlCommand.Parameters.AddWithValue("?id", TextBox20.Text)
        sqlCommand.Parameters.AddWithValue("?m_id", TextBox20.Text)
        sqlCommand.Parameters.AddWithValue("?model", TextBox23.Text)
        sqlCommand.Parameters.AddWithValue("?color", TextBox24.Text)
        sqlCommand.Parameters.AddWithValue("?ch_id", TextBox22.Text)
        sqlCommand.Parameters.AddWithValue("?pt_num", TextBox21.Text)
        sqlCommand.Parameters.AddWithValue("?code", ComboBox1.SelectedItem)
        sqlCommand.ExecuteNonQuery()

Change the catch block to see the actual exception:

  Catch ex As Exception
         MsgBox(ex.Message)   
          Return False

    End Try

edited Feb 11 '12 at 11:42

answered Feb 11 '12 at 11:03

Stelian Matei

11,553
2
25
29

If this doesn't work, please check the exception thrown and give us some more details. – Stelian Matei Feb 11 '12 at 11:10
I have posted the entire code within the class, Update is still not working at all. I am able to retrieve data from the MySQL but I am not able to insert. – user1176607 Feb 11 '12 at 11:40
@user1176607 What exactly do you want to execute? `INSERT` or `UPDATE` because those two are different things? – John Woo Feb 11 '12 at 11:42
I want to execute Insert, if you look at the code I am facing the problem in the function called InsertCar(). Whenever I press Button2_Click after filling the textboxes, the InsertCar() should insert data into a MySQL table. I tried everything I know so far, and nothing is being added to the table. – user1176607 Feb 11 '12 at 11:47

John Woo · Answer 6 · 2012-02-11T14:55:19.553

0

First, You missed this one: sqlCommand.CommandType = CommandType.Text
Second, Your MySQL Parameter Declaration is wrong. It should be @ and not ?

try this:

Public Function InsertCar() As Boolean

    Dim iReturn as boolean
    Using SQLConnection As New MySqlConnection(connectionString)
        Using sqlCommand As New MySqlCommand()
            With sqlCommand
                .CommandText = "INSERT INTO members_car (`car_id`, `member_id`, `model`, `color`, `chassis_id`, `plate_number`, `code`) values (@xid,@m_id,@imodel,@icolor,@ch_id,@pt_num,@icode)"
                .Connection = SQLConnection
                .CommandType = CommandType.Text // You missed this line
                .Parameters.AddWithValue("@xid", TextBox20.Text)
                .Parameters.AddWithValue("@m_id", TextBox20.Text)
                .Parameters.AddWithValue("@imodel", TextBox23.Text)
                .Parameters.AddWithValue("@icolor", TextBox24.Text)
                .Parameters.AddWithValue("@ch_id", TextBox22.Text)
                .Parameters.AddWithValue("@pt_num", TextBox21.Text)
                .Parameters.AddWithValue("@icode", ComboBox1.SelectedItem)
            End With
            Try
                SQLConnection.Open()
                sqlCommand.ExecuteNonQuery()
                iReturn = TRUE
            Catch ex As MySqlException
                MsgBox ex.Message.ToString
                iReturn = False
            Finally
                SQLConnection.Close()
            End Try
        End Using
    End Using

   Return iReturn

End Function

edited Feb 11 '12 at 14:55

answered Feb 11 '12 at 11:16

John Woo

258,903
69
498
492

The default is CommandType is text. I don't think it makes any difference. Please put a breakpoint on the catch block and give details about the exception – Stelian Matei Feb 11 '12 at 11:32
I have posted the entire code within the class, Update is still not working at all. I am able to retrieve data from the MySQL but I am not able to insert. – user1176607 Feb 11 '12 at 11:39
Please change the catch block to see the actual exception. Put MsgBox(ex.Message) before return false – Stelian Matei Feb 11 '12 at 11:43
@user1176607 Please try my updated solution. It's the same syntax i'm using in my system. – John Woo Feb 11 '12 at 11:55
@user1176607 don't use `?` but instead `@` – John Woo Feb 11 '12 at 11:57
Still nothing. When I try press the Button2_Click, I get an empty messagebox which is listed under the catch exception in the Button2_Click – user1176607 Feb 11 '12 at 13:12
@user1176607 this sounds VERY strange. Did you try my solution? that solution is the same format as the one in my Functions. i have edited the variables and parameters that suit in your function. Are you sure you get an empty message box when you execute the Function? – John Woo Feb 11 '12 at 13:17
Yes I replaced the entire function with yours, and I am getting that empty message box. I removed all the code from the Button2_Click and kept the InsertCar(), same thing. I dont think the problem is with the database connection as I am able to retrieve data. – user1176607 Feb 11 '12 at 13:19
Thanks for the help, it works now, I had to remove .ExecuteNonQuery() before the End With. thanks again – user1176607 Feb 11 '12 at 14:45
@user1176607 my bad, i forgot to remove it also. I hope that i helped you even a little. :) – John Woo Feb 11 '12 at 14:56

Inserting data into a MySQL table using VB.NET

6 Answers6

Linked