Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Procedure or function VendorCoverageInsert has too many arguments specified.

Can anyone tell me why I am getting this error?  Am I not referencing the checkbox properly?  I would like the user to select as many zip codes from the gridview, click the submit button and append all selected records to the table.  Thanks.

Code behind called when user clicks the button
Protected Sub btnAddSelectedCoverage_Click(sender As Object, e As EventArgs) Handles btnAddSelectedCoverage.Click
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.VendorCoverageInsert"
            For Each gvRow As GridViewRow In gdvZipList.Rows
                Dim chkBox As CheckBox = CType(gvRow.FindControl("RowLevelCheckBox"), CheckBox)
                If chkBox.Checked Then
                    cmd.Parameters.Add("@VendorId", Data.SqlDbType.VarChar).Value = Session("VendorId").ToString
                    cmd.Parameters.Add("@UserId", Data.SqlDbType.VarChar).Value = Session("VendorId").ToString
                    cmd.Parameters.Add("@Coverage", Data.SqlDbType.Char).Value = chkBox.ToString
                End If
            Next
            Dim dt As New System.Data.DataTable
            cnn.Open()
            cmd.ExecuteScalar()
            Dim sRedirect As String = "VendorAddEditCoverage.aspx?VID=" + Session("VendorId").ToString
            Response.Redirect(sRedirect)
        Finally
            cnn.Close()
        End Try
    End Sub

Open in new window


Stored Procedure called by the code behind
ALTER PROCEDURE dbo.VendorCoverageInsert
	(
	@UserId VarChar(50),
	@VendorId Varchar(50),
	@Coverage char(5)
	)
AS


BEGIN
	INSERT INTO VendorCoverageArea
	                         (VendorId, ZipCode, CreatedUser, ModifiedUser)
	VALUES        (@VendorId,@Coverage,@UserId,@UserId)
	               
	END

Open in new window


Gridview
<asp:GridView ID="gdvZipList" runat="server" AutoGenerateColumns="False" 
                            CssClass="mGrid" Width="257px">
                            <Columns>
                                <asp:TemplateField HeaderText="Select">
                               
            <EditItemTemplate>
                                        <asp:CheckBox ID="RowLevelCheckBox" runat="server" Checked='<%# Bind("ZipCode") %>' />
                                    </EditItemTemplate>
                                    <ItemTemplate>
                                        <asp:CheckBox ID="RowLevelCheckBox" runat="server" Checked='<%# Bind("ZipCode") %>' 
                                            Enabled="false" />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:BoundField DataField="ZipCode" HeaderText="Zip Code" />
                            </Columns>
                             
                        </asp:GridView>

Open in new window

0
rcowen00
Asked:
rcowen00
  • 2
  • 2
1 Solution
 
BuggyCoderCommented:
Change it like this:-
fire the command from within your for loop:-

Protected Sub btnAddSelectedCoverage_Click(sender As Object, e As EventArgs) Handles btnAddSelectedCoverage.Click
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.VendorCoverageInsert"
            For Each gvRow As GridViewRow In gdvZipList.Rows
                Dim chkBox As CheckBox = CType(gvRow.FindControl("RowLevelCheckBox"), CheckBox)
                If chkBox.Checked Then
                    cmd.Parameters.Add("@VendorId", Data.SqlDbType.VarChar).Value = Session("VendorId").ToString
                    cmd.Parameters.Add("@UserId", Data.SqlDbType.VarChar).Value = Session("VendorId").ToString
                    cmd.Parameters.Add("@Coverage", Data.SqlDbType.Char).Value = chkBox.ToString

                    cnn.Open()
                    cmd.ExecuteScalar()
                End If
            Next
            Dim dt As New System.Data.DataTable
            Dim sRedirect As String = "VendorAddEditCoverage.aspx?VID=" + Session("VendorId").ToString
            Response.Redirect(sRedirect)
        Finally
            cnn.Close()
        End Try
    End Sub

Open in new window

0
 
rcowen00Author Commented:
I'm getting "The connection was not closed. The connection's current state is open."
0
 
BuggyCoderCommented:
Try
                        For Each gvRow As GridViewRow In gdvZipList.Rows
                Dim chkBox As CheckBox = CType(gvRow.FindControl("RowLevelCheckBox"), CheckBox)
                If chkBox.Checked Then
                   Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.VendorCoverageInsert"

                    cmd.Parameters.Add("@VendorId", Data.SqlDbType.VarChar).Value = Session("VendorId").ToString
                    cmd.Parameters.Add("@UserId", Data.SqlDbType.VarChar).Value = Session("VendorId").ToString
                    cmd.Parameters.Add("@Coverage", Data.SqlDbType.Char).Value = chkBox.ToString

                    cnn.Open()
                    cmd.ExecuteScalar()
                    cnn.Close()
                End If
            Next

Open in new window

0
 
rcowen00Author Commented:
It is working but it is inserting "Syste" for every zipcode that is selected.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now