ASP.NET SQLDataSource SelectParameters default value not working

Question

I wrote a simple ASP.NET form to manage a particular query on my (MySQL) db. Such query is handled by this stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `noleggio_conducenti`(IN _id_convenzionato INT(10))
BEGIN
IF _id_convenzionato IS NOT NULL THEN
    SELECT * FROM
    (
        SELECT 
            *
        FROM
            noleggi AS H
        WHERE
            id_convenzionato = _id_convenzionato 

        UNION 

        SELECT 
            *
        FROM
            noleggi AS H
        WHERE
            id_convenzionato IN (SELECT 
                    id_recipiente
                FROM
                    sosautomotive.transizioni_point
                WHERE
                    id_cedente = _id_convenzionato )
    ) H
    WHERE (H.uscita IS NOT NULL) AND (H.rientro IS NOT NULL);
ELSE
    SELECT 
        *
    FROM
        noleggi
    WHERE (uscita IS NOT NULL) AND (rientro IS NOT NULL);
END IF;
END

As you can see the only parameter can be NULL ... my hope was to use this stored procedure to provide data to the following SQLDataSource object:

<asp:SqlDataSource 
    ID="_sdsConducenti" 
    runat="server"
    ConnectionString="<%$ ConnectionStrings:sos_db %>"
    ProviderName="<%$ ConnectionStrings:sos_db.ProviderName %>" 
    SelectCommandType="StoredProcedure"
    SelectCommand="noleggio_conducenti">
    <SelectParameters>
        <asp:Parameter Name="_id_convenzionato" Type="Int32" DefaultValue="" ConvertEmptyStringToNull="true" />
    </SelectParameters>
</asp:SqlDataSource>

That provides data to a GridView :

<asp:GridView
    ID="_gvConducenti"
    runat="server"
    DataSourceID="_sdsConducenti"
    OnSorting="_gvConducenti_Sorting"
    OnPageIndexChanging="_gvConducenti_PageIndexChanging"
    OnRowDataBound="_gvConducenti_RowDataBound"
    AutoGenerateColumns="false"
    EmptyDataText="Nessun conducente presente."
    BorderStyle="None"
    CellSpacing="0"
    CellPadding="0"
    ShowHeader="true"
    ShowFooter="true"
    AllowSorting="true"
    AllowPaging="true"
    PageSize="10"
    GridLines="Horizontal"
    SelectedIndex="0"
    Style="width: 100%;" 
    HorizontalAlign="Center">
    <SelectedRowStyle CssClass="SelRow" />
    <HeaderStyle CssClass="GridHeader" />
    <AlternatingRowStyle BackColor="#F7F5E9" CssClass="AltRow" />
    <PagerStyle HorizontalAlign="Center" />
    <PagerSettings
        Visible="true"
        Mode="NumericFirstLast"
        PageButtonCount="3"
        Position="Bottom"
        NextPageText="Pagina successiva"
        PreviousPageText="Pagina precedente"
        FirstPageText="Prima pagina"
        LastPageText="Ultima pagina" />
    <Columns>
        <asp:TemplateField Visible="false">
            <HeaderTemplate>&nbsp;</HeaderTemplate>
            <ItemTemplate>
                <%#Eval("idnoleggio")%>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Numero" SortExpression="numero">
            <ItemTemplate>
                <asp:LinkButton 
                    runat="server" 
                    CommandArgument='<%# Eval("idnoleggio")%>' 
                    CommandName="Link" 
                    OnCommand="Link_Command">
                        <%#Eval("numero_completo")%>
                </asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField HeaderText="Conducente" DataField="conducente" />
        <asp:BoundField HeaderText="Via" DataField="conducente_via" />
        <asp:BoundField HeaderText="N° Civico" DataField="conducente_num_civico" />
        <asp:BoundField HeaderText="CAP" DataField="conducente_cap" />
        <asp:BoundField HeaderText="Città" DataField="conducente_residente_in" />
        <asp:BoundField HeaderText="Prov." DataField="conducente_residente_in_provincia" />
        <asp:BoundField HeaderText="Convenzionato" DataField="convenzionato" />
        <asp:BoundField HeaderText="Uscita" DataField="uscita" DataFormatString="{0:dd/MM/yyyy HH:mm}" />
        <asp:BoundField HeaderText="Rientro" DataField="rientro" DataFormatString="{0:dd/MM/yyyy HH:mm}" />
        <asp:BoundField HeaderText="Tipologia noleggio" DataField="modalita_noleggio" />
    </Columns>
</asp:GridView>

of course this form has other fields used to filter the query and those are handled by this couple of functions:

private void Ricerca()
{
    if (!String.IsNullOrWhiteSpace(_txtConvenzionato.Text.Trim()))
    {
        int _nIdConvenzionato = CUtilita.RitornaIdConvenzionato(_txtConvenzionato.Text);

        if (_nIdConvenzionato != -1)
            _sdsConducenti.SelectParameters["_id_convenzionato"].DefaultValue = _nIdConvenzionato.ToString();
        else
            _sdsConducenti.SelectParameters["_id_convenzionato"].DefaultValue = null;
    }

    _sdsConducenti.FilterExpression = Filter();

}

