?
Solved

Populating a Gridview from a Stored Procedure with Parameters

Posted on 2009-02-16
8
Medium Priority
?
3,292 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

0
Comment
Question by:Dovberman
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:CB_Thirumalai
ID: 23650184
The below link will be of help to you.
http://msdn.microsoft.com/en-us/library/z72eefad(VS.80).aspx
0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 23650195
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
0
 

Author Comment

by:Dovberman
ID: 23651605
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>
0
Independent Software Vendors: 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 18

Expert Comment

by:David Robitaille
ID: 23651784
try to set a default value to your parameters
<asp:Parameter Name="Increases" Type = "Int16" DefaultValue="1" />

0
 

Author Comment

by:Dovberman
ID: 23651982
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>
0
 
LVL 18

Accepted Solution

by:
David Robitaille earned 2000 total points
ID: 23652036
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.
0
 

Author Comment

by:Dovberman
ID: 23652671
I guess there is no choice except to set the parameters as controls.
This will work.

Thanks,
0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 23652693
You could also set their value programatically. in fact you set their "default value" but the result are the same.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

862 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