使用数据集和dataadapter在一个主节点和三个子节点中插入关系数据 [英] inserting relational data in one master and three childs using dataset and dataadapter

问题描述

我是vb.net 2010的新手，并试图在sql server 2008中使用数据集和数据适配器以及存储过程在四个关系表中保存数据，一个主人和三个孩子，但它返回error.if我尝试这两个表它的工作原理一个主人和一个孩子。错误是过程或函数proc_AddCutReshapeInputDetail指定了许多参数我的代码如下

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

/*=================================================================================	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代码

VB.NET CODE

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

推荐答案

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

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

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 :)

Hope, it helps :)

这篇关于使用数据集和dataadapter在一个主节点和三个子节点中插入关系数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！