private string Filter()
{
    StringBuilder _sbFilter = new StringBuilder();

    try
    {
        #region Data fine noleggio

        if (!string.IsNullOrWhiteSpace(_txtDallaDataFineNoleggio.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataFineNoleggio.Text.Trim()))
        {
            DateTime _dtDallaDataFineNoleggio = DateTime.Parse(_txtDallaDataFineNoleggio.Text.Trim());
            DateTime _dtAllaDataFineNoleggio = DateTime.Parse(_txtAllaDataFineNoleggio.Text.Trim());

            _sbFilter.AppendFormat("((uscita>='{0:yyyy-MM-dd}') AND (uscita<='{1:yyyy-MM-dd}'))", _dtDallaDataFineNoleggio, _dtAllaDataFineNoleggio);
        }
        else if (string.IsNullOrWhiteSpace(_txtDallaDataFineNoleggio.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataFineNoleggio.Text.Trim()))
        {
            DateTime _dtAllaDataFineNoleggio = DateTime.Parse(_txtAllaDataFineNoleggio.Text.Trim());
            _sbFilter.AppendFormat("(uscita<='{0:yyyy-MM-dd}')", _dtAllaDataFineNoleggio);
        }
        else if (!string.IsNullOrWhiteSpace(_txtDallaDataFineNoleggio.Text.Trim()) &&
            string.IsNullOrWhiteSpace(_txtAllaDataFineNoleggio.Text.Trim()))
        {
            DateTime _dtDallaDataFineNoleggio = DateTime.Parse(_txtDallaDataFineNoleggio.Text.Trim());
            _sbFilter.AppendFormat("(uscita>='{0:yyyy-MM-dd}')", _dtDallaDataFineNoleggio);
        }

        #endregion
    }
    catch (FormatException ex)
    {
        _lblStatus.Text = ex.Message;
        _lblStatus.ForeColor = System.Drawing.Color.Red;
    }

    try
    {
        #region Data esportazione

        if (!string.IsNullOrWhiteSpace(_txtDallaDataEstrazione.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataEstrazione.Text.Trim()))
        {
            DateTime _dtDallaDataEstrazione = DateTime.Parse(_txtDallaDataEstrazione.Text.Trim());
            DateTime _dtAllaDataEstrazione = DateTime.Parse(_txtAllaDataEstrazione.Text.Trim());

            _sbFilter.AppendFormat("((data_esportazione_conducenti>='{0:yyyy-MM-dd}') AND (data_esportazione_conducenti<='{1:yyyy-MM-dd}'))", _dtDallaDataEstrazione, _dtAllaDataEstrazione);
        }
        else if (string.IsNullOrWhiteSpace(_txtDallaDataEstrazione.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataEstrazione.Text.Trim()))
        {
            DateTime _dtAllaDataEstrazione = DateTime.Parse(_txtAllaDataEstrazione.Text.Trim());
            _sbFilter.AppendFormat("(data_esportazione_conducenti<='{0:yyyy-MM-dd}')", _dtAllaDataEstrazione);
        }
        else if (!string.IsNullOrWhiteSpace(_txtDallaDataEstrazione.Text.Trim()) &&
            string.IsNullOrWhiteSpace(_txtAllaDataEstrazione.Text.Trim()))
        {
            DateTime _dtDallaDataEstrazione = DateTime.Parse(_txtDallaDataEstrazione.Text.Trim());
            _sbFilter.AppendFormat("(data_esportazione_conducenti>='{0:yyyy-MM-dd}')", _dtDallaDataEstrazione);
        }

        #endregion
    }
    catch (FormatException ex)
    {
        _lblStatus.Text = ex.Message;
        _lblStatus.ForeColor = System.Drawing.Color.Red;
    }

    #region Tipo Noleggio

    if (!string.IsNullOrWhiteSpace(_ddlTipoNoleggio.SelectedValue) && !_ddlTipoNoleggio.SelectedValue.Equals("TUTTI"))
    {
        if (_sbFilter.Length > 0)
            _sbFilter.Append(" AND");

        _sbFilter.Append("(modalita_noleggio = '" + _ddlTipoNoleggio.SelectedValue + "')");
    }

    #endregion

    if (_sbFilter.Length == 0)
        return null;

    return _sbFilter.ToString();

}

I tested the stored procedure trough MySQL Workbench and I'm sure it works (both with a valid parameter and with NULL). BUT when I try to use it within the form it works ONLY if the parameter value is provided and when then value is set to NULL (i.e. _sdsConducenti.SelectParameters["_id_convenzionato"].DefaultValue = null) the query did not provide any data (while it should retrieve a large number of records). Where did I fail? How can I check if the NULL value is correctly set as the parameter value before calling the stored procedure?

Full code available here.

MBB · Accepted Answer · 2018-09-07 10:34:20Z

1

Try specifying below property in SQLDataSource definition -

CancelSelectOnNullParameter="false"

And it is described as

true if a data retrieval operation is canceled when a parameter contained in the SelectParameters collection evaluated to null; otherwise, false. The default is true

The similar problem is explained here -

Set SqlDataSource parameter to null

For more information refer here-

edited Sep 7, 2018 at 10:34

answered Sep 7, 2018 at 10:04

MBB

1,6954 gold badges11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP.NET SQLDataSource SelectParameters default value not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related