在sql server中插入最近添加的记录的id作为forigh key [英] inserting id of recently added record as forigh key in sql server

问题描述

我想在其他表中插入记录的id为外键。

我已经使用 Scope_Identity检索了添加的最后一条记录的id （）属性。

我现在已将其值分配给标签o想要将标签文本作为整数插入数据库但是它不起作用？我怎么能把它插入表？或者我如何将范围标识方法得到的值插入到其他表中

这是我的示例代码：

I want to insert id of a record as Foreign Key in other table.
I have retrieved id of last record added by using Scope_Identity() property.
I have assigned its value to a label now o want to insert text of label as integer into database but its not working? How i can insert this into table? or how i can insert the value got by the scope identity method into other table
Here is my sample code:

InsertCommand = "Insert into [Person] ([Name],[age] ) Values (@name,@age);SET @NewId = Scope_Identity()"
<asp:Parameter DefaultValue="0" Direction="Output" Name="Newid" Type="Int32"  />

和.cs文件

and in .cs file

int newid = (int)e.Command.Parameters["@NewId"].Value;
this.label4.Text+  = newid;

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

       ConnectionString ="<%$ ConnectionStrings:ConnectionString %>"

       InsertCommand="Insert into [Details] ([Contact],[address],[Id]) Values (@con,@det,@Newid)">
    <InsertParameters>
        <asp:ControlParameter  Name="con" ControlID="TextBox5" Type="String"/>
        <asp:ControlParameter  Name="det" ControlID="TextBox4" Type="String"/>
        <asp:ControlParameter Name="Newid" ControlID="Label4" Type="Int32" />  
    </InsertParameters>

如果有其他方式请指导我。

if there is any other way please guide me.

推荐答案

ConnectionStrings：ConnectionString ％>

InsertCommand = 插入[详细信息]（[联系人]，[地址]，[Id]）值（@ con，@ det，@ Newid） >
< InsertParameters >
< asp：ControlParameter 名称 = con ControlID = TextBox5 类型 = 字符串 / >
< asp：ControlParameter 名称 = det ControlID = TextBox4 类型 = 字符串 / >
< asp：ControlParameter 名称 = Newid ControlID = Label4 类型 = Int32 / >
< / InsertParameters >

ConnectionStrings:ConnectionString %>" InsertCommand="Insert into [Details] ([Contact],[address],[Id]) Values (@con,@det,@Newid)"> <InsertParameters> <asp:ControlParameter Name="con" ControlID="TextBox5" Type="String"/> <asp:ControlParameter Name="det" ControlID="TextBox4" Type="String"/> <asp:ControlParameter Name="Newid" ControlID="Label4" Type="Int32" /> </InsertParameters>

如果有其他方式请指导我。

if there is any other way please guide me.

子表中的插入类似于父表的插入。唯一的事情是（正如你所说的）是使用正确的，新插入的值作为外键。



既然你没有发布表结构也没有完整的代码，我将问题分解成碎片。

1.检查第一个插入是否有效

2.检查变量 NewId

3.在第二次插入期间检查是否有任何错误



另外我更喜欢使用 OUTPUT 子句而不是单独的函数调用。有关信息，请参阅 OUTPUT子句（Transact-SQL） [ ^ ]

The insert into the child table is similar to the insert to the parent table. The only thing is (as you said) is to use the correct, newly inserted value as a foreign key.

Since you haven't posted the table structures nor the complete code, I'd break the problem into pieces.
1. check that the first insert works
2. check the return value from the variable NewId
3. check for any errors during the second insert

Also I'd prefer using OUTPUT clause instead of a separate function call. For information refer to OUTPUT Clause (Transact-SQL)[^]

这篇关于在sql server中插入最近添加的记录的id作为forigh key的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！