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

VB 前端之家
前端之家收集整理的这篇文章主要介绍了使用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相关文章

VB Format函数
Format[$] ( expr [ , fmt ] ) format 返回变体型 format$ 强制返回为文本 --------------...
vb6/ASP FORMAT MM/DD/YYYY
VB6或者ASP 格式化时间为 MM/dd/yyyy 格式，竟然没有好的办法， Format 或者FormatDateTi...
VB.net 捕获项目全局异常
在项目中添加如下代码：新建窗口来显示异常信息。 Namespace My ‘全局错误处理，新的...
实现用VB.Net/(C#)开发K/3 BOS 插件的真正可行方法
转了这一篇文章，原来一直想用C#做k3的插件开发，vb没有C#用的爽呀，这篇文章写与2011年，...
vb，wps，excel 分裂
Sub 分列() ‘以空格为分隔符，连续空格只算1个。对所选中的单元格进行处理 Dim m...
VB.NET MYSQL DataGridView 增删改查（INSERT,SELECT,UPDATE,DELETE）
  Imports MySql.Data.MySqlClient Public Class Form1 ‘ GLOBAL DECLARATIONS ...
VB.NET 使用ADODB連接資料庫滙出到EXCEL
‘導入命名空間 Imports ADODB Imports Microsoft.Office.Interop   Private Sub A1() Di...
vb.net 多线程運用 ping
Imports System.IO Imports System.Threading Imports System.Diagnostics Public Class F...
VB等待进程结束
VB运行EXE程序，并等待其运行结束 参考：https://blog.csdn.net/useway/article/details/5...
vb中去掉string数组的一部分
今天碰到一个问题，登陆的时候，如果不需要验证手机号为空，则不去验证手机号 因为登陆的时...
PHPJavaJava SEPythonC#C&C++RubyVBasp.NetGoPerlnettyDjangoDelphiJsp.NET CoreSpringFlaskSpringbootSpringMVCLuaLaravelMybatisAspGroovyThinkPHPYiiswoole
文件时间pythonm相等性PHP Warning时间问题问题解决pcntl_signal采样点wav模块动态文本调用频率限制对外暴露多个访问请求更新数据表模型结构type()方法比较速度手写体sobel算子保存模型Image类nn.Conv2dpytorch1.0kaggleDCGAN交并比range()用法打印模型反卷积卷积