将自动生成的主键字段插入相关的多个表中 [英] Inserting an auto generated primary key field into related multiple tables

问题描述

我正在尝试将自动生成的主键字段值插入多个相关表，下面是我的示例插入代码，它想一次保存2个表

Hie guys i''m trying to insert an auto generating primary key field value to multple related tables below is my sample insert code it suposed to save 2 tables at once

sql = "INSERT INTO dbo.Clients (FirstName,Surname) values(''" & (txtName.Text) & "'',''" & (txtSuname.Text) & "'' )"
        sql2 = "INSERT INTO dbo.PersonalProduct(Product,Details)values(,''" & (txtProduct.Text) & "'',''" & (txtDetails.Text) & "'')"

Clients表的主键是自动生成的，主键是PersonProduct表中的外键，但是主键的值未插入PersonProduct表中，如何最好地解决此问题，数据库是SQL Server

the primary key for the Clients table is auto generated the primary key is a foreign key in the PersonProduct Table but the value of primary key is not inserted into the PersonProduct Table how best can overcome this problem the database is SQL Server

推荐答案

既然您发布了看起来像VB.NET代码的代码，那么我认为这不在存储过程中吗?

在存储过程中这样做更容易，但是您需要获取在第一个INSERT命令中添加的记录的@@ IDENTITY.您没有返回该ID的信息.一旦获得ID，将其放入第二张表的INSERT中就很简单了.

阅读此 [

Since you posted what looks like VB.NET code, I take it this is not in a stored procedure?

It''s easier to do in a stored procedure, but you need to get the @@IDENTITY of the record that was added in the first INSERT command. You have nothing returning that ID. Once you get the ID, it''s trivial to put it in the INSERT of the second table.

Read this[^] for a discussion of the concept.

Now, other problems I see with your code is that you''re using string contatentation to build the SQL queries. DON''T! Use parameterized queries instead, or better yet, a stored procedure to do this, and populate the parameters using SqlParameter objects.

You''re also not validating the data you have in the textbox fields.

Want to know why this is bad?? What do you think would happen if I typed:

x'',''x''); DROP TABLE Clients; --

进入任何一个文本框?

尝试阅读本文 [

into either of those textboxes??

Try reading this article[^] on SQL Injection Attacks and find out how to avoid this problem.

有这里有很多东西...
1)我讨厌自动生成的密钥.为什么?因为如果需要引用它们，则必须使用存储过程来创建虚拟记录并返回ID，然后再填充它，或者创建记录，然后将其取回以找出它的ID.创建了.是的，有一些方法可以解决这个问题，但是它们是PITA，并不总是在多用户环境中工作.相反，如果以后需要引用该记录，则使用GUID ID.这样做的好处是，由于PC分配了ID，因此您只需一趟DB就可以保存记录，并且可以继续进行操作.
2)您确实需要研究使用围绕该代码的事务，或将两个更新移动到单个存储过程中并在其中进行处理.为什么?如果第二次插入失败，该怎么办?您已经创建了该客户端，但是会出现错误，并且您将不会；如果不确定您是否确实做到了，那么您可能会再次尝试创建该客户端...通过事务，您要么立即提交所有更改，要么，或者将它们全部回滚，因此数据库始终包含有效数据.
3)不要串联你的琴弦！特别是文本框内容！ Google进行"Bobby Tables"或"Sql Injection攻击"，然后使用参数化查询，然后无意或故意破坏数据库...

There are so many things here...
1) I hate autogenerated keys. Why? Because if you need to refer to them, you either have to use a stored procedure to create a dummy record and return the ID, then fill it later, or create the record, and then retrieve it back to find out what the id it just created was. Yes, there are some ways to get round this, but they are a PITA and don''t always work in an multiuser environment. Instead, I use GUID id''s if I need to refer to that record later. This has the advantage that since the PC is assigning the ID you only need one trip to the DB to save the record, and you can move on.
2) You really need to look at using a Transaction around that code, or moving the two updates into a single Stored Procedure and transact it there. Why? What happens if the second insert fails? You have created the client, but then there will be an error, and you won;t be sure if you really did - so you will likely try to create him again... With a transaction, you either commit all the changes at once, or you roll them all back, so your database always contains valid data.
3) Don''t concatenate your strings! Particularly with textbox contents! Google for "Bobby Tables" or "Sql Injection attack" and then use parametrized queries before you accidentally or deliberately get your database destroyed...

这篇关于将自动生成的主键字段插入相关的多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！