ASP.NET SelectParameter / Control Parameter usage

Question

newbie to asp.net here.

I am trying to setup a selectparameters and controlparameters based off textbox web controls for date ranges to retrieve data on a asp.net page.

SelectCommand="SELECT SUM(Turnover) AS TotalTurnover, (SUM(Turnover) / (SELECT COUNT(*) FROM (SELECT DISTINCT [Trade Date] FROM TradeSummary WHERE ([Trade Date] BETWEEN @T1 AND @T2)))) AS AverageTO FROM TradeSummary WHERE ([Trade Date] BETWEEN @T1 AND @T2)">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="TradeDate1" DefaultValue="8-10-2012" Name="T1" Type="DateTime" PropertyName="Text" />
                            <asp:ControlParameter ControlID="TradeDate2" DefaultValue="8-11-2012" Name="T2" Type="DateTime" PropertyName="Text" />
                    </SelectParameters>

TradeDate1 and TradeDate2 refer to 2 textbox controls that I setup for date picking.

I am wondering how I can examine the values of @T1 and @T2 so that I can ensure the query being passed to the DB is valid because it seems like if I were to eval("TotalTurnover") then I get a dbnull error.

Any help would be appreciated. Thanks!

you can also pass the control paraamters from your code behind after checking the values. — Waqar Janjua, Aug 22 '12 at 12:26

score 2 · Accepted Answer · answered Aug 22 '12 at 12:27

2

you can also pass the control paraamters from your code behind after checking the values. Add the below code in your Page_Load or any other control event ( e.g button )

// check your textbox values
if( TradeDate1.Text != null )
{
   SqlDataSource1.SelectParameters.Add("@T1",TradeDate1.Text);
}

answered Aug 22 '12 at 12:27

Waqar Janjua

6,113
2
26
36

Thanks perhaps I am missing something very obvious. Is my current markup only specifying there are 2 parameters and that it needs to be explicitly added so that the query would include such values? – C.F. Aug 22 '12 at 12:44
Waqar, this works perfectly. Thanks so much! If you dont mind me go off topic a bit, would the selectcommand run against the DB at all prior to the add method being called in codebehind? I am slightly confused as to when this query runs? Should one set default values to retrieve zero or default dataset prior to ADD being called?? – C.F. Aug 22 '12 at 16:00
If I understand your query clearly. You can do it in any order. first set the select command or add the paramerters. like this ` SqlDataSource1.SelectCommand = "your command"; SqlDataSource1.SelectParameters.Add ...... ` You can add it in any order. If this answer is helpful then remember to mark it as answer. Thanks. – Waqar Janjua Aug 22 '12 at 16:11
@WaqarJanjua I did the same thing. However, for gridview the data is binding before it reaches the button and hence throwing me an error. Can you have a look at this http://stackoverflow.com/questions/16716210/must-declare-the-scalar-variable-fname-error-asp-net question? – Huzaifa May 23 '13 at 15:21

score 1 · Answer 2 · answered Aug 22 '12 at 12:34

I'd check the input values of the textboxes on a click event of a button and if they're all ok go on the databind the datasource. Then on the selecting event of the datasource I'd set the parameters necessary by using datsource.SelectParameters["T1"].DefaultValue = date;

ASP.NET SelectParameter / Control Parameter usage

2 Answers2