Solved

Procedure or function VendorCoverageInsert has too many arguments specified.

Posted on 2012-03-19
4
287 Views
Last Modified: 2012-03-19
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
Comment
Question by:rcowen00
  • 2
  • 2
4 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37739305
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
 

Author Comment

by:rcowen00
ID: 37739352
I'm getting "The connection was not closed. The connection's current state is open."
0
 
LVL 20

Accepted Solution

by:
BuggyCoder earned 500 total points
ID: 37739400
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
 

Author Comment

by:rcowen00
ID: 37739449
It is working but it is inserting "Syste" for every zipcode that is selected.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now