We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Populating a Gridview from a Stored Procedure with Parameters

Dovberman
Dovberman asked
on
Medium Priority
3,322 Views
Last Modified: 2012-05-06
Error:
Procedure or function 'usp_getPriceIncreases' expects parameter '@Increases',
which was not supplied.

I am trying to populate a GridView from a DataSource control that specifies a stored procedure.

I could find a reference for declaring the SelectParameters.
Perhaps the QueryStringParameter is not the correct property to use.

Any help would be appreciated.
Procedure or function 'usp_getPriceIncreases' expects parameter '@Increases', 
which was not supplied.
 
<asp:SqlDataSource ID="dscExceptions1" runat="server" 
         ConnectionString="Data Source=ADMIN-PC;Initial Catalog=StockSelectSQL;
         Integrated Security=True" 
         ProviderName="System.Data.SqlClient" 
         SelectCommand="usp_getPriceIncreases" />
            <SelectParameters>
             <asp:QueryStringParameter DefaultValue="15" Name="Increases" 
                 QueryStringField="@Increases" Type="Int16" />
             <asp:QueryStringParameter DefaultValue="2009-01-05" Name="FromDate" 
                 QueryStringField="@FromDate" Type="DateTime" />
             <asp:QueryStringParameter DefaultValue="2009-02-05" Name="ToDate" 
                 QueryStringField="@ToDate" Type="DateTime" />
             <asp:QueryStringParameter DefaultValue="3" Name="MarketID" 
                 QueryStringField="@MarketID" Type="Int16" />
         </SelectParameters>
</asp:SqlDataSource>
 
<asp:GridView ID="GridView1" runat="server" 
    <asp:GridView ID="GridView1" runat="server" 
         AutoPostBack="True" Height="20px" 
         Width="496px" Caption="Selected Stocks" 
         BorderColor="Black" BorderStyle="Solid" BorderWidth="1px" 
         BackColor="Azure" AlternatingRowStyle-BackColor="#FFFF66" 
         AlternatingRowStyle-BorderStyle="Solid" Font-Names="Arial" 
         Font-Size="Smaller" AutoGenerateColumns="False" 
         DataKeyNames="SymbolID" DataSourceID="dscExceptions1">
         <Columns>
             <asp:BoundField DataField="SymbolID" HeaderText="SymbolID" 
                 InsertVisible="False" ReadOnly="True" SortExpression="SymbolID" 
                 Visible="False" />
             <asp:BoundField DataField="SymbolName" HeaderText="Ticker Name" 
                 SortExpression="SymbolName" />
             <asp:BoundField DataField="SecName" HeaderText="Issue Name" 
                 SortExpression="SecName" />
             <asp:BoundField DataField="Increases" HeaderText="Increases" ReadOnly="True" 
                 SortExpression="Increases" />
         </Columns>
         <HeaderStyle BackColor="Blue" Font-Bold="True" ForeColor="White" />
         <AlternatingRowStyle BackColor="#FFFF66" BorderStyle="Solid">
         </AlternatingRowStyle>
 </asp:GridView>  
 
--------
Stored Procedure
 
ALTER PROCEDURE [dbo].[usp_getPriceIncreases] 
	-- Add the parameters for the stored procedure here
	@Increases integer,
	@FromDate datetime,
	@ToDate datetime,
	@MarketID int  
AS
 
/* 
exec usp_getPriceIncreases
@Increases = 15,
@FromDate = '2009-01-05',
@ToDate = '2009-02-04',
@MarketID = 3 
*/
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT SYM.SymbolID,SYM.SymbolName,SYM.SecName  
	, Count(*) AS Increases  
	FROM StockHist AS HIST INNER JOIN
    Symbol AS SYM ON HIST.SymbolID = SYM.SymbolID 
	WHERE 
	SYM.MarketID = @MarketID  
	AND HIST.ClosePrice >=ClosePricePrev 
	AND HIST.QuoteDate >= @FromDate 
	AND HIST.QuoteDate <= @ToDate 
	GROUP BY SYM.SymbolID,SYM.SymbolName,SYM.SecName  
	Having count(*) > @Increases  
	ORDER BY Increases DESC 
END

Open in new window

Comment
Watch Question

David RobitailleAnalyst Programmer

Commented:
Query parameter are the one passed in the querry sring (ex: http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/viewOpenZoneQuestions.jsp?taid=865 , taid is the parameter, 865 the value), it`s not the one you need.
Here hoe to pass parameter to a SqlDataSource
http://www.asp.net/learn/data-access/tutorial-48-vb.aspx

Author

Commented:
I tried changing the SelectParameters tag and got the same error.

<asp:SqlDataSource ID="dscExceptions1" runat="server"
         ConnectionString="Data Source=ADMIN-PC;Initial Catalog=StockSelectSQL;
         Integrated Security=True"
         ProviderName="System.Data.SqlClient"
         SelectCommand="usp_getPriceIncreases" />
         
         <SelectParameters>
            <asp:Parameter Name="Increases" Type = "Int16" />
            <asp:Parameter Name="FromDate" Type = "DateTime" />
            <asp:Parameter Name="ToDate" Type = "DateTime" />
            <asp:Parameter Name="MarketID" Type = "Int16" DefaultValue="0" />
        </SelectParameters>

      </asp:SqlDataSource>
David RobitailleAnalyst Programmer

Commented:
try to set a default value to your parameters
<asp:Parameter Name="Increases" Type = "Int16" DefaultValue="1" />

Author

Commented:
I found my mistake:

<asp:SqlDataSource ID="dscExceptions1" runat="server"
         ConnectionString="<%$ ConnectionStrings:StockSelectSQLConnectionString %>"
         SelectCommand="usp_getPriceIncreases"
         // I had forgotten to enter the SelectCommandtype
         SelectCommandType="StoredProcedure">  
         <SelectParameters>
            <asp:Parameter Name="Increases" Type = "Int16" DefaultValue="15"/>
            <asp:Parameter Name="FromDate" Type = "DateTime" DefaultValue="2009-01-02" />
            <asp:Parameter Name="ToDate" Type = "DateTime" DefaultValue="2009-02-05" />
            <asp:Parameter Name="MarketID" Type = "Int16" DefaultValue="3" />
         </SelectParameters>

</asp:SqlDataSource>

How do I get the Gridview to recognize the entered parameters?
All I get is the defaut selection>
Analyst Programmer
Commented:
well, from where the parameters come from?
If it<s from control, you could use asp:ControlParameter instead of Parameter; there is examples in the link i send.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I guess there is no choice except to set the parameters as controls.
This will work.

Thanks,
David RobitailleAnalyst Programmer

Commented:
You could also set their value programatically. in fact you set their "default value" but the result are the same.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.