使用VB.NET将数据插入MySQL表

发布时间：2020-12-17 00:02:12 所属栏目：大数据来源：网络整理

导读：我在使用VB.NET应用程序将数据插入MySQL数据库的表时遇到问题. 我有一个简单的表单,当我将一些数据设置到文本框并按下GO按钮时,代码应该执行一个名为InsertCar()的函数,它接受所有这些值并将它们插入到数据库中,然后如果事务是成功完成或否则.我的问题是没有

我在使用VB.NET应用程序将数据插入MySQL数据库的表时遇到问题.
我有一个简单的表单,当我将一些数据设置到文本框并按下GO按钮时,代码应该执行一个名为InsertCar()的函数,它接受所有这些值并将它们插入到数据库中,然后如果事务是成功完成或否则.我的问题是没有任何东西被插入表中.

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

我正在使用此MsgBox(str_carSql)来测试SQL语句是否正确且是否正确.

任何帮助将不胜感激.

UPDATE

我做了以下,但仍然无法正常工作

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,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

插入也不工作,我将发布整个代码可能在其他地方找到问题

导入MySql.Data.MySqlClient
Imports System.Data.Sql
进口系统
导入System.Data

公共类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,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

结束类

谢谢大家的帮助.这是对我有用的解决方案

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,@imodel,@icolor,@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

实例化连接后,打开它.

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

另外,避免仅通过附加字符串来构建SQL语句.如果你使用参数更好,这样你就可以获得性能,你的程序不容易受到SQL注入攻击,你的程序也更稳定.例如：

str_carSql = "insert into members_car 
               (car_id,code) 
               values 
               (@id,@code)"

然后你这样做：

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

然后你打电话：

sqlCommand.ExecuteNonQuery()

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!