0

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.

4
  • Why do your control ID's start with underscores? And why are you using a SqlDataSource? Why don't you just use a SqlCommand (possibly with a micro-ORM such as Dapper) to get the data you need into a class that represents your data, then bind that as appropriate? SqlDataSource is one of the worst parts of Web Forms. Commented Dec 11, 2020 at 14:38
  • decorations on methods are some sort of code guideline the former developer used.. Anyway they are not part of the problem. Commented Dec 11, 2020 at 15:37
  • Your SqlDataSoure is hitting _sdsRecords_Selecting for 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? Commented Dec 12, 2020 at 13:36
  • Is a typo there's only a SQLDatasource I prefer to use english names instead original ones because italian words may sound confusing. Commented Dec 12, 2020 at 15:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.