-
Sep 20th, 2016, 09:11 AM
#1
Thread Starter
Member
[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
-
Sep 20th, 2016, 10:21 AM
#2
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.
-
Sep 20th, 2016, 10:36 AM
#3
Thread Starter
Member
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
-
Sep 20th, 2016, 10:42 AM
#4
Thread Starter
Member
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
-
Sep 20th, 2016, 10:51 AM
#5
Re: Error..too many arguments in stored proc
Sigh....
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...
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
-
Sep 20th, 2016, 10:57 AM
#6
Thread Starter
Member
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..
-
Sep 20th, 2016, 11:09 AM
#7
Re: Error..too many arguments in stored proc
It should be inside the loop, not before it... I wasn't particularly clear on that.
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
-
Sep 20th, 2016, 11:18 AM
#8
Thread Starter
Member
Re: Error..too many arguments in stored proc
That did it..thanks so much
-
Sep 20th, 2016, 11:28 AM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|