Results 1 to 9 of 9

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

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Resolved [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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error..too many arguments in stored proc

    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]).
    Last edited by si_the_geek; Sep 20th, 2016 at 10:27 AM.

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Re: Error..too many arguments in stored proc

    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

  4. #4

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Re: Error..too many arguments in stored proc

    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

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Error..too many arguments in stored proc

    Sigh....

    Quote Originally Posted by si_the_geek View Post
    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 View Post
    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Re: Error..too many arguments in stored proc

    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..

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error..too many arguments in stored proc

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

    Quote Originally Posted by dlhall View Post
    all values are either int or smallint except for intcounty which is nvarchar
    Oops, I picked the wrong one for my example

  8. #8

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Re: Error..too many arguments in stored proc

    That did it..thanks so much

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error..too many arguments in stored proc

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width