插入两个表中,第一个自动编号插入另一个表中 [英] Insert in two tables with the first auto number in another
本文介绍了插入两个表中,第一个自动编号插入另一个表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将表单中的数据插入到两个mysql表中,并将第一个表生成的自动号插入到另一个表中。我尝试了以下但是它给了我行中的错误cmd.CommandText = str_sql
类型' System.InvalidOperationException'在 MySql.Data中发生。 dll
请帮忙问候。
我尝试了什么:
函数 addrec()
connect()
Dim ireturn 作为 布尔
Dim str_sql as String = INSERT INTO` people`(`first_name`,`last_name`,`phone_number`,`mobile`,`email`,`address`,`city`,`state`,`zip`, `country`,`comments`)VALUES(@ firstname,@ lastname,@ phone,@ mobile,@ email,@ address,@ city,@ state,@ zip,@ country,@ comments)
cmd = 新 MySqlCommand
cmd.CommandText = str_sql
使用 cmd
.CommandType = CommandType.Text
.Parameters.AddWithValue( @ firstname ,txtfirstname.Text)
.Parameters.AddWithValue( @ lastname,txtlastname.Text )
.Parameters.AddWithValue( @ phone,txtphone.Text)
.Parameters.AddWithValue( @ mobile,txtmobile.Text)
.Parameters。 AddWithValue( @ email, txtemail.Text)
.Parameters.AddWithValue( @ address,txtaddress.Text)
.Parameters.AddWithValue( @ city,txtcity.Text)
.Parameters.AddWithValue( @ state,txtstate.Text)
.Parameters.AddWithValue ( @ zip,txtzip.Text)
.Parameters.AddWithValue( @ country,txtcountary.Text)
.Parameters.AddWithValue( @ comments,txtcomments.Text)
结束 < span class =code-keyword>使用
Dim newAutoNumberValue 正如 整数
尝试
cmd.ExecuteNonQuery()
cmd.CommandText = SELECT @@ IDENTITY
newAutoNumberValue = cmd.ExecuteScalar ()
ireturn = True
' MsgBox(记录添加成功!,成功)
' ClearTextBoxes ()
Catch ex As MySqlException
MsgBox(例如: Message.ToString)
ireturn = False
最后
' dbcon.Close()
结束 尝试
Dim str_sql2 作为 字符串 = INSERT INTO`customer`(`person_id`,`company`,`bank_name`,`account_number`)VALUES(@ autonum,@ company,@ bank,@ acc)
cmd = 新 MySqlCommand
cmd.CommandText = str_sql2
使用 cmd
.Parameters.AddWithValue( @ autonum,newAutoNumberValue)
.Parameters.AddWithValue( @ company,txtcompany.Text)
.Parameters.AddWithValue ( @ bank,txtbank.Text)
.Parame ters.AddWithValue( @ acc,txtaccnum.Text)
结束 使用
尝试
cmd.ExecuteNonQuery()
ireturn = True
MsgBox( < span class =code-string>记录添加成功! ,, 成功)
' ClearTextBoxes()
Catch ex As MySqlException
MsgBox(ex.Message.ToString)
ireturn = 错误
最后
dbcon.Close()
结束 尝试
返回 ireturn
结束 功能
解决方案
对于初学者来说,这应该在一个事务中 - 如果任一部分失败,它应该回滚两个INSERT操作。
我建议你应该将此作为存储过程与事务进行,并且使用 LAST_INSERT_ID() [ ^ ]插入第二个表格。
我道歉,表名是客户而不是客户。这就解决了这个问题。
感谢您的回复。
Hi,
I want to insert data from the form into two mysql tables and insert the first table generated auto number into another table. I tried the below but it gives me the error in the line cmd.CommandText = str_sql
An unhandled exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll
pls help regards.
What I have tried:
Function addrec()
connect()
Dim ireturn As Boolean
Dim str_sql As String = "INSERT INTO `people`( `first_name`, `last_name`, `phone_number`,`mobile`, `email`, `address`, `city`, `state`, `zip`, `country`, `comments`) VALUES (@firstname,@lastname,@phone,@mobile,@email,@address,@city,@state,@zip,@country,@comments)"
cmd = New MySqlCommand
cmd.CommandText = str_sql
With cmd
.CommandType = CommandType.Text
.Parameters.AddWithValue("@firstname", txtfirstname.Text)
.Parameters.AddWithValue("@lastname", txtlastname.Text)
.Parameters.AddWithValue("@phone", txtphone.Text)
.Parameters.AddWithValue("@mobile", txtmobile.Text)
.Parameters.AddWithValue("@email", txtemail.Text)
.Parameters.AddWithValue("@address", txtaddress.Text)
.Parameters.AddWithValue("@city", txtcity.Text)
.Parameters.AddWithValue("@state", txtstate.Text)
.Parameters.AddWithValue("@zip", txtzip.Text)
.Parameters.AddWithValue("@country", txtcountary.Text)
.Parameters.AddWithValue("@comments", txtcomments.Text)
End With
Dim newAutoNumberValue As Integer
Try
cmd.ExecuteNonQuery()
cmd.CommandText = "SELECT @@IDENTITY"
newAutoNumberValue = cmd.ExecuteScalar()
ireturn = True
' MsgBox("Record added successfully!",, "Succeeded")
'ClearTextBoxes()
Catch ex As MySqlException
MsgBox(ex.Message.ToString)
ireturn = False
Finally
' dbcon.Close()
End Try
Dim str_sql2 As String = "INSERT INTO `customer`( `person_id`, `company`, `bank_name`,`account_number`)VALUES (@autonum,@company,@bank,@acc)"
cmd = New MySqlCommand
cmd.CommandText = str_sql2
With cmd
.Parameters.AddWithValue("@autonum", newAutoNumberValue)
.Parameters.AddWithValue("@company", txtcompany.Text)
.Parameters.AddWithValue("@bank", txtbank.Text)
.Parameters.AddWithValue("@acc", txtaccnum.Text)
End With
Try
cmd.ExecuteNonQuery()
ireturn = True
MsgBox("Record added successfully!",, "Succeeded")
'ClearTextBoxes()
Catch ex As MySqlException
MsgBox(ex.Message.ToString)
ireturn = False
Finally
dbcon.Close()
End Try
Return ireturn
End Function
解决方案
For starters, that should be in a transaction - if either part fails, it should roll back both INSERT operations.
I'd suggest that you should do this as a Stored Procedure with the transaction, and use LAST_INSERT_ID()[^] to insert in the second table.
My apology the table name is customers and not customer. and that solved the issue.
Thanks for yours replies.
这篇关于插入两个表中,第一个自动编号插入另一个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文