无法使用VB.NET在MySQL中插入数据 [英] Cant insert data in MySQL using VB.NET

问题描述

我一直在互联网上搜索，以了解我的代码出了什么问题.我无法将数据插入MySQL的表中.我在其中有一组文本框和一个按钮，因此当我单击该按钮时，文本框中的所有数据都将添加到MySQL表中.在按钮的代码中，我具有执行插入任务的功能.

Hi,

I''ve being searching all over the internet to know what is wrong with my code. I am unable to insert data into a table in MySQL. I have a set of text boxes where and a button so when I click the button, all the data in the textboxes are added to a MySQL Table. Within the code of the button I have a function that perform the insert task.

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

每当我按下按钮时，什么都不会发生，并且表格仍然为空.

Whenever I press the button, nothing happen and the table remains empty.

推荐答案

好吧，那不会

您加载了所有参数，然后执行非查询-但您尚未打开连接！
完成后(它将抛出异常)，然后进入try块，打开连接并进行下一步操作.

在WITH块中执行ExecuteNonQuery调用.

Well no, it won''t

You load up all the parameters, and then execute the non-query - but you haven''t opened the connection yet!
When that is done (and it will throw and exception) you then enter the try block, and open the connection and have another go...

Take out the ExecuteNonQuery call in the WITH block.

下面的代码将起作用

变更
1)打开连接
2)使用字符串生成器构建插入查询

Below code will work

Changes
1) Open connection
2) Use of string builder to build insert query

Public Function InsertCar() As Boolean

      Dim iReturn As Boolean
      Using SQLConnection As New MySqlConnection(connectionString)
          SQLConnection.Open()
          Dim MSqlQuery As New StringBuilder
          MSqlQuery.AppendFormat("INSERT INTO members_car (`car_id`, `member_id`, `model`, `color`, `chassis_id`, `plate_number`, `code`) values ({0},{1},""{2}"",""{3}"",{4},{5},""{6}"")", TextBox20.Text, TextBox20.Text, TextBox23.Text, TextBox24.Text, TextBox22.Text, TextBox21.Text, ComboBox1.SelectedItem)

          Using sqlCommand As New MySqlCommand()
              With sqlCommand
                  .CommandText = SqlQuery.ToString()
                  .Connection = SQLConnection
                  .CommandType = CommandType.Text
                  .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)
                  .ExecuteNonQuery()
              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

希望这对您有所帮助，然后接受并投票回答我的答案，否则请回复您的查询
--Rahul D.

Hope this helps if yes then accept and vote my answer otherwise revert back with your queries
--Rahul D.

这篇关于无法使用VB.NET在MySQL中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！