[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

Insert @@Identity of table into another table as a foreign key.

I am trying to get the last inserted ID of an identity field in my database.  I then want to insert that id into another table as a foreign key.  I keep getting 'Insert statement conflicted with column foreign key contraint' error.  Here is the code.

SqlCommand addBondInfoCmd = new SqlCommand("INSERT INTO [BondInformationForm] " +
                                                                "([Student_ID], " +
                                                                "[BondInfo_AddDate], " +
                                                                "[BondInfo_AddUser])" +
                                                               
                                                        "VALUES (" +
                                                                Session["StuID"] + "," +
                                                                "'" + DateTime.Now + "', " +
                                                                "'" + User.Identity.Name.ToString() + "')", con);
SqlCommand addBankInfo = new SqlCommand("INSERT INTO [Bank] "+
                                                        "([Student_ID], " +
                                                        "[BondInfo_ID], "+
                                                        "[Bank_Name], " +
                                                        "[Bank_AcctNo]) " +
                                                    "VALUES (" +
                                                                Session["StuID"] + ", " +
                                                                bondID + ", " +
                                                                "'" + tbBName.Text.ToString() + "', " +
                                                                "'" + tbAcctNo.Text.ToString() + "')", con);
        try
        {
            //Execute the Commands
            con.Open();

            addStudentBondInfoCmd.ExecuteNonQuery();
            addBondInfoCmd.ExecuteNonQuery();
           
            //Get the Bond Info Form ID
            //SqlCommand getBondInfoID = new SqlCommand("SELECT [BondInfo_ID]from BondInformationForm
                                                                        Where [Student_ID] = " + Session["StuID"], con);
            SqlCommand getBondInfoID = new SqlCommand("SELECT @@Identity  from BondInformationForm",
                                                                       con);

            //Create the Data Adapter
            SqlDataAdapter daBondInfoID = new SqlDataAdapter(getBondInfoID);

            //Access the Dataset
            dsStudent dsBondInfoID = new dsStudent();

            //Fill the Data Adapter
            dsBondInfoID.EnforceConstraints = false;
            daBondInfoID.Fill(dsBondInfoID, "BondInformationForm");

            bondID = int.Parse(getBondInfoID.ExecuteScalar().ToString());
            tbAcctNo.Text = getBondInfoID.ExecuteScalar().ToString();

            addBankInfo.ExecuteNonQuery();

            con.Close();

What's wrong with this code?  
0
VibertH
Asked:
VibertH
  • 6
  • 5
1 Solution
 
srafi78Commented:
From Books Online
>>>@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.<<<

0
 
VibertHAuthor Commented:
I know how to use the function.  The problem I have is I execute another insert statement right after the @@Identity and need that identity to be passed to the second insert statement.
0
 
srafi78Commented:
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.

Syntax
IDENT_CURRENT('table_name')

Arguments
table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

Return Types
sql_variant

Remarks
IDENT_CURRENT is similar to the Microsoft® SQL Server" 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
>>>
I guess you have to store @@Identity in a seperate variable and then use this variable in the next insert statement <<<
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
VibertHAuthor Commented:
>>>
I guess you have to store @@Identity in a seperate variable and then use this variable in the next insert statement <<<

I did that.  
this is the insert statement I need to use it in

SqlCommand addBankInfo = new SqlCommand("INSERT INTO [Bank] "+
                                                        "([Student_ID], " +
                                                        "[BondInfo_ID], "+
                                                        "[Bank_Name], " +
                                                        "[Bank_AcctNo]) " +
                                                    "VALUES (" +
                                                                Session["StuID"] + ", " +
                                                                bondID + ", " +
                                                                "'" + tbBName.Text.ToString() + "', " +
                                                                "'" + tbAcctNo.Text.ToString() + "')", con);

this is the where i get the bondID

SqlCommand getBondInfoID = new SqlCommand("SELECT @@Identity  from BondInformationForm",
                                                                       con);

            //Create the Data Adapter
            SqlDataAdapter daBondInfoID = new SqlDataAdapter(getBondInfoID);

            //Access the Dataset
            dsStudent dsBondInfoID = new dsStudent();

            //Fill the Data Adapter
            dsBondInfoID.EnforceConstraints = false;
            daBondInfoID.Fill(dsBondInfoID, "BondInformationForm");

            bondID = int.Parse(getBondInfoID.ExecuteScalar().ToString());
and the insert is executed right after this.  see the code in the first post.  it's throwing an sqlexception on the second insert.
0
 
srafi78Commented:
Okay, Why not try and execute the whole sql as a Stored Procedure, looks like you are inserting values in two tables, In the second insert you are using the values from 1st insert.

something like

Create procedure spAddInfo(@StudentID, @BondInfo_AddDate, @BondInfo_AddUser, @BankName)
AS
Declare @BondID as INT
Declare @tbAcctNo as Varchar(20)
Declare @Identity as INT

BEGIN
INSERT INTO [BondInformationForm] ([Student_ID], [BondInfo_AddDate], [BondInfo_AddUser])
VALUES (@StudentID,@BondInfo_AddDate,@BondInfo_AddUser)
Select @BondID = @@identity
Select @tbAcctNo = [BondInfo_ID]from BondInformationForm Where [Student_ID] = @StudentID)
INSERT INTO [Bank] ([Student_ID],[BondInfo_ID], [Bank_Name],[Bank_AcctNo])
VALUES (@StudentID, @BondID, @BankName, @tbAcctNo')
END
Execute this proc may give you what you need
0
 
srafi78Commented:
Use transactions to handle errors while update
0
 
VibertHAuthor Commented:
Well, I wasn't using a proc (even though I know that's best practice).  I have moved from the easy ADO.NET coding in VS 2003 (everything is drag and drop) to coding everything in 2005.  The procs where giving me all kinds of errors.  So I skipped that and just put the statements directly in the code.  So, rather than redoing everything, I will move forward as it.  Therefore, I proc is not my solution.

Any other suggestions with the code?
0
 
srafi78Commented:
Question: Did you verify that you are getting a valid value for the bondID and tbAcctNo before executing the second insert.
Invalid values in the variables may be causing the problem...
0
 
VibertHAuthor Commented:
I got it.  Since the textbox, tbAcctNo, had the correct value, I simply assigned  the @@Identity value to a hidden field and then used that field in the insert statement.  It's not the best way to do this, but it works.  I wish I knew why I had this problem, but oh well, I can't spend all day on it.

Thanks anyway.
0
 
VibertHAuthor Commented:
Never mind.  It only works if there is an error first and the value has been written to the lable.  So I still need help.  

To answer your question, tbAcctNo is a text box on my form that I wrote the value to see if it return the correct value.  It does, but  execute scalor runs after I have asssigned the value from the textbox.  The insert statement is initialized before the execute scalor statement.
0
 
srafi78Commented:
Did you try initializing the insert statement after the execute scalar method?

What I usually do is store the SQL in a string field and then initialize each object as I need it
Like
conn = getConnection() 'i always use a readonly property to get and initialize the connection can be used anywhere in the code
strSql1 = "Select..."
strSql2 = "Insert..."

conn.Open()
cmd = new sqlcommand(strSql2, conn)
....
cmd = new sqlcommand(strSql1, conn)
....
conn.close()

Hope this helps. For me using the SP is the best option though...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now