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:
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();
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)
{
cmd.Parameters.AddWithValue("@individualRoomId", rowRoom.Cells[13].Value.ToString());
}
foreach (DataGridViewRow rowPayment in payment)
{
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);
}
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();
}
}
USE [HMS]
GO
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