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
– Waqar Janjua, Commented Aug 22, 2012 at 12:26

Waqar Janjua · Accepted Answer · 2012-08-22 12:27:58Z

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, 2012 at 12:27

Waqar Janjua

6,1432 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

C.F. Over a year ago

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. Over a year ago

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??

Waqar Janjua Over a year ago

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.

Huzaifa Over a year ago

@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 stackoverflow.com/questions/16716210/… question?

Misbit · Accepted Answer · 2012-08-22 12:34:46Z

1

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;

answered Aug 22, 2012 at 12:34

Misbit

3472 silver badges21 bronze badges

Collectives™ on Stack Overflow

ASP.NET SelectParameter / Control Parameter usage

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related