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

我在使用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()

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

猜你在找的VB相关文章