如何将数据插入关系表 [英] How to insert data into relational table
本文介绍了如何将数据插入关系表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有关系表,这是我想做的。
1.将数据插入表格
2.获取最后插入的行ID
3.将最后插入的行ID插入关系表
错误:
'LAST_INSERT_ID'无法识别内置函数名称
这是我的代码
私人 Sub btnSave_Click(发件人作为 对象,e As EventArgs)句柄 btnSave.Click
cn.Open()
使用 cmd 作为 新 SqlClient.SqlCommand( INSERT INTO tblcustomer(FirstName,LastName,Imag e)价值观('& txtName.Text& ','& txtSurname.Text& ',@ customerPic),cn)
结束 使用
使用 cmd 作为 新 SqlClient.SqlCommand( < span class =code-string> INSERT INTO tblProduct(ProductName,Quantity,CustID)VALUES('& txtProductName.Text& < span class =code-string>','& txtQuantity.Text& ' ,LAST_INSERT_ID()),cn)
cmd.Parameters.Add(新 SqlClient.SqlParameter( @ customerPic,SqlDbType.Image))。Value = IO.File.ReadAllBytes(a.FileName )
i = cmd.ExecuteNonQuery
如果(i> 0 )然后
MsgBox( 保存& i& 成功录制)
结束 如果
结束 使用
cn.Close()
结束 Sub
解决方案
那是因为LAST_INSERT_ID()不是SQL Server函数 - 它是MySQL函数和MySQL!= MSSQL
- 正如错误信息所说 - 悲伤但是真实 - 这是一个链接,其中讨论了一些替代方案
https://www.sitepoint.com/com munity / t / convert-mysql-last-insert-id-to-mssql-2005/36998/4 [ ^ ]
您将需要< a href =https://msdn.microsoft.com/en-sg/library/ms190315.aspx> SCOPE_IDENTITY(Transact-SQL) [ ^ ]。
查看这篇文章也有一些想法向您的数据库询问该唯一ID [ ^ ]
使用 cmd 作为 新 SqlClient.SqlCommand( INSERT INTO tblcustomer(FirstName,LastName,Image)VALUES(@ fName,@ lName,@ customerPic),cn)
结束 使用
使用 cmd As 新 SqlClient.SqlCommand( INSERT INTO tblProduct(ProductName ,Quantity,CustID)VALUES(@ ProdName,@ Quantity,@ custID),cn)
结束 使用
cmd.Parameters.Add( @ fname ,SqlDbType.VarChar, 100 )。Value = txtName.Text
cmd.Parameters.Add( @ lName,SqlDbType.VarChar, 100 )。Value = txtSurname.Text
cmd.Parameters.Add(新 S. qlClient.SqlParameter( @ customerPic,SqlDbType.Image))。Value = IO.File.ReadAllBytes (a.FileName)
cmd.Parameters.Add( @ Prodname,SqlDbType。 VarChar, 100 )。Value = txtProductName.Text
cmd.Parameters.Add( < span class =code-string> @ Quantity,SqlDbType.VarChar, 100 )。Value = txtQuantity.Text
cmd.Parameters .Add( @ custID,SqlDbType.VarChar, 100 )。值=( 选择@@ IDENTITY)
i = cmd.ExecuteScalar
如果(i> 0 )然后
MsgBox( 保存& i& 成功录制)
结束 如果
这就是我所做的,它说成功保存记录但我的sqlserver中没有保存数据。有人可以帮我解决这个问题吗?感谢
I have relational tables and here is i want to do.
1. insert data into table
2. Get the last inserted row id
3. Insert the last inserted row id into the relational tables
Error :
'LAST_INSERT_ID' is not recognized built-in function name
Here is my code
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
cn.Open()
Using cmd As New SqlClient.SqlCommand("INSERT INTO tblcustomer(FirstName,LastName,Image)VALUES('" & txtName.Text & "' ,'" & txtSurname.Text & "', @customerPic )", cn)
End Using
Using cmd As New SqlClient.SqlCommand("INSERT INTO tblProduct(ProductName,Quantity,CustID)VALUES('" & txtProductName.Text & "' ,'" & txtQuantity.Text & "',LAST_INSERT_ID())", cn)
cmd.Parameters.Add(New SqlClient.SqlParameter("@customerPic", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName)
i = cmd.ExecuteNonQuery
If (i > 0) Then
MsgBox("Save " & i & "Record Successfully")
End If
End Using
cn.Close()
End Sub
解决方案
thats because LAST_INSERT_ID() isnt a SQL Server function - its a MySQL function and MySQL != MSSQL
- just as the error message says - sad but true - here's a link where some alternatives are discussed
https://www.sitepoint.com/community/t/convert-mysql-last-insert-id-to-mssql-2005/36998/4[^]
You will need SCOPE_IDENTITY (Transact-SQL)[^].
Check up this article for some idea too Ask Your Database for that Unique ID[^]
Using cmd As New SqlClient.SqlCommand("INSERT INTO tblcustomer(FirstName,LastName,Image)VALUES(@fName,@lName, @customerPic )", cn) End Using Using cmd As New SqlClient.SqlCommand("INSERT INTO tblProduct(ProductName,Quantity,CustID)VALUES(@ProdName,@Quantity, @custID )", cn) End Using cmd.Parameters.Add("@fname", SqlDbType.VarChar, 100).Value = txtName.Text cmd.Parameters.Add("@lName", SqlDbType.VarChar, 100).Value = txtSurname.Text cmd.Parameters.Add(New SqlClient.SqlParameter("@customerPic", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName) cmd.Parameters.Add("@Prodname", SqlDbType.VarChar, 100).Value = txtProductName.Text cmd.Parameters.Add("@Quantity", SqlDbType.VarChar, 100).Value = txtQuantity.Text cmd.Parameters.Add("@custID", SqlDbType.VarChar, 100).Value = ("Select @@IDENTITY") i = cmd.ExecuteScalar If (i > 0) Then MsgBox("Save " & i & "Record Successfully") End If
This is what i did, it says successfully save record but no save data in my sqlserver. can someone help me to fix this? thanks
这篇关于如何将数据插入关系表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文