locked
ASP.NET SQLDataSource SelectParameters not set correctly RRS feed

  • Question

  • User-1939834628 posted

    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>
    

    For several reason I cannot change the paradigm of this webform I need it to work trough SQLDataSource even if it's the worst method to use.

    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).

    Friday, December 11, 2020 5:01 PM

Answers

  • User1535942433 posted

    Hi weirdgyn1972,

    Accroding to your description,as far as I think,if you use Parameter,you could use this to return the value:

    e.Command.Parameters["exit"].Value = _exit ;

    However,if you use ControlParameter, it return a control.So you could use this to set the value:

    _txtExitDate.Text= x;

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2020 7:29 AM

All replies

  • User1535942433 posted

    Hi weirdgyn1972,

    Accroding to your description,as far as I think,if you use Parameter,you could use this to return the value:

    e.Command.Parameters["exit"].Value = _exit ;

    However,if you use ControlParameter, it return a control.So you could use this to set the value:

    _txtExitDate.Text= x;

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2020 7:29 AM
  • User-1939834628 posted

    Hi. I fixed the problem using ControlParameter and specifing the Direction = Input which I was giving as superflous (I don't think this the fix but maybe I did something wrong in the middle).

    Wednesday, December 16, 2020 4:55 PM