Geek Answers Handbook

Stored procedure for inserting two tables with binding?

I tried to insert a new row into two tables, between which there is a relationship. I wrote a stored procedure as follows.

ALTER PROCEDURE InsertUserProfile ( @UserID varchar(10), @Pass varchar(50), @Enabled int, @Permission int, @Rank int, @FName varchar(50), @LName varchar(50), @Phone varchar(50), @Email1 varchar(50), @Email2 varchar(50) ) AS BEGIN TRANSACTION INSERT INTO tbl_user_login VALUES (@UserID, @Pass, @Enabled, @Permission, @Rank) IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END INSERT INTO tbl_user_profile VALUES (@FName, @LName, @Phone, @Email1, @Email2) IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END COMMIT

It follows ASP.NET code

 SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString); SqlCommand cmd = new SqlCommand("dbo.InsertUserProfile", sqlConn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserID", DbType.String).Value = txtUserID.Text; cmd.Parameters.Add("@Pass", DbType.String).Value = txtPass.Text; cmd.Parameters.Add("@Enabled", DbType.Int32).Value = 1; cmd.Parameters.Add("@Permission", DbType.Int32).Value = Convert.ToInt32(ddlPermission.SelectedValue); cmd.Parameters.Add("@Rank", DbType.Int32).Value = Convert.ToInt32(ddlRank.SelectedValue); cmd.Parameters.Add("@FName", DbType.String).Value = txtFName.Text; cmd.Parameters.Add("@LName", DbType.String).Value = txtLName.Text; cmd.Parameters.Add("@Phone", DbType.String).Value = txtPhone.Text; cmd.Parameters.Add("@Email1", DbType.String).Value = txtEmail1.Text; cmd.Parameters.Add("@Email2", DbType.String).Value = txtEmail2.Text; sqlConn.Open(); int rows = cmd.ExecuteNonQuery(); sqlConn.Close();

But I get the following error.

The INSERT statement contradicted the FOREIGN KEY constraint "FK_tbl_user_profile_tbl_user_login". The conflict occurred in the database "My DB Location", table "dbo.tbl_user_login", column identifier. approval completed.

I am new to Stored Procedures, so any suggestions, how can I fix it so that I can insert data into two tables?

DIAGRAM TABLE

 tbl_user_login ID (int) UserID (varchar10) Pass (varchar50) Enabled (int) Permission (int) Rank (int) tbl_user_profile ID (int) FName (varchar50) LName (varchar50) Phone (varchar50) Email1 (varchar50) Email2 (varchar50)
+4
source share
3 answers

@Richard Property "ID", which is an automatic increment in both tables.

Having an automatic increment ( IDENTITY ) acts like a primary key in order, but using it as a foreign key is dangerous because you cannot guarantee that they will always be synchronized; any rollback can leave them broken (rollback does not cancel identification increments, as this will affect other SPIDs). In addition, any thread between two parallel INSERT will be at risk.

The correct approach here is to query SCOPE_IDENTITY() after the first insert and use it in INSERT on the second table; those. in the second table you specify the value. Note that since @@ERROR and SCOPE_IDENTITY() are floating values, you should request them immediately after the first INSERT :

 SELECT @Error = @@ERROR, @NewId = SCOPE_IDENTITY()
+6
source

You need to fix the automatically incremented value that you get when you insert into the first table, tbl_user_login. After capturing it, you need to use it to insert into the second table.

 DECLARE @ID int BEGIN TRANSACTION INSERT INTO tbl_user_login VALUES (@UserID, @Pass, @Enabled, @Permission, @Rank) SET @ID = SCOPE_IDENTITY() IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END INSERT INTO tbl_user_profile VALUES (@ID, @FName, @LName, @Phone, @Email1, @Email2) IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END COMMIT
+1
source 
 Create Table tbl_user_login ( **ID INT Identity(1,1),** UserID varchar10, Pass varchar50 , Enabled int, Permission int , Rank int );

Enter the identifier column as Identity (1,1), which will grow automatically, and this will solve your problem. Do this in both tables.

-1
source

More articles:


All Articles