Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Procedure or function VendorCoverageInsert has too many arguments specified.

Posted on 2012-03-19
4
Medium Priority
?
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

661 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