如何将相同的数据插入 vb.net 中的 2 个不同表中

Question

当我输入第一个查询时，它已成功保存到第一个表中。当我包含第二个查询时，问题就开始了。问题是： 数据未保存到两个表中。 错误行是cmd.executereader()。

Private Sub btnfrmRcptQuerySave_Click(sender As Object, e As EventArgs) Handles btnfrmRcptQuerySave.Click
Try
con = New SqlConnection(cs)
con.Open()
Dim ctn1 As String = "select * from TicketInfo"
cmd = New SqlCommand(ctn1)
cmd.Connection = con
Dim sql1 As String = "insert into TicketInfo(TicketNo, Date, CustNum, CustName, Address, ContNum, Email, AptDate) Values (@0,@1,@2,@3,@4,@5,@6,@7)"
cmd = New SqlCommand(sql1)
cmd.Parameters.AddWithValue("@0", Val(lblRcptQueryNo.Text))
cmd.Parameters.AddWithValue("@1", lblRcptQueryDate.Text)
cmd.Parameters.AddWithValue("@2", Val(txtRcptQueryCustNo.Text))
cmd.Parameters.AddWithValue("@3", txtRcptQueryName.Text)
cmd.Parameters.AddWithValue("@4", txtRcptQueryAddress.Text)
cmd.Parameters.AddWithValue("@5", txtRcptQueryContact.Text)
cmd.Parameters.AddWithValue("@6", txtRcptQueryEmail.Text)
cmd.Parameters.AddWithValue("@7", DateTimeRcptQuery.Value.Date)
cmd.Connection = con
cmd.ExecuteReader()
con.Close()
MessageBox.Show("Table 1 saved")
'Save Data to CleintDetails Table.
con = New SqlConnection(cs)
con.Open()
Dim ctn2 As String = "select * from CleintDetails"
cmd = New SqlCommand(ctn2)
cmd.Connection = con
Dim sql2 As String = "insert into CleintDetails(CustNum, CustName, Address, ContNum, Email) Values (@0,@1,@2,@3,@4)"
cmd = New SqlCommand(sql2)
cmd.Parameters.AddWithValue("@0", Val(txtRcptQueryCustNo.Text))
cmd.Parameters.AddWithValue("@1", txtRcptQueryName.Text)
cmd.Parameters.AddWithValue("@2", txtRcptQueryAddress.Text)
cmd.Parameters.AddWithValue("@3", txtRcptQueryContact.Text)
cmd.Parameters.AddWithValue("@4", txtRcptQueryEmail.Text)
cmd.Connection = con
cmd.ExecuteReader()
con.Close()
MessageBox.Show("Table 2 saved")

btnfrmRcptQuerySave.Enabled = False
MessageBox.Show("Successfully Saved", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Answer 1

我刚开始使用 SQL，所以我可能是错的，但我会这样做。

Dim SQL_Command As SQLCommand = SQL_Connection.CreateCommand()
SQL_Command.CommandText = "INSERT INTO Table1 ..."
'Add parameters
SQL_Command.ExecuteNonQuery()
SQL_Command.CommandText = "INSERT INTO Table2 ..."
'no need for adding parameters, we still have them from before as we didn't call SQL_Command.Parameters.Clear() 
SQL_Command.ExecuteNonQuery()

Answer 2

您创建了一个 Select 命令，但从不对其进行任何操作。然后覆盖.CommandText属性。

将数据库对象保留在本地，以便确保它们已关闭并释放。 即使存在错误，Using...End Using块也会处理此问题。

可以将命令文本和连接直接传递给命令的构造函数。

不要使用旧的 VB6Val方法。使用 .net 函数(CInt、CDec等)。

不要在最后一刻之前打开连接，并尽快关闭。

不要使用.AddWithValue.见 http://www.dbdelta.com/addwithvalue-is-evil/和 https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/另一个： https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications 我必须猜测要与.Add方法一起使用的数据类型和大小。检查数据库中的实际值。

.ExecuteReader与"选择"命令一起使用。.ExecuteNonQuery与插入、更新和删除命令一起使用。

在连接关闭之前，不要显示消息框或以其他方式与用户界面交互。

Private Sub btnfrmRcptQuerySave_Click(sender As Object, e As EventArgs) Handles btnfrmRcptQuerySave.Click
Dim retVal As Integer
Try
Using con As New SqlConnection(cs)
Using cmd As New SqlCommand("insert into TicketInfo(TicketNo, Date, CustNum, CustName, Address, ContNum, Email, AptDate) Values (@0,@1,@2,@3,@4,@5,@6,@7)", con)
cmd.Parameters.Add("@0", SqlDbType.Int).Value = CInt(lblRcptQueryNo.Text)
cmd.Parameters.Add("@1", SqlDbType.NVarChar, 100).Value = lblRcptQueryDate.Text
cmd.Parameters.Add("@2", SqlDbType.Int).Value = CInt(txtRcptQueryCustNo.Text)
cmd.Parameters.Add("@3", SqlDbType.NVarChar, 100).Value = txtRcptQueryName.Text
cmd.Parameters.Add("@4", SqlDbType.NVarChar, 100).Value = txtRcptQueryAddress.Text
cmd.Parameters.Add("@5", SqlDbType.NVarChar, 100).Value = txtRcptQueryContact.Text
cmd.Parameters.Add("@6", SqlDbType.NVarChar, 100).Value = txtRcptQueryEmail.Text)
cmd.Parameters.Add("@7", SqlDbType.DateTime).Value = DateTimeRcptQuery.Value
con.Open()
retVal = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Saving Table 1", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
MessageBox.Show($"{retVal} record saved to Table 1.")
'Save Data to CleintDetails Table.
Try
Using con As New SqlConnection(cs)
Using cmd As New SqlCommand("insert into CleintDetails(CustNum, CustName, Address, ContNum, Email) Values (@0,@1,@2,@3,@4)", con)
cmd.Parameters.Add("@0", SqlDbType.Int).Value = CInt(txtRcptQueryCustNo.Text)
cmd.Parameters.Add("@1", SqlDbType.NVarChar, 100).Value = txtRcptQueryName.Text
cmd.Parameters.Add("@2", SqlDbType.NVarChar, 100).Value = txtRcptQueryAddress.Text
cmd.Parameters.Add("@3", SqlDbType.NVarChar, 100).Value = txtRcptQueryContact.Text
cmd.Parameters.Add("@4", SqlDbType.NVarChar, 100).Value = txtRcptQueryEmail.Text
con.Open()
retVal = cmd.ExecuteNonQuery()
End Using
End Using
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Saving Table 2", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
MessageBox.Show($"{retVal} record saved to Table 2")
btnfrmRcptQuerySave.Enabled = False
End Sub

这是一个设计不佳的数据库，需要在 2 个不同的表中输入重复数据。熟悉外键的概念。