locked
Argument Not Specified For Parameter, too many arguments RRS feed

  • Question

  • User-359936451 posted

    I'm working with visual studio 2008 and a sql server express database. I've written some VB code behind and a stored procedure to retrieve data from the DB with multiple selections from a Listbox. I'm getting the following error which appears to be a problem passing 'myString' the list of selected items from the listbox, to the SP. The process works the first time I execute, but when I go back to the list box and select additional records, I get this error...

    Procedure or function usp_itemReport has too many arguments specified.

     

    Code Behind:

            Dim numItems As Integer
            Dim numSelected As Array
    
            Dim myString As String
            myString = ""
    
            numSelected = ListBox1.GetSelectedIndices()    ' Index of items selected in box
            numItems = ListBox1.Items.Count                      ' Number of items in List box
    
            Dim li As ListItem
    
            For Each li In ListBox1.Items
                If li.Selected = True Then
                    If myString = "" Then
                        myString = li.Value
                    Else
                        myString = myString & ", " & li.Value
                    End If
                End If
            Next
     
            ' Build connection to the DB and query for the records.
    
            itemDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("garrgrpConnectionString").ToString()
    
            itemDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
            itemDataSource.SelectCommand = "usp_itemReport"                                     ' Call stored procedure
    
            'Establishes value of Primary key list sent to STORED PROCEDURE 
            itemDataSource.SelectParameters.Add("keyIds", myString)
    
    
            GridView2.DataSource = itemDataSource
    
            GridView2.DataBind()

     


    Stored Proc:

    ALTER PROCEDURE usp_itemReport
    
    
    @keyIds varchar(300)
    
    
    AS  
    SET NOCOUNT ON;   
    		  
    DECLARE @sql varchar(max);   
    		  
    SET @sql = 'SELECT * FROM item AS s WHERE keyID IN ('+ @keyIds +')';    
    		  
    EXECUTE (@sql);   
    		  
    RETURN


     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Thursday, November 4, 2010 9:13 AM

Answers

  • User197322208 posted

    itemDataSource.SelectParameters.Clear

  • 'Establishes value of Primary key list sent to STORED PROCEDURE   
  • itemDataSource.SelectParameters.Add("keyIds", myString)  
  • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, November 4, 2010 9:38 AM

All replies

  • User197322208 posted

    itemDataSource.SelectParameters.Clear

  • 'Establishes value of Primary key list sent to STORED PROCEDURE   
  • itemDataSource.SelectParameters.Add("keyIds", myString)  
  • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, November 4, 2010 9:38 AM
  • User-359936451 posted

    THANK YOU, THANK YOU!!!! 

    Thursday, November 4, 2010 9:43 AM
  • User-359936451 posted

    I have added  colummn names by binding to the fields but the orginal column remains in the gridview. Is there a way to bind to the column so that I do not get duplicate columns returned in the gridview?

    Here is a snippet of the code where I created a Bound item from the Gridview, I thought it would use the columns returned but it actually created a second column, returning the results twice.

                                    <Columns>
                                        <asp:BoundField DataField="itemStreetDate" HeaderText="Street Date" 
                                            SortExpression="itemStreetDate" DataFormatString="{0:d}" />
                                        <asp:BoundField DataField="itemVendorName" HeaderText="Ven Name" 
                                            SortExpression="itemVendorName" />
                                        <asp:BoundField DataField="itemTitle" HeaderText="Title" 
                                            SortExpression="itemTitle" />
                                        <asp:BoundField DataField="itemUPCID" HeaderText="UPC" 
                                            SortExpression="itemUPCID" />
                                    </Columns>
    


     

     

     


     

     

     

     

     

     

     

     

    Tuesday, November 30, 2010 12:42 PM
  • User-359936451 posted

    I found the answer, it is a property setting of the GRidview....

    <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"

    Tuesday, November 30, 2010 12:48 PM
  • User-359936451 posted

    Okay, I now have another issue that doesn't appear to be as easy to solve.

    It appears that since I bound the gridview in code, the wizard will not create the java for sorting on column headings. This seems to be more difficult then expected to code this manually, there must be a better way to get the column sorting function set for this process.

    I did already include the setting...

    AllowSorting="True"

    For the Gridview, this only provides the underline under the column names, the page throws and error whenI click on the column to sort on it...

     

    Server Error in '/' Application.

    The GridView 'GridView2' fired event Sorting which wasn't handled.

    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.Web.HttpException: The GridView 'GridView2' fired event Sorting which wasn't handled.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    Stack Trace:

    [HttpException (0x80004005): The GridView 'GridView2' fired event Sorting which wasn't handled.]
       System.Web.UI.WebControls.GridView.OnSorting(GridViewSortEventArgs e) +1321246
       System.Web.UI.WebControls.GridView.HandleSort(String sortExpression, SortDirection sortDirection) +78
       System.Web.UI.WebControls.GridView.HandleSort(String sortExpression) +75
       System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +531
       System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +207
       System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
    

     

     As another note, my SqlDataSource is coded inline in the asp.net page....

    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
        <asp:SqlDataSource ID="itemDataSource" runat="server" ></asp:SqlDataSource>

     


    Any suggestions?

     Thank you!!

    Tuesday, November 30, 2010 1:10 PM