Restructured SQL Query but it does not display elements

Question

I have a dropdown list ddlProjecsFilter that I populate with the names of all projects in my database.

On page load, I show this dropdown list and when a person selects a Name, they get all the information regarding that Project Name. However, I have two extra filters based on character input (Funder, Type).

When writing the query like this, the dropdown list has no elements in it, yet the two other filters work.

  protected void nameFilter()
    {
        string queryDefault = "SELECT NULL AS [Value], 'All' AS [Text], '0' AS [OrderNo] UNION ALL ";
        string queryBase = "SELECT [NameId] AS [Value], [Type] AS [Text], '1' AS [OrderNo] FROM [tblProjects] ";
        string queryWhere = " WHERE NameId IS NOT NULL ";
        string queryOrder = " ORDER BY [OrderNo], [Text]";


        if (!string.IsNullOrEmpty(txtTypeFilter.Text))
        {
            queryWhere +=" AND [Funder] LIKE @Funder";
            queryWhere += " AND [Type] LIKE @Type";
        }

        sdsProjects.SelectCommand = queryDefault + queryBase + queryWhere + queryOrder;
        sdsProjects.SelectParameters.Add("@Funder", txtFunderFilter.Text);
        sdsProjects.SelectParameters.Add("@Type", txtTypeFilter.Text);

        ddlProjectsFilter.DataBind();
    }

If I however do it like this, everything works as intended. However, this is not best practice.

   if (!string.IsNullOrEmpty(txtTypeFilter.Text))
        {
            queryWhere += string.Format(" AND [Funder] LIKE'%{0}%'", txtFunderFilter.Text);
            queryWhere += string.Format(" AND [Type] LIKE'%{0}%'", txtTypeFilter.Text);
        }

Can anyone point out the issue with the first snippet?

Definitions:

 <td>
                                        <asp:DropDownList runat="server" DataSourceID="sdsProjects" ID="ddlProjectsFilter"
                                            AutoPostBack="true" DataTextField="Text" DataValueField="Value"
                                            CssClass="form-control local-control" OnSelectedIndexChanged="ddlProjectsFilter_SelectedIndexChanged" AppendDataBoundItems="true">
                                        </asp:DropDownList><asp:SqlDataSource ID="sdsProjectsFilter" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseLive %>"></asp:SqlDataSource>
                                    </td>
                                    <td></td>
                                    <td>
                                        <asp:TextBox runat="server" ID="txtTypeFilter" AutoPostBack="true" OnTextChanged="txtTypeFilter_TextChanged"
                                            CssClass="form-control local-control TypeFilter"></asp:TextBox></td>
                                    <td></td>
                                    <td>
                                        <asp:TextBox runat="server" ID="txtFunderFilter" AutoPostBack="true" OnTextChanged="txtFunderFilter_TextChanged"
                                            CssClass="form-control local-control FunderFilter"></asp:TextBox></td>...

hmm.. they aren't equivalent.. if the second query was `LIKE '{0}'` (without wildcards) they would be. so the issue with the first query is probably that it needs a wildcard. I'm not sure if the `.Text`, or parameter value would include that wildcard though — Brett Caswell, Jun 19 '21 at 09:34
My idea was to stray away from using %s. What needs to be changed on the first code in that case to make it equivalent to the second snippet? — DarknessPlusPlus, Jun 19 '21 at 09:40
provide an example of `txtFunderFilter.Text` and `txtTypeFilter.Text`, also.. what is the schema of `Funder` and `Type` fields? it's possible you have trailing whitespace going on — Brett Caswell, Jun 19 '21 at 09:44
I added more definitions. Both Funder and Type are strings / nvarchar(255). How would you suggest I change the query to not use wildcards? — DarknessPlusPlus, Jun 19 '21 at 09:55
Does this answer your question? [Use of SqlParameter in SQL LIKE clause not working](https://stackoverflow.com/questions/665129/use-of-sqlparameter-in-sql-like-clause-not-working) — Yong Shun, Jun 19 '21 at 10:21

score 1 · Accepted Answer · answered Jun 19 '21 at 18:05

You have quite a good setup. However, you should "pair up" the text box test with each time you wish to add an optional parameter.

So, with this setup you can have 1 or 5 optional filters.

And because you want a like, see the code below:

So, I suggest this:

        SqlDataSource sdsProjects = new SqlDataSource();

        string queryDefault = "SELECT NULL AS [Value], 'All' AS [Text], '0' AS [OrderNo] UNION ALL ";
        string queryBase = "SELECT [NameId] AS [Value], [Type] AS [Text], '1' AS [OrderNo] FROM [tblProjects] ";
        string queryWhere = " WHERE NameId IS NOT NULL ";
        string queryOrder = " ORDER BY [OrderNo], [Text]";


        if (!string.IsNullOrEmpty(txtTypeFilter.Text))
        {
            queryWhere += " AND ([Type] LIKE '%' + @Type + '%') ";
            sdsProjects.SelectParameters.Add("@Type",DbType.String, txtTypeFilter.Text);
        }

        if (!string.IsNullOrEmpty(txtFunderFilter.Text))
        {
            queryWhere += " AND ([Funder] LIKE '%' + @Funder + '%') ";
            sdsProjects.SelectParameters.Add("@Funder",DbType.String, txtFunderFilter.Text);
        }

        sdsProjects.SelectCommand = queryDefault + queryBase + queryWhere + queryOrder;

Restructured SQL Query but it does not display elements

1 Answers1