Click here to Skip to main content
14,920,592 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
   

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[^]
   
Here is an example using a datatable that saves multiple database requests: Insert Multiple Rows At A Time From C# To SQL[^]
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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