locked
Procedure or function getCategoryAncestorsByProductID has too many arguments specified. RRS feed

  • Question

  • User-1706579310 posted

    I am trying to get the value of the CategoryID so that I can insert that value into the database when an admin wants to update product features.

    I did not write the stored procedure so I have no idea what it is doing, I got an error saying that my listview didn't have a value for @CategoryID so I added it to the stored procedure in the SELECT statement. Not knowing how the stored procedure works, I got the "too many arguments" error.

    Dim featureSql As String = "INSERT INTO Marketing(ProductID, 
    MarketingTypeID, MarketingTitle, MarketingData) 
    VALUES ( @ProductID, 3, 'Feature', @MarketingData);
    UPDATE Product SET ModifyDate = getdate(), 
    ModifyUser = @ModifyUser 
    WHERE ProductID = @ProductID; 
    INSERT INTO Feature (FeatureTitle, CategoryID) 
    VALUES (@FeatureTitle, @CategoryID)"
    
                    Using cn As New SqlConnection
    (System.Configuration.ConfigurationManager.ConnectionStrings
    ("LocalSqlServer").ConnectionString)
                      
                        Using cmd As New SqlCommand(featureSql, cn)
                           
                            cmd.Parameters.Add(New SqlParameter("@FeatureTitle", txtFeature.Text))
                            cmd.Parameters.Add(New SqlParameter("@CategoryID", CategoryID.Value))
                        End Using
                    End Using
    <asp:SqlDataSource ID="dsCategoryAncestors" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProductsConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:ProductsConnectionString.ProviderName %>" 
            SelectCommand="getCategoryAncestorsByProductID" 
            SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:QueryStringParameter Name="ProductID" QueryStringField="id" 
                    Type="Int32" />
            </SelectParameters>
            <SelectParameters>
                <asp:QueryStringParameter Name="CategoryID" QueryStringField="id" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
    -- Insert child category into table first giving it an OrderNumber of 1 -- the higher the number, the higher in the tree
    	INSERT INTO #FamilyTree VALUES (1,@CategoryID,@CategoryName)
    	
    	-- Use this for the OrderNumber. Increment by 1 for each level.
    	DECLARE @OrderNumber int
    	SET		@OrderNumber = 2
    	
    	-- Use this to hold the new CategoryID to add to the familytree table
    	DECLARE @AncestorID INT
    	
    	WHILE (SELECT COUNT(*) FROM Category 
    WHERE CategoryID =
    (SELECT ParentID 
    FROM Category 
    WHERE CategoryID = @CategoryID)) > 0
    	BEGIN
    
    		SELECT @AncestorID = CategoryID, 
    @CategoryName = CategoryName 
    FROM Category WHERE CategoryID = 
    (SELECT ParentID FROM Category 
    WHERE CategoryID = @CategoryID)
    		
    		INSERT INTO #FamilyTree VALUES (@OrderNumber,@AncestorID,@CategoryName)
    		SET @OrderNumber = @OrderNumber + 1
    		SET @CategoryID = @AncestorID --Moves to the next CategoryID to see if it has any parents
    				
    	END
    	
    --Returns Categories in order from Parents to Children
    SELECT CategoryName, CategoryID 
    FROM #FamilyTree 
    ORDER BY OrderNumber DESC
    
    DROP TABLE #FamilyTree
    
    END
    
    Friday, November 11, 2011 2:42 PM

Answers

  • User-1706579310 posted

    I just had to remove the <SelectParameters> of CategoryID and add CategoryID to the Select statement in the stored procedure and it does not throw errors anymore. :) Thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 14, 2011 10:21 AM

All replies

  • User3866881 posted

    Hello grinnellja:)

    In fact I think you can just only use SqlCommand+SqlConnection to deal with your problem just like this:

    Dim featureSql As String = "INSERT INTO Marketing(ProductID, 
    MarketingTypeID, MarketingTitle, MarketingData) 
    VALUES ( @ProductID, 3, 'Feature', @MarketingData);
    UPDATE Product SET ModifyDate = getdate(), 
    ModifyUser = @ModifyUser 
    WHERE ProductID = @ProductID; 
    INSERT INTO Feature (FeatureTitle, CategoryID) 
    VALUES (@FeatureTitle, @CategoryID)"


                   
    Using cn As New SqlConnection
    (System.Configuration.ConfigurationManager.ConnectionStrings
    ("LocalSqlServer").ConnectionString)
                      
                       
    Using cmd As New SqlCommand(featureSql, cn) 
                            cmd
    .Parameters.Add(New SqlParameter("@FeatureTitle", txtFeature.Text))
                            cmd
    .Parameters.Add(New SqlParameter("@CategoryID", CategoryID.Value))
                            cmd.Parameters.Add(New SqlParameter("@ProductID", Your ProductId's Value)) 
                            cmd.Parameters.Add(New SqlParameter("@MarketingData",Your Data Value))
                            cn.Open()
                            cmd.ExecuteNonQuery()
                            cn.Close()
                       
    End Using
                   
    End Using

    Saturday, November 12, 2011 8:04 PM
  • User-1706579310 posted

    I just had to remove the <SelectParameters> of CategoryID and add CategoryID to the Select statement in the stored procedure and it does not throw errors anymore. :) Thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 14, 2011 10:21 AM