inserting relational data in one master and three childs using dataset and dataadapter

Question

1.00/5 (1 vote)

See more:

iam new to vb.net 2010 and trying to save data in four relational tables one master and three childs using dataset and data adapter and stored procedure in sql server 2008 but it returns error.if i try this for two tables it works for one master and one child. The Error is "Procedure or function proc_AddCutReshapeInputDetail has to many arguments specified" My Code is as follows

SQL

/*=================================================================================	Add - Insert Cutting_Reshape
=================================================================================*/
DROP PROCEDURE proc_Insert_CuttingReshape
CREATE PROCEDURE proc_Insert_CuttingReshape
(
@Cut_ReshapeId int output,
@Cut_ReshapeNo int,
@Cut_ReshapeDate datetime,
@Cut_ReshapeType varchar(7),
@Cut_ReshapeInputQty float,
@Cut_ReshapeInputAmount float,
@Cut_ReshapeOutputQty float,
@Cut_ReshapeOutputAmount float,
@Cut_ReshapeWasteQty float,
@AddBy int,
@AddOn datetime
)
AS 
INSERT INTO Cut_Reshape(Cut_ReshapeNo,Cut_ReshapeDate,Cut_ReshapeType,Cut_ReshapeInputQty,Cut_ReshapeInputAmount,
	            Cut_ReshapeOutputQty,Cut_ReshapeOutputAmount,Cut_ReshapeWasteQty,AddBy,AddOn)
	VALUES
				(@Cut_ReshapeNo,@Cut_ReshapeDate,@Cut_ReshapeType,@Cut_ReshapeInputQty,@Cut_ReshapeInputAmount,
				 @Cut_ReshapeOutputQty,@Cut_ReshapeOutputAmount,@Cut_ReshapeWasteQty,@AddBy,@AddOn)
	SELECT @Cut_ReshapeId=@@IDENTITY


/*=================================================================================Add - Insert New Cutting_Reshape Input Detail
=================================================================================*/

ALTER PROCEDURE proc_AddCutReshapeInputDetail
	(
	@Cut_ReshapeId int,
	@InputProductId int,
	@InputPcs float,
	@InputQty float,
	@InputCost float,
	@InputCostAmount float,
	@InputStkId int
	)
	AS 
	
	--Insert Records in Cut_ReshapeInput table 
	INSERT INTO Cut_ReshapeInput(Cut_ReshapeId,ProductId,InputPcs,InputQty,InputCost,InputCostAmount,InputStkId)
	VALUES
	(@Cut_ReshapeId,@InputProductId,@InputPcs,@InputQty,@InputCost,@InputCostAmount,@InputStkId)
		

/*=================================================================================
Add - Insert New Cutting_Reshape Output Detail
=================================================================================*/

ALTER PROCEDURE proc_AddCutReshapeOutputDetail
	(
	@Cut_ReshapeId int,
	@OutputProductId int,
	@OutputQty float,
	@OutputCost float,
	@OutputCostAmount float
	)
	AS 
	
	INSERT INTO Cut_ReshapeOutput(Cut_ReshapeId,ProductId,OutputQty,OutputCost,OutputCostAmount)
	VALUES
	(@Cut_ReshapeId,@OutputProductId,@OutputQty,@OutputCost,@OutputCostAmount)
/*=================================================================================
	Add - Insert New Cutting_Reshape Waste Detail
=================================================================================*/
ALTER PROCEDURE proc_AddCutReshapeWasteDetail
	(

	@Cut_ReshapeId int,
	@WasteProductId int,
	@WasteQty float
	)
	AS
	INSERT INTO Cut_ReshapeWaste(Cut_ReshapeId,ProductId,WasteQty )
	VALUES
	(@Cut_ReshapeId,@WasteProductId,@WasteQty)

VB.NET CODE

VB

