Click here to Skip to main content
15,393,377 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i try to insert 3 table at once and the the two table are 1 to many relation. it is working for single row insertion, but when i use foreach it trigger error of "has many argument".
i dont know how to add 3 table with muliple row at onces any helps, thanks.

What I have tried:

C#
public static void insertCheckIn(Int32 guestId, Int32 userId, string guestName, DateTime checkIn, DateTime checkOut, Int16 noDay, DataGridViewRowCollection rooms, DataGridViewRowCollection payment, Int32 noMale, Int32 noFemale,
            Int32 noKid, )
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("sp_insertCheckIn", MainClass.con))
                {
                    MainClass.con.Open();
                    //checkIn Table
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@userId", userId);
                    cmd.Parameters.AddWithValue("@guestId", guestId);
                    cmd.Parameters.AddWithValue("@dateIn", checkIn);
                    cmd.Parameters.AddWithValue("@dateOut", checkOut);
                    cmd.Parameters.AddWithValue("@noDay", noDay);

                    foreach (DataGridViewRow rowRoom in rooms)
                    {
                       //checkIn room table
                       cmd.Parameters.AddWithValue("@individualRoomId", rowRoom.Cells[13].Value.ToString());
                    }

                    foreach (DataGridViewRow rowPayment in payment)
                    {
                    // checkInPayment table
                    
                  cmd.Parameters.AddWithValue("@paymenentMode",rowPayment.Cells[1].Value ?? DBNull.Value);
                   cmd.Parameters.AddWithValue("@totalPaid", rowPayment.Cells[2].Value ?? DBNull.Value);
                   cmd.Parameters.AddWithValue("@paymentDate", rowPayment.Cells[3].Value ?? DBNull.Value);
                        }

                    
                    //checkIn_info Table 
                    cmd.Parameters.AddWithValue("@noMale", noMale);
                    cmd.Parameters.AddWithValue("@noFemale", noFemale);
                    cmd.Parameters.AddWithValue("@noKid", noKid);

                    cmd.Parameters.AddWithValue("@createdAt", createdAt);
                    cmd.Parameters.AddWithValue("@updatedAt", updatedAt);
                    cmd.ExecuteNonQuery();
                    MainClass.con.Close();
                    MainClass.showMessage("CheckIn Successfully.", "success");
                }       

            }
            catch (Exception ex)
            {
                MainClass.showMessage("Exception : " + ex, "error");
                MainClass.con.Close();
            }
        }


// SQL STORE PROCEDURE 

USE [HMS]
GO
/****** Object:  StoredProcedure [dbo].[sp_insertCheckIn]    Script Date: 1/17/2021 1:44:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_insertCheckIn]
	-- Add the parameters for the stored procedure here
		@userId int,
		@guestId int,
		@dateIn datetime2(0),
		@dateOut datetime2(0),
		@noDay int,
		@individualRoomId int,
		@paymenentMode varchar(255),
		@totalPaid decimal(18,2),
		@paymentDate datetime2(0),
		@noMale int,
		@noFemale int,
		@noKid int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
	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?)
	DECLARE @lastID INT = SCOPE_IDENTITY(); 

hOW ???
END
Posted
Updated 17-Jan-21 0:42am
Comments
Richard Deeming 18-Jan-21 6:08am
   

Here is an example using a datatable that saves multiple database requests: Insert Multiple Rows At A Time From C# To SQL[^]
   
Because your loop keeps adding more parameters of the same name to teh same command, and your procedure has no idea what to do with them.

Instead, consider passing a DataTable: Sending a DataTable to a Stored Procedure[^]
   

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