• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1021
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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