Private Sub addCutReshape()
    Dim cmdCutReshape As SqlCommand
    Dim dsCutReshape As New DataSet
    Dim daCutReshape As SqlDataAdapter
    Dim daTable As DataTable

    Try

        db.ConnectDB()

        db.objTrans = db.Con.BeginTransaction("TransCutReshape")
        daCutReshape = New SqlDataAdapter("Select * from Cut_Reshape", db.Con)
        daCutReshape.InsertCommand = New SqlCommand("proc_Insert_CuttingReshape", db.Con)
        cmdCutReshape = daCutReshape.InsertCommand
        daCutReshape.SelectCommand.Transaction = db.objTrans
        daCutReshape.InsertCommand.Transaction = db.objTrans
        cmdCutReshape.CommandType = CommandType.StoredProcedure

        cmdCutReshape.Parameters.Add("@Cut_ReshapeId", SqlDbType.Int)
        cmdCutReshape.Parameters("@Cut_ReshapeId").Direction = ParameterDirection.Output
        cmdCutReshape.Parameters("@Cut_ReshapeId").SourceColumn = "Cut_ReshapeId"

        cmdCutReshape.Parameters.Add("@Cut_ReshapeNo", SqlDbType.Int, 4, "Cut_ReshapeNo")
        cmdCutReshape.Parameters("@Cut_ReshapeNo").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeDate", SqlDbType.DateTime, 8, "Cut_ReshapeDate")
        cmdCutReshape.Parameters("@Cut_ReshapeDate").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeType", SqlDbType.VarChar, 7, "Cut_ReshapeType")
        cmdCutReshape.Parameters("@Cut_ReshapeType").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeInputQty", SqlDbType.Float, 24, "Cut_ReshapeInputQty")
        cmdCutReshape.Parameters("@Cut_ReshapeInputQty").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeInputAmount", SqlDbType.Float, 24, "Cut_ReshapeInputAmount")
        cmdCutReshape.Parameters("@Cut_ReshapeInputAmount").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeOutputQty", SqlDbType.Float, 24, "Cut_ReshapeOutputQty")
        cmdCutReshape.Parameters("@Cut_ReshapeOutputQty").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeOutputAmount", SqlDbType.Float, 24, "Cut_ReshapeOutputAmount")
        cmdCutReshape.Parameters("@Cut_ReshapeOutputAmount").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@Cut_ReshapeWasteQty", SqlDbType.Float, 24, "Cut_ReshapeWasteQty")
        cmdCutReshape.Parameters("@Cut_ReshapeWasteQty").Direction = ParameterDirection.Input

        cmdCutReshape.Parameters.Add("@AddBy", SqlDbType.Int, 4, "AddBy")
        cmdCutReshape.Parameters("@AddBy").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters.Add("@AddOn", SqlDbType.DateTime, 8, "AddOn")
        cmdCutReshape.Parameters("@AddOn").Direction = ParameterDirection.Input
        daCutReshape.FillSchema(dsCutReshape, SchemaType.Source)

        daTable = dsCutReshape.Tables("Table")
        daTable.TableName = "Cut_Reshape"



        Dim daCutReshapeInput As SqlDataAdapter
        daCutReshapeInput = New SqlDataAdapter("Select * from Cut_ReshapeInput", db.Con)
        daCutReshapeInput.InsertCommand = New SqlCommand("proc_AddCutReshapeInputDetail", db.Con)
        cmdCutReshape = daCutReshapeInput.InsertCommand
        daCutReshapeInput.SelectCommand.Transaction = db.objTrans
        daCutReshapeInput.InsertCommand.Transaction = db.objTrans
        cmdCutReshape.CommandType = CommandType.StoredProcedure

        cmdCutReshape.Parameters.Add("@Cut_ReshapeId", SqlDbType.Int, 4, "Cut_ReshapeId")
        cmdCutReshape.Parameters("@Cut_ReshapeId").Direction = ParameterDirection.Input
        cmdCutReshape.Parameters("@Cut_ReshapeId").SourceColumn = "Cut_ReshapeId"

        cmdCutReshape.Parameters.Add("@InputProductId", SqlDbType.Int, 4, "ProductId")
        cmdCutReshape.Parameters("@InputProductId").Direction = ParameterDirection.Input

        cmdCutReshape.Parameters.Add("@InputPcs", SqlDbType.Float, 24, "InputPcs")
        cmdCutReshape.Parameters("@InputPcs").Direction = ParameterDirection.Input

        cmdCutReshape.Parameters.Add("@InputQty", SqlDbType.Float, 24, "InputQty")
        cmdCutReshape.Parameters("@InputQty").Direction = ParameterDirection.Input

        cmdCutReshape.Parameters.Add("@InputCost", SqlDbType.Float, 24, "InputCost")
        cmdCutReshape.Parameters("@InputCost").Direction = ParameterDirection.Input

        cmdCutReshape.Parameters.Add("@InputCostAmount", SqlDbType.Float, 24, "InputCostAmount")
        cmdCutReshape.Parameters("@InputCostAmount").Direction = ParameterDirection.Input

        cmdCutReshape.Parameters.Add("@InputStkId", SqlDbType.Int, 4, "InputStkId")
        cmdCutReshape.Parameters("@InputStkId").Direction = ParameterDirection.Input

        daCutReshapeInput.FillSchema(dsCutReshape, SchemaType.Source)
        daTable = dsCutReshape.Tables("Table")
        daTable.TableName = "Cut_ReshapeInput"
        dsCutReshape.Relations.Add(New DataRelation("InputParentChild", dsCutReshape.Tables("Cut_Reshape").Columns("Cut_ReshapeId"), dsCutReshape.Tables("Cut_ReshapeInput").Columns("Cut_ReshapeId")))

        Dim cmdCutReshapeOutput As SqlCommand
        Dim daCutReshapeOutput As SqlDataAdapter
        daCutReshapeOutput = New SqlDataAdapter("Select * from Cut_ReshapeOutput", db.Con)
        daCutReshapeOutput.InsertCommand = New SqlCommand("proc_AddCutReshapeOutputDetail", db.Con)
        cmdCutReshapeOutput = daCutReshapeInput.InsertCommand
        daCutReshapeOutput.SelectCommand.Transaction = db.objTrans
        daCutReshapeOutput.InsertCommand.Transaction = db.objTrans
        cmdCutReshapeOutput.CommandType = CommandType.StoredProcedure
        cmdCutReshapeOutput.Parameters.Add("@Cut_ReshapeId", SqlDbType.Int, 4, "Cut_ReshapeId")
        cmdCutReshapeOutput.Parameters("@Cut_ReshapeId").Direction = ParameterDirection.Input
        cmdCutReshapeOutput.Parameters("@Cut_ReshapeId").SourceColumn = "Cut_ReshapeId"
        cmdCutReshapeOutput.Parameters.Add("@OutputProductId", SqlDbType.Int, 4, "ProductId")
        cmdCutReshapeOutput.Parameters("@OutputProductId").Direction = ParameterDirection.Input
        cmdCutReshapeOutput.Parameters.Add("@OutputQty", SqlDbType.Float, 24, "OutputQty")
        cmdCutReshapeOutput.Parameters("@OutputQty").Direction = ParameterDirection.Input
        cmdCutReshapeOutput.Parameters.Add("@OutputCost", SqlDbType.Float, 24, "OutputCost")
        cmdCutReshapeOutput.Parameters("@OutputCost").Direction = ParameterDirection.Input
        cmdCutReshapeOutput.Parameters.Add("@OutputCostAmount", SqlDbType.Float, 24, "OutputCostAmount")
        cmdCutReshapeOutput.Parameters("@OutputCostAmount").Direction = ParameterDirection.Input
        daCutReshapeOutput.FillSchema(dsCutReshape, SchemaType.Source)
        daTable = dsCutReshape.Tables("Table")
        daTable.TableName = "Cut_ReshapeOutput"
        dsCutReshape.Relations.Add(New DataRelation("OutputParentChild", dsCutReshape.Tables("Cut_Reshape").Columns("Cut_ReshapeId"), dsCutReshape.Tables("Cut_ReshapeOutput").Columns("Cut_ReshapeId")))

        Dim cmdCutReshapeWaste As SqlCommand
        Dim daCutReshapeWaste As SqlDataAdapter
        daCutReshapeWaste = New SqlDataAdapter("Select * from Cut_ReshapeWaste", db.Con)
        daCutReshapeWaste.InsertCommand = New SqlCommand("proc_AddCutReshapeWasteDetail", db.Con)
        cmdCutReshapeWaste = daCutReshapeInput.InsertCommand
        daCutReshapeWaste.SelectCommand.Transaction = db.objTrans
        daCutReshapeWaste.InsertCommand.Transaction = db.objTrans
        cmdCutReshapeWaste.CommandType = CommandType.StoredProcedure
        cmdCutReshapeWaste.Parameters.Add("@Cut_ReshapeId", SqlDbType.Int, 4, "Cut_ReshapeId")
        cmdCutReshapeWaste.Parameters("@Cut_ReshapeId").Direction = ParameterDirection.Input
        cmdCutReshapeWaste.Parameters("@Cut_ReshapeId").SourceColumn = "Cut_ReshapeId"
        cmdCutReshapeWaste.Parameters.Add("@WasteProductId", SqlDbType.Int, 4, "ProductId")
        cmdCutReshapeWaste.Parameters("@WasteProductId").Direction = ParameterDirection.Input
        cmdCutReshapeWaste.Parameters.Add("@WasteQty", SqlDbType.Float, 24, "WasteQty")
        cmdCutReshapeWaste.Parameters("@WasteQty").Direction = ParameterDirection.Input
        daCutReshapeWaste.FillSchema(dsCutReshape, SchemaType.Source)
        daTable = dsCutReshape.Tables("Table")
        daTable.TableName = "Cut_ReshapeWaste"
        dsCutReshape.Relations.Add(New DataRelation("WasteParentChild", dsCutReshape.Tables("Cut_Reshape").Columns("Cut_ReshapeId"), dsCutReshape.Tables("Cut_ReshapeWaste").Columns("Cut_ReshapeId")))

        Dim CutReshapeDataRow As DataRow = dsCutReshape.Tables("Cut_Reshape").NewRow
        txtNo.Text = db.GetNewInvoiceWithTrans("Cut_Reshape", "Cut_ReshapeNo")
        CutReshapeDataRow("Cut_ReshapeNo") = Val(txtNo.Text)
        CutReshapeDataRow("Cut_ReshapeDate") = dtp.Value
        CutReshapeDataRow("Cut_ReshapeType") = cboType.Text
        CutReshapeDataRow("Cut_ReshapeInputQty") = Val(txtInputSumQty.Text)
        CutReshapeDataRow("Cut_ReshapeInputAmount") = Val(txtInputSumAmount.Text)
        CutReshapeDataRow("Cut_ReshapeOutputQty") = Val(txtOutputSumQty.Text)
        CutReshapeDataRow("Cut_ReshapeOutputAmount") = Val(txtOutputSumAmount.Text)
        CutReshapeDataRow("Cut_ReshapeWasteQty") = Val(txtWasteSumQty.Text)
        CutReshapeDataRow("AddBy") = modGlobals.UserId
        CutReshapeDataRow("AddOn") = Now
        dsCutReshape.Tables("Cut_Reshape").Rows.Add(CutReshapeDataRow)


        Dim y As Integer = 0
        For y = 0 To dgvInput.Rows.Count - 1
            If dgvInput.Rows(y).Cells(1).Value <> Nothing Then
                Dim InputDetailDataRow As DataRow = dsCutReshape.Tables("Cut_ReshapeInput").NewRow
                InputDetailDataRow("ProductId") = dgvInput.Rows(y).Cells(8).Value
                InputDetailDataRow("InputPcs") = dgvInput.Rows(y).Cells(2).Value
                InputDetailDataRow("InputQty") = dgvInput.Rows(y).Cells(3).Value
                InputDetailDataRow("InputCost") = dgvInput.Rows(y).Cells(4).Value
                InputDetailDataRow("InputCostAmount") = dgvInput.Rows(y).Cells(5).Value
                InputDetailDataRow("InputStkId") = dgvInput.Rows(y).Cells(7).Value
                InputDetailDataRow.SetParentRow(CutReshapeDataRow)
                dsCutReshape.Tables("Cut_ReshapeInput").Rows.Add(InputDetailDataRow)
            End If
        Next

        For y = 0 To dgvOutput.Rows.Count - 1
            If dgvOutput.Rows(y).Cells(1).Value <> Nothing Then
                Dim OutputDetailDataRow As DataRow = dsCutReshape.Tables("Cut_ReshapeOutput").NewRow
                OutputDetailDataRow("ProductId") = dgvOutput.Rows(y).Cells(5).Value
                OutputDetailDataRow("OutputQty") = dgvOutput.Rows(y).Cells(2).Value
                OutputDetailDataRow("OutputCost") = dgvOutput.Rows(y).Cells(3).Value
                OutputDetailDataRow("OutputCostAmount") = dgvOutput.Rows(y).Cells(4).Value
                OutputDetailDataRow.SetParentRow(CutReshapeDataRow)
                dsCutReshape.Tables("Cut_ReshapeOutput").Rows.Add(OutputDetailDataRow)
            End If
        Next

        For y = 0 To dgvWaste.Rows.Count - 1
            If dgvWaste.Rows(y).Cells(1).Value <> Nothing Then
                Dim WasteDetailDataRow As DataRow = dsCutReshape.Tables("Cut_ReshapeWaste").NewRow
                WasteDetailDataRow("ProductId") = dgvWaste.Rows(y).Cells(3).Value
                WasteDetailDataRow("WasteQty") = dgvWaste.Rows(y).Cells(2).Value
                WasteDetailDataRow.SetParentRow(CutReshapeDataRow)
                dsCutReshape.Tables("Cut_ReshapeWaste").Rows.Add(WasteDetailDataRow)
            End If
        Next

        daCutReshape.Update(dsCutReshape, "Cut_Reshape")

        daCutReshapeInput.Update(dsCutReshape, "Cut_ReshapeInput")
        daCutReshapeOutput.Update(dsCutReshape, "Cut_ReshapeOutput")
        daCutReshapeWaste.Update(dsCutReshape, "Cut_ReshapeWaste")


        db.objTrans.Commit()
        db.DisConnectDB()
    Catch ex As Exception
        db.objTrans.Rollback()
        db.DisConnectDB()
        MsgBox(ex.Message)
    End Try

End Sub

Posted 6-Nov-15 11:27am

SHERAZ SHIEKH

Updated 6-Nov-15 13:22pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 6-Nov-15 19:23pm

All I can recommend is to not use DataAdapters; they're not worth the trouble.

1 solution

Add a Solution

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

Suvendu Shekhar Giri · Answer 1 · 2015-11-06T18:22:00

Try re-initializing the command object just before using it again.

VB

cmd = New SqlCommand()
Dim daCutReshapeInput As SqlDataAdapter
        daCutReshapeInput = New SqlDataAdapter("Select * from Cut_ReshapeInput", db.Con)
        daCutReshapeInput.InsertCommand = New SqlCommand("proc_AddCutReshapeInputDetail", db.Con)

Hope, it helps :)

inserting relational data in one master and three childs using dataset and dataadapter

1 solution

Solution 1

Add your solution here

Preview 0

inserting relational data in one master and three childs using dataset and dataadapter

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us