?
Solved

Too many arguments sent to stored procedure

Posted on 2009-04-22
6
Medium Priority
?
374 Views
Last Modified: 2012-05-06
I was wondering if anyone can look at my code and figure out why too many arguments are being sent to the stored procedure on each dropdownlist selection to populate my listbox

The drop down lists and listbox are created dynamically on a button event and placed in a placeholder
addproduct.aspx:
 
<asp:Label runat="server" ID="SubCategoryLabel1" Text="Sub Category 1:" Font-Size="Small"></asp:Label>
 <asp:DropDownList runat="server" ID="SubCategoryDDL1" DataSourceID="SelectSubCategorySQL" DataTextField="SubCatName" DataValueField="SubCatId" AutoPostBack="true"></asp:DropDownList>
 <asp:Button runat="server" ID="AddMoreSubCatButton" Text="Add More" Font-Size="X-Small" />
<br />
<asp:ListBox runat="server" ID="ProductTagsListBox1" Width="125px" SelectionMode="Multiple" DataSourceID="ProductTagsSQL1" DataTextField="TagName" DataValueField="TagId"></asp:ListBox>
<asp:SqlDataSource runat="server" ID="ProductTagsSQL1" ConnectionString="<%$ ConnectionStrings:DevConnectionString %>" SelectCommand="CMS.MgmtGetTagsBySubCat" SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:ControlParameter ControlID="SubCategoryDDL1" Name="SubCatId" PropertyName="SelectedValue" />
  </SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource runat="server" ID="SelectSubCategorySQL" ConnectionString="<%$ ConnectionStrings:DevConnectionString %>" SelectCommand="CMS.MgmtGetSubCategoryNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
<asp:PlaceHolder ID="MoreSubCatControlsPlaceHolder" runat="server" EnableViewState="true"></asp:PlaceHolder>
 
addproduct.aspx.vb:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
        'Re-create all the dynamically created web controls on PostBack
        Dim intSubCatCounter As Integer
 
        If Not IsPostBack Then
            ViewState("SubCatFieldCount") = 1
        ElseIf ViewState("SubCatFieldCount") > 1 Then
            For intSubCatCounter = 2 To ViewState("SubCatFieldCount")
                CreateSubCatControls(intSubCatCounter)
            Next
        End If
 
    End Sub
 
    Protected Sub AddMoreSubCatButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles AddMoreSubCatButton.Click
 
        'Limit the attached sub category controls to 2
        If ViewState("SubCatFieldCount") <= 2 Then
            'Increment sub category controls in viewstate
            ViewState("SubCatFieldCount") += 1
            CreateSubCatControls(ViewState("SubCatFieldCount"))
        End If
 
    End Sub
 
    Protected Sub CreateSubCatControls(ByVal strFieldNum As String)
 
        Dim SubCatDDL As DropDownList = New DropDownList()
        MoreSubCatControlsPlaceHolder.Controls.Add(SubCatDDL)
        SubCatDDL.ID = "SubCategoryDDL" + strFieldNum
        SubCatDDL.DataSource = SelectSubCategorySQL
        SubCatDDL.DataTextField = "SubCatName"
        SubCatDDL.DataValueField = "SubCatId"
        SubCatDDL.DataBind()
        SubCatDDL.AutoPostBack = True
 
        MoreSubCatControlsPlaceHolder.Controls.Add(New LiteralControl("<br />"))
 
        Dim ProductTagsListBox As ListBox = New ListBox()
        MoreSubCatControlsPlaceHolder.Controls.Add(ProductTagsListBox)
        ProductTagsListBox.ID = "ProductTagsListBox" + strFieldNum
        ProductTagsListBox.Width = Unit.Pixel(125)
        ProductTagsListBox.SelectionMode = ListSelectionMode.Multiple
        ProductTagsListBox.DataSourceID = "ProductTagsSQL" + strFieldNum
        ProductTagsListBox.DataTextField = "TagName"
        ProductTagsListBox.DataValueField = "TagId"
 
        Dim PUser As New ControlParameter
        PUser.ControlID = SubCatDDL.ID
        PUser.Name = "SubCatId"
        PUser.PropertyName = "SelectedValue"
        PUser.Type = TypeCode.Int32
 
        Dim SQLDS As New SqlDataSource
        MoreSubCatControlsPlaceHolder.Controls.Add(SQLDS)
        SQLDS.ID = "ProductTagsSQL" + strFieldNum
        SQLDS.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("DevConnectionString").ConnectionString
        SQLDS.SelectCommand = "CMS.MgmtGetTagsBySubCat"
        SQLDS.SelectParameters.Add(PUser)
        SQLDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
 
        ProductTagsListBox.DataBind()
 
 
    End Sub
 
