Click here to Skip to main content
14,774,519 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have two tables. checkIn table and room table. checkin has many room (1 to many relationship). in my roomGridView i have 3 row belongs to checkin, my question is how can i save multiple row of 1 to many relation using store procedure in c# window form.

in my room table it has checkInId (latest id)// i try using SCOPE_IDENTITY(), i dont know how to use it for multiple time ? if i make two stored procedure how i can i get the checkInId ??

What I have tried:

//in my insertion 
using (SqlCommand cmd = new SqlCommand("sp_insertCheckIn", MainClass.con))
                    {
                        MainClass.con.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@guestId", guestId);
                        cmd.Parameters.AddWithValue("@dateIn", checkIn);

                      foreach (DataGridViewRow row in rooms)
                      {
                        cmd.Parameters.AddWithValue("@individualRoomId", 
                        row.Cells[13].Value.ToString());
                      }

                       cmd.ExecuteNonQuery();
                        MainClass.con.Close();
                        MainClass.showMessage("CheckIn Successfully.", "success");

// sql store procedure 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[sp_insertCheckIn]
@userId int,
@guestId int,
@dateIn datetime2(0),
...
AS
BEGIN
SET NOCOUNT ON;
insert into checkIn_table(checkIn_user_id, guest_id,date_in,date_out,no_day) 
    values (@userId,@guestId,@dateIn,@dateOut,@noDay) -- put values here (from parameters?)
insert into checkIn_room_table(checkIn_id, individual_room_id)
			values (SCOPE_IDENTITY(), @individualRoomId)  -- put other values here (from parameters?)// the problem is it has more than one and it is possible to use foreach here?
Posted
Updated 16-Jan-21 6:54am
v2

1 solution

From the MS documentation:
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Which means that provided you insert you data in the same SP, It will return the ID for the first insert to put into the second table, and if that table also has an IDENTITy column it will change for the next row.
If you want the whole of the second table to INSERT the same ID value to many rows, then store the value it returns and INSERT that:
INSERT INTO TableA ([Desc]) VALUES ('Hello');
DECLARE @lastID INT = SCOPE_IDENTITY(); 
INSERT INTO TableB (FID, [Desc]) VALUES (@lastID, 'World1');
INSERT INTO TableB (FID, [Desc]) VALUES (@lastID, 'World2');
INSERT INTO TableB (FID, [Desc]) VALUES (@lastID, 'World3');
   
Comments
Member 14921707 16-Jan-21 13:28pm
   
i want to see my code , i know that is work, but how can i use foreach in my case ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900