我在使用VB.NET应用程序向MySQL数据库表格插入数据时遇到问题。
我有一个简单的表格,在这个表格中,当我对文本框设置一些数据并按下“GO”按钮时,代码应该执行一个名为InsertCar()
的函数,该函数将所有这些值插入到数据库中,如果事务成功完成则返回true,否则返回false。我的问题是,没有任何东西被插入到表格中。
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 语句是否正确,结果是正确的。
任何帮助将不胜感激。
更新
我已经尝试了以下方法,但仍然无法正常工作。
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, member_id, model, color, chassis_id, plate_number, 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
更新
插入操作也无法正常工作,我将发布整个代码,可能问题出在其他地方。
导入 MySql.Data.MySqlClient 和 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, member_id, model, color, chassis_id, plate_number, 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,@m_id,@imodel,@icolor,@ch_id,@pt_num,@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
sqlCommand.CommandType = CommandType.Text
。 - John Woo