End Class

Open in new window

0
Comment
Question by:miguel_j
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24212386
Hi miguel_j,
>>..why too many arguments are being sent to the stored procedure on each dropdownlist selection to populate my listbox
- Can you post the detailed errors that generated on screen? Lastly, post your stored procedure that returned errors for further investigation regarding the issues as reported. I suspect there might be calling wrong stored procedure or insufficient passed arguments based on declared inside the stored procedure.
0
 

Author Comment

by:miguel_j
ID: 24213581
thank you x_com
apologies for not detailing the error.
the error occurs when i select an item from the dynamically generated dropdownlists...

the dropdownlist control calls the storeproc to populate the dynamically generated listbox with the results..

basically when the page first loads there is a dropdownlist with a matching listbox and a add more button... the button creates the same controls on the fly and are suppose to perform the same fuctions (i.e selecting tags corresponding to the subcat selection)


the following is the stored procedure. very simple. no problems there

ALTER PROCEDURE [CMS].[MgmtGetTagsBySubCat]

      @SubCatId INT

AS

BEGIN

      SELECT   TagId, TagName
      FROM     Management.SubCategoryTags
      WHERE       SubCatId = @SubCatId
      ORDER BY TagName

END


detailed error:
Procedure or function MgmtGetTagsBySubCat has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function MgmtGetTagsBySubCat has too many arguments specified.

Source Error:


Line 81:         SQLDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
Line 82:
Line 83:         ProductTagsListBox.DataBind()
Line 84:
Line 85:
 



0
 
LVL 15

Accepted Solution

by:
NazoUK earned 2000 total points
ID: 24214652
I suspect the parameters of the datasource are being saved in ViewState, therefore when the page is posted back and the dynamic sqldatasource is recreated, it reloads its state (including parameters) from ViewState and you end up adding another copy of the parameter to its parameters collection.

Try setting SQLDS.EnableViewState=false after the line
Dim SQLDS As New SqlDataSource
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24221810
Hi miguel_j
Beside that, I'm also curious if the dynamic created control causes the issue. Try check the existence of created control before bind the source to avoid control recreation and loses the selected value.

Protected Sub CreateSubCatControls(ByVal strFieldNum As String)
        Dim ddl As Control = FindControl("SubCategoryDDL" & strFieldNum) 
 
        If ddl Is Nothing Then 
 
        Dim SubCatDDL As DropDownList = New DropDownList()
        MoreSubCatControlsPlaceHolder.Controls.Add(SubCatDDL)
        SubCatDDL.ID = "SubCategoryDDL" & strFieldNum
        SubCatDDL.DataSource = SelectSubCategorySQL
        SubCatDDL.DataTextField = "SubCatName"
        SubCatDDL.DataValueField = "SubCatId"
        SubCatDDL.DataBind()
        SubCatDDL.AutoPostBack = True
 
        MoreSubCatControlsPlaceHolder.Controls.Add(New LiteralControl("<br />"))
        
        end if
 
        Dim lst As Control = FindControl("ProductTagsListBox" & strFieldNum) 
 
        If lst Is Nothing Then 
 
        Dim ProductTagsListBox As ListBox = New ListBox()
        MoreSubCatControlsPlaceHolder.Controls.Add(ProductTagsListBox)
        ProductTagsListBox.ID = "ProductTagsListBox" & strFieldNum
        ProductTagsListBox.Width = Unit.Pixel(125)
        ProductTagsListBox.SelectionMode = ListSelectionMode.Multiple
        ProductTagsListBox.DataSourceID = "ProductTagsSQL" & strFieldNum
        ProductTagsListBox.DataTextField = "TagName"
        ProductTagsListBox.DataValueField = "TagId"
 
        Dim PUser As New ControlParameter
        PUser.ControlID = SubCatDDL.ID
        PUser.Name = "SubCatId"
        PUser.PropertyName = "SelectedValue"
        PUser.Type = TypeCode.Int32
 
        Dim SQLDS As New SqlDataSource
        MoreSubCatControlsPlaceHolder.Controls.Add(SQLDS)
        SQLDS.ID = "ProductTagsSQL" + strFieldNum
        SQLDS.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("DevConnectionString").ConnectionString
        SQLDS.SelectCommand = "CMS.MgmtGetTagsBySubCat"
        SQLDS.SelectParameters.Add(PUser)
        SQLDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
 
        ProductTagsListBox.DataBind()
        
        end if
 
    End Sub
 

Open in new window

0
 

Author Closing Comment

by:miguel_j
ID: 31573650
Thank you for your solution... this solution has also helped me with other aspects of my project
0
 

Author Comment

by:miguel_j
ID: 24428981
I just realised that after each postback the selected value in the listbox is not retained.
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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

809 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