I have an ASP.NET web form coded like this:
<asp:TextBox
ClientIDMode="Static"
ID="_txtExitDate"
runat="server"
EnableViewState="true" />
<asp:TextBox
ClientIDMode="Static"
ID="_txtEnterDate"
runat="server"
EnableViewState="true" />
<asp:Button
runat="server"
ClientIDMode="Static"
ID="_btnSearch"
Text="Search"
OnClick="_btnSearch_Click" />
<asp:GridView
ID="_gvRecords"
runat="server"
DataSourceID="_sdsRecords"
DataKeyNames="total_records"
CellPadding="0"
CellSpacing="0"
ShowHeader="true"
ShowFooter="true"
PageSize="20"
SelectedIndex="0"
Width="100%"
BorderStyle="None"
GridLines="Horizontal"
AutoGenerateColumns="false"
AllowSorting="true"
AllowPaging="true"
EmptyDataText="No records."
OnPageIndexChanging="_gvRecords_PageIndexChanging"
OnSorting="_gvRecords_Sorting" >
<SelectedRowStyle CssClass="SelRow" />
<HeaderStyle CssClass="GridHeader" />
<AlternatingRowStyle CssClass="AltRow" BackColor="#F7F5E9" />
<Columns>
<asp:BoundField DataField="region" HeaderText="Region" SortExpression="region" />
<asp:BoundField DataField="total_records" HeaderText="Records" SortExpression="total_records" />
</Columns>
</asp:GridView>
Of course these are only the relevant tags the layout is managed by other code that's not important.
The form include a SQLDataSource defined like this:
<asp:SqlDataSource
runat="server"
ConnectionString="<%$ ConnectionStrings:db %>"
ProviderName="<%$ ConnectionStrings:db.ProviderName %>"
ID="_sdsRecords"
OnSelecting="_sdsRecords_Selecting"
SelectCommand ="
SELECT
COUNT(DISTINCT(records.id)) AS total_records,
tab_cities.city_region AS region
FROM
records
INNER JOIN
tab_A ON records.id_subject = tab_A.id
INNER JOIN
tab_cities ON tab_cities.city_province_code = tab_A.province
WHERE
(records.mode = 'S')
AND (records.status = 'C')
AND (records.delete_date IS NULL)
AND (records.exit_date >= @exit)
AND (records.enter_date <= @enter)
GROUP BY
tab_cities.city_region
ORDER BY
total_records DESC">
<SelectParameters>
<asp:Parameter Direction="Input" DbType="DateTime" Name="exit" />
<asp:Parameter Direction="Input" DbType="DateTime" Name="enter" />
</SelectParameters>
</asp:SqlDataSource>
The codebehind is something like this:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
DateTime date = DateTime.Now;
var firstDayOfMonth = new DateTime(date.Year, date.Month-1, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
_txtExitDate.Text = firstDayOfMonth.ToShortDateString();
_txtEnterDate.Text = lastDayOfMonth.ToShortDateString();
}
}
protected void _btnSearch_Click(object sender, EventArgs e)
{
_sdsRecords.Select(DataSourceSelectArguments.Empty);
}
protected void _sdsNoleggi_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
DateTime _exit = DateTime.Parse(_txtExitDate.Text.Trim());
DateTime _enter = DateTime.Parse(_txtEnterDate.Text.Trim());
e.Command.Parameters["exit"].Value = _exit ;
e.Command.Parameters["enter"].Value = _enter ;
}
The form seems to work at startup i.e. the query returns the number of rows I'm expecting but if I try to change the value of _txtExitDate and _txtEnterDate and click on search button nothing changes.
_sdsNoleggi_Selecting() is triggered and command parameters are set correctly.
Another issue I cannot unserstand is why if I change <asp:Parameter /> with <asp:ControlParameter /> like this:
<SelectParameters>
<asp:ControlParameter DbType="DateTime" Name="exit" ControlID="_txtExitDate" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter DbType="DateTime" Name="enter" ControlID="_txtEnterDate" PropertyName="Text" ConvertEmptyStringToNull="true" />
</SelectParameters>
parameters are not set correctly and then the whole query doesn't return any record (_sdsRecords_Selecting() event handler is removed in such case).
---- LATE UPDATE ----
As stated below the site this page come from is fully coded this way. Changing the paradigm of the whole thing is not an option.
_sdsRecords_Selectingfor the selecting event, but in the question you've shown_sdsNoleggi_Selecting. Is that just a typo in the question? Or do you have multiple SqlDataSource and selecting handlers in your code, and possibly are getting them confused?