[RESOLVED] Error..too many arguments in stored proc

So, I am trying to save data from a gridview control to an MSSQL database using a stored proc that should loop through the gridview and save all rows. It appears if there is only 1 row in the gridview, all is fine, but multiple rows generate the error. I am seeing 11 arguments in the stored proc and 11 arguments in the gridview save routine. Can anyone see what's up here?
VB code:

Code:

---

    Protected Sub Save2()
        Dim cmd As New SqlCommand
        Using conn As New SqlConnection("Data Source=server;Initial Catalog=database;User ID=user;Password=password")
            cmd.CommandText = "savetons"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = conn
            conn.Open()
            For Each row As GridViewRow In GridView2.Rows
                For i As Integer = 0 To GridView2.Rows.Count - 1
                    cmd.Parameters.AddWithValue("@intcounty", GridView2.Rows(i).Cells(1).Text)
                    cmd.Parameters.AddWithValue("@uftrs", GridView2.Rows(i).Cells(2).Text)
                    cmd.Parameters.AddWithValue("@dblton", GridView2.Rows(i).Cells(4).Text)
                    cmd.Parameters.AddWithValue("@intuse", GridView2.Rows(i).Cells(5).Text)
                    cmd.Parameters.AddWithValue("@dbln", GridView2.Rows(i).Cells(6).Text)
                    cmd.Parameters.AddWithValue("@dblp", GridView2.Rows(i).Cells(7).Text)
                    cmd.Parameters.AddWithValue("@dblk", GridView2.Rows(i).Cells(8).Text)
                    If GridView2.Rows(i).Cells(3).Text = "Bag" Then
                        cmd.Parameters.AddWithValue("@inttontype", "1")
                    ElseIf GridView2.Rows(i).Cells(3).Text = "Bulk" Then
                        cmd.Parameters.AddWithValue("@inttontype", "2")
                    ElseIf GridView2.Rows(i).Cells(3).Text = "Liq" Then
                        cmd.Parameters.AddWithValue("@inttontype", "3")
                    End If
                    cmd.Parameters.AddWithValue("@lnglicno", licno.Text)
                    cmd.Parameters.AddWithValue("@intqtr", DDLQtr.SelectedValue)
                    cmd.Parameters.AddWithValue("@intyear", DDLYr.SelectedValue)
                    cmd.ExecuteNonQuery()

                Next
            Next
            conn.Close()
        End Using
        If Chk1.Checked Then
            SavePO()
        Else
        End If
    End Sub

---

And here's the Stored proc:

Code:

---

ALTER PROCEDURE [dbo].[savetons]
        -- Add the parameters for the stored procedure here
(@lnglicno as int,
@intqtr as int,
@intyear as int,
@intcounty as nvarchar(50),
@uftrs as smallint,
@dblton as int,
@inttontype as smallint,
@intuse as smallint,
@dbln as int,
@dblp as int,
@dblK as 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 temptonnage
(lnglicno, intqtr, intyear, intcounty, uftrs, dblton, inttontype, intuse, dbln, dblp, dblk)
Values
(@lnglicno, @intqtr, @intyear, @intcounty, @uftrs, @dblton, @inttontype, @intuse, @dbln, @dblp, @dblk)
END

---

A likely cause of problems is data types.

When you use .AddWithValue the data type is guessed based on the data type of the value you give, and as you are passing String based values in almost every case, a VarChar based data type is being assumed - yet most of your fields are actually Int based.

What you should be doing is either switch from .AddWithValue to .Add and specify the data type information, or stay with .AddWithValue but convert the values appropriately (which you should also be doing if you use .Add), eg:

Code:

---

cmd.Parameters.AddWithValue("@intcounty", CInt(GridView2.Rows(i).Cells(1).Text))

---

However, the important thing is that you keep on adding parameters inside the loop, and never clear them... so on the second iteration you will have two copies of @intcounty (and all the other parameters).

Either clear the parameters at the top of the loop, or add the parameters before the loop and only set the values inside the loop.


edit: why have you got both of these loops?

Code:

---

            For Each row As GridViewRow In GridView2.Rows
                For i As Integer = 0 To GridView2.Rows.Count - 1

---

They are almost the same as each other, so you should only be using one (what will happen with both is that you will add each row multiple times [one copy of each row per row in the grid]).

OK, thanks for that, but after making the changes below, no difference, same error:
Note - all values are either int or smallint except for intcounty which is nvarchar

Code:

---

Protected Sub Save2()
        Dim cmd As New SqlCommand
        Using conn As New SqlConnection("Data Source=server;Initial Catalog=database;User ID=user;Password=password")
            cmd.CommandText = "savetons"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = conn
            conn.Open()
            cmd.Parameters.Clear()
            For Each row As GridViewRow In GridView2.Rows
                For i As Integer = 0 To GridView2.Rows.Count - 1
                    cmd.Parameters.AddWithValue("@intcounty", GridView2.Rows(i).Cells(1).Text)
                    cmd.Parameters.AddWithValue("@uftrs", CInt(GridView2.Rows(i).Cells(2).Text))
                    cmd.Parameters.AddWithValue("@dblton", CInt(GridView2.Rows(i).Cells(4).Text))
                    cmd.Parameters.AddWithValue("@intuse", CInt(GridView2.Rows(i).Cells(5).Text))
                    cmd.Parameters.AddWithValue("@dbln", CInt(GridView2.Rows(i).Cells(6).Text))
                    cmd.Parameters.AddWithValue("@dblp", CInt(GridView2.Rows(i).Cells(7).Text))
                    cmd.Parameters.AddWithValue("@dblk", CInt(GridView2.Rows(i).Cells(8).Text))
                    If GridView2.Rows(i).Cells(3).Text = "Bag" Then
                        cmd.Parameters.AddWithValue("@inttontype", "1")
                    ElseIf GridView2.Rows(i).Cells(3).Text = "Bulk" Then
                        cmd.Parameters.AddWithValue("@inttontype", "2")
                    ElseIf GridView2.Rows(i).Cells(3).Text = "Liq" Then
                        cmd.Parameters.AddWithValue("@inttontype", "3")
                    End If
                    cmd.Parameters.AddWithValue("@lnglicno", CInt(licno.Text))
                    cmd.Parameters.AddWithValue("@intqtr", CInt(DDLQtr.SelectedValue))
                    cmd.Parameters.AddWithValue("@intyear", CInt(DDLYr.SelectedValue))
                    cmd.ExecuteNonQuery()

                Next
            Next
            conn.Close()
        End Using
        If Chk1.Checked Then
            SavePO()
        Else
        End If
    End Sub

---

Just saw your edit, and made this adjustment as well, but still, same error

Code:

---

Protected Sub Save2()
        Dim cmd As New SqlCommand
        Using conn As New SqlConnection("Data Source=server;Initial Catalog=database;User ID=user;Password=password")
            cmd.CommandText = "savetons"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = conn
            conn.Open()
            cmd.Parameters.Clear()
            ' For Each row As GridViewRow In GridView2.Rows
                For i As Integer = 0 To GridView2.Rows.Count - 1
                    cmd.Parameters.AddWithValue("@intcounty", GridView2.Rows(i).Cells(1).Text)
                    cmd.Parameters.AddWithValue("@uftrs", CInt(GridView2.Rows(i).Cells(2).Text))
                    cmd.Parameters.AddWithValue("@dblton", CInt(GridView2.Rows(i).Cells(4).Text))
                    cmd.Parameters.AddWithValue("@intuse", CInt(GridView2.Rows(i).Cells(5).Text))
                    cmd.Parameters.AddWithValue("@dbln", CInt(GridView2.Rows(i).Cells(6).Text))
                    cmd.Parameters.AddWithValue("@dblp", CInt(GridView2.Rows(i).Cells(7).Text))
                    cmd.Parameters.AddWithValue("@dblk", CInt(GridView2.Rows(i).Cells(8).Text))
                    If GridView2.Rows(i).Cells(3).Text = "Bag" Then
                        cmd.Parameters.AddWithValue("@inttontype", "1")
                    ElseIf GridView2.Rows(i).Cells(3).Text = "Bulk" Then
                        cmd.Parameters.AddWithValue("@inttontype", "2")
                    ElseIf GridView2.Rows(i).Cells(3).Text = "Liq" Then
                        cmd.Parameters.AddWithValue("@inttontype", "3")
                    End If
                    cmd.Parameters.AddWithValue("@lnglicno", CInt(licno.Text))
                    cmd.Parameters.AddWithValue("@intqtr", CInt(DDLQtr.SelectedValue))
                    cmd.Parameters.AddWithValue("@intyear", CInt(DDLYr.SelectedValue))
                    cmd.ExecuteNonQuery()

                Next
            Next
            conn.Close()
        End Using
        If Chk1.Checked Then
            SavePO()
        Else
        End If
    End Sub

---

Sigh....

Quote:

---

Originally Posted by si_the_geek

However, the important thing is that you keep on adding parameters inside the loop, and never clear them... so on the second iteration you will have two copies of @intcounty (and all the other parameters).

Either clear the parameters at the top of the loop, or add the parameters before the loop and only set the values inside the loop.

---

Apparently you missed the key points before the edit...

Quote:

---

Originally Posted by si_the_geek

However, the important thing is that you keep on adding parameters inside the loop, and never clear them... so on the second iteration you will have two copies of @intcounty (and all the other parameters).

Either clear the parameters at the top of the loop, or add the parameters before the loop and only set the values inside the loop.

---

-tg

I guess I did..I'm sure I simply do not understand. I did place a cmd.parameters.clear() command before the loop, so can you be more specific? Sorry to be such a noobie..

It should be inside the loop, not before it... I wasn't particularly clear on that.

Quote:

---

Originally Posted by dlhall

all values are either int or smallint except for intcounty which is nvarchar

---

Oops, I picked the wrong one for my example :blush:

That did it..thanks so much

No worries :)


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).