vb.net mysql 插入数据,无法使用VB.NET在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.

解决方案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.

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

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

--Rahul D.