Hi there
I am new to software development and have a test project I am working on. I have been trying to create a sql stored procedure and C# to insert data into a header table and a detail table. I need to be able to save multiple rows in the detail table which must be linked to a single header row id as per the picture below. The stored procedure I have created works but only saves one line in the header table and one in the detail table. As soon as I add a second line in the listview on my C# windows form and save it, it generates a id for the first row and another id for the second line.
Mageshwaran RPosted Nov 14, 2019, 12:13 PM
Jignesh KumarPosted Dec 10, 2018, 10:09 PM
Wynand VermaakPosted Dec 10, 2018, 9:23 AM
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GRHeader](
[Rec_Id] [int] IDENTITY(1000000,1) NOT NULL,
[Rec_Date] [date] NULL,
[Sup_Id] [int] NULL,
[Rec_Total] [money] NULL,
CONSTRAINT [PK_GRHeader] PRIMARY KEY CLUSTERED
(
[Rec_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GRDetails](
[Rec_Id] [int] NOT NULL,
[Rec_LineId] [int] NULL,
[Inv_Id] [int] NULL,
[Inv_Desc] [nvarchar](50) NULL,
[Rec_Qty] [int] NULL,
[Inv_Cost] [money] NULL,
[Rec_LineTotal] [int] NULL,
CONSTRAINT [PK_GRDetails] PRIMARY KEY CLUSTERED
(
[Inv_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_goodsreceived]
@Rec_Qty int,
@Rec_LineId int,
@Rec_Total money,
@Inv_SOH int,
@Inv_Id int,
@Inv_Desc nvarchar(50) NULL,
@Inv_Cost money NULL,
@Sup_Id int NULL,
@Rec_Date datetime NULL,
@Rec_Id int output
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RecID int
SET @Inv_SOH = (Select SUM(Inv_SOH) from Inventory where Inv_Id = @Inv_Id)
DECLARE @GetInv_Id CURSOR
SET @GetInv_Id = CURSOR FOR
SELECT Inv_Id, Inv_SOH
FROM Inventory
WHERE Inv_Id = @Inv_Id
SET @Rec_Total = @Rec_Qty * @Inv_Cost
OPEN @GetInv_Id
FETCH NEXT
FROM @GetInv_Id INTO @Inv_Id, @Inv_SOH
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Rec_Qty > 0
BEGIN TRAN A
INSERT INTO [GRHeader]
(Sup_Id, Rec_Date, Rec_Total)
VALUES (@Sup_Id, @Rec_Date, @Rec_Total);
SET @Rec_Id = SCOPE_IDENTITY();
COMMIT TRAN A
BEGIN TRAN B
SET @Rec_LineId = @@ROWCOUNT
SELECT @RecID = @@IDENTITY
INSERT INTO [GRDetails]
(Gr_Id, Gr_Line, Inv_Id, Inv_Desc, Gr_Qty, Inv_CostPrice, Inv_Soh)
VALUES (@RecID, @Rec_LineId, @Inv_Id, @Inv_Desc, @Rec_Qty, @Inv_Cost, @Inv_SOH);
COMMIT TRAN B
BEGIN TRAN C
UPDATE Inventory
SET Inv_SOH = Inv_SOH + @Rec_Qty
WHERE Inv_Id = @Inv_Id
COMMIT TRAN C
FETCH NEXT
FROM @GetInv_Id INTO @Inv_ID, @Inv_SOH
CLOSE @GetInv_Id
DEALLOCATE @GetInv_Id
END
END
GO
Code AlonePosted Dec 9, 2018, 2:06 PM