ASP.NET SQLDataSource SelectParameters not set correctly[Answered] RSS

• • Reply
  weirdgyn1972

  Member

  1 Points

  18 Posts

  ASP.NET SQLDataSource SelectParameters not set correctly

  Dec 11, 2020 05:01 PM|weirdgyn1972|LINK

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

• • Reply
  yij sun

  Contributor

  3360 Points

  1279 Posts

  Re: ASP.NET SQLDataSource SelectParameters not set correctly

  Dec 14, 2020 07:29 AM|yij sun|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  weirdgyn1972

  Member

  1 Points

  18 Posts

  Re: ASP.NET SQLDataSource SelectParameters not set correctly

  Dec 16, 2020 04:55 PM|weirdgyn1972|LINK

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