sql datasource:
<asp:SqlDataSource ID="SqlDataSourceGrid" runat="server" ConnectionString="<%$ ConnectionStrings:EnergiaConnectionString %>"
CancelSelectOnNullParameter="False"
SelectCommand="SELECT [id], [customer_email], [customer_number], [date] FROM [tbl_submit_records] WHERE
( ([date] >= @date) AND ([date] <= @date2 )) ORDER BY [date] DESC"
onselecting="SqlDataSourceGrid_Selecting">
<SelectParameters>
<asp:Parameter Name="date" Type="DateTime" />
<asp:Parameter Name="date2" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
change the selectCommand or parameter manually in the datasource.selecting event. (happen before the datasource select data)
protected void SqlDataSourceGrid_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if (RadDatePickerFrom.SelectedDate != null)
{
e.Command.Parameters["@date"].Value = RadDatePickerFrom.SelectedDate.Value.Date.ToString();
// SqlDataSourceGrid.SelectParameters["date"].DefaultValue = RadDatePickerFrom.SelectedDate.Value.Date.ToString();
}
else
{
e.Command.Parameters["@date"].Value = DateTime.Now.Date.AddMonths(-1).ToString();
//SqlDataSourceGrid.SelectParameters["date"].DefaultValue = DateTime.Now.AddMonths(-1).ToString();
}
if (RadDatePickerTo.SelectedDate != null)
{
e.Command.Parameters["@date2"].Value = RadDatePickerTo.SelectedDate.Value.Date.AddDays(1).AddSeconds(-1).ToString();
//SqlDataSourceGrid.SelectParameters["date2"].DefaultValue = RadDatePickerTo.SelectedDate.Value.Date.AddDays(1).AddSeconds(-1).ToString();
}
else
{
e.Command.Parameters["@date2"].Value = DateTime.Now.Date.AddDays(1).AddSeconds(-1).ToString();
}
}
rebind datasource (submit button click), datasource re select, fire datasource.selecting event etc..
GridView1.Rebind();
/// second way : create property on the page. pass the page property to the parameter.
// create the property based on the select value.
public DateTime FilterDateTo
{
get
{
if (dtmTo.SelectedDate.HasValue)
return
dtmTo.SelectedDate.Value.AddDays(1).AddSeconds(-1);
else
return SystemSettingsManager.GetNow().Date.AddDays(1).AddSeconds(-1);
}
}
// controlID= "--Page"
<data:CustomerBalanceDataSource runat="server" ID="CustomerBalanceDS" SelectMethod="GetByDateToAndStatusAndCurrencyAndAccountType" >
<Parameters>
<asp:Parameter Name="dateFrom" />
<asp:ControlParameter Name="dateTo" ControlID="__Page" PropertyName="FilterDateTo" />
<asp:ControlParameter Name="isDebtor" ControlID="ddl_State" />
<asp:ControlParameter Name="currencyCode" ControlID="ddlCurrency" />
<asp:ControlParameter Name="accountType" ControlID="ddlAccountType" />
</Parameters>
</data:CustomerBalanceDataSource>