0

The procedure for Insert/update.I use datagridview get and display the data.
I get the error message

"Procedure or function Updatedata has too many arguments specified."

// this is my Procedure
ALTER PROCEDURE [dbo].[Updatedata] 

    @item char(30),
    @uom int,@group int,@orqty int,@avgcons int,@reorder int
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS( SELECT * FROM  P_Itemmaster WHERE Item=@item  )

    UPDATE  P_Itemmaster SET Uom=@uom,PGroup=@group,Minorqty=@orqty,avgcons=@avgcons,reorder=@reorder where  item=@item

  ELSE

    INSERT INTO P_Itemmaster (Item,Uom,PGroup,Minorqty,Avgcons,reorder)Values(@item,@uom,@group,@orqty,@avgcons,@reorder)

END

//call the procedure in the program

   Private Sub Updatetable()
        conn = New SqlConnection(frmstlogin.Constr)
        conn.Open()
        cmd = New SqlCommand(cmdstr, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Updatedata"
        Dim MUom As Integer, Mgroup As Integer, Morqty As Integer, Mavgcons As Integer, Mreorder As Integer
        For i = 0 To Dgvitem.Rows.Count - 2
            Mitem = Dgvitem.Item(1, i).Value
            MUom = Dgvitem.Item(2, i).Value
            Mgroup = Dgvitem.Item(3, i).Value
            Morqty = Dgvitem.Item(4, i).Value
            Mavgcons = Dgvitem.Item(5, i).Value
            Mreorder = Dgvitem.Item(6, i).Value
            cmd.Parameters.AddWithValue("@Item", Mitem)
            cmd.Parameters.AddWithValue("@Uom", MUom)
            cmd.Parameters.AddWithValue("@Group", Mgroup)
            cmd.Parameters.AddWithValue("@orqty", Morqty)
            cmd.Parameters.AddWithValue("@Avgcons", Mavgcons)
            cmd.Parameters.AddWithValue("@Reorder", Mreorder)
             cmd.ExecuteNonQuery()
        Next
    End Sub

Edited by deceptikon: Changed to a discussion thread

2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by kkunodziya
0

'It is probably because you are adding your parameters within a for loop. In that case 'only the first line will be updated without an error, i.e when i=0. but when i=1, you 'are going to be having 12 parameters for your cmd, and so on and so on.

'try something like:

Private Sub Updatetable(Mitem Integer, MUom As Integer, Mgroup As Integer, Morqty As Integer, Mavgcons As Integer, Mreorder As Integer)
        conn = New SqlConnection(frmstlogin.Constr)
        conn.Open()
        cmd = New SqlCommand(cmdstr, conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Updatedata"

            cmd.Parameters.AddWithValue("@Item", Mitem)
            cmd.Parameters.AddWithValue("@Uom", MUom)
            cmd.Parameters.AddWithValue("@Group", Mgroup)
            cmd.Parameters.AddWithValue("@orqty", Morqty)
            cmd.Parameters.AddWithValue("@Avgcons", Mavgcons)
            cmd.Parameters.AddWithValue("@Reorder", Mreorder)
            cmd.ExecuteNonQuery()

    End Sub

    'You then call your sub in some event e.g button click like

    For i = 0 To Dgvitem.Rows.Count - 2

    Updatetable(Dgvitem.Item(1, i).Value, Dgvitem.Item(2, i).Value, Dgvitem.Item(3, i).Value, Dgvitem.Item(4, i).Value, Dgvitem.Item(5, i).Value, Dgvitem.Item(6, i).Value)

    Next
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.