Multi-parameter SqlDatasource search not returning any records

Question

I have an odd problem. I have a gridview attached to a sqldatasource that I send three possible parameters to. The search works fine if I search the item number but if I search for the first name or last name without the item number it does not return anything. The SQL works fine when I test it in SQL management studio but does not when I test it in visual studio in the search form. Any assistance in solving this will be appreciated. Thanks for your time.

<table style="width: 100%;">

        <tr>
            <td>Caller Search</td>
            <td>
                <asp:Label ID="ITEMsearchlable" runat="server" Text="ITEM#"></asp:Label><br />
                <asp:TextBox ID="ITEM_NUMsearch" runat="server"></asp:TextBox></td>
            <td>
                <asp:Label ID="ofname_label" runat="server" Text="First name"></asp:Label><br />
                <asp:TextBox ID="search_fname" runat="server"  CssClass="uppercase"></asp:TextBox></td>
            <td><asp:Label ID="olname_label" runat="server" Text="Last name"></asp:Label><br />
                <asp:TextBox ID="search_lname" runat="server"   CssClass="uppercase"></asp:TextBox></td>
            <td>
                <asp:Button ID="search_btn" runat="server" Text="Offender Caller" /></td>
        </tr>

    </table>

          <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" Width="771px" EmptyDataText="No record found">
              <Columns>
                  <asp:CommandField ShowSelectButton="True" />
                  <asp:BoundField DataField="ITEM_NUM" HeaderText="ITEM_NUM" SortExpression="ITEM_NUM" />
                  <asp:BoundField DataField="NAME_FIRST" HeaderText="NAME_FIRST" SortExpression="NAME_FIRST" />
                  <asp:BoundField DataField="NAME_LAST" HeaderText="NAME_LAST" SortExpression="NAME_LAST" />
                  <asp:BoundField DataField="CALLER_ID" HeaderText="CALLER_ID" SortExpression="CALLER_ID" />
              </Columns>
          </asp:GridView>
          <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
              ConnectionString="<%$ ConnectionStrings:testdbConnectionString %>" 
              SelectCommand="SELECT DISTINCT
                            ORDERS.ITEM_NUM, 
                            ORDERS.NAME_FIRST, 
                            ORDERS.NAME_LAST, 
                            VICTIM_CALL_LOG.CALLER_ID
                            FROM ORDERS LEFT OUTER JOIN
                            VICTIM_CALL_LOG ON ORDERS.ITEM_NUM = VICTIM_CALL_LOG.ITEM_NUM WHERE ((ORDERS.ITEM_NUM = @ITEM_NUM) 
                            OR (ORDERS.NAME_FIRST LIKE '%' + @NAME_FIRST + '%') OR (ORDERS.NAME_LAST LIKE '%' + @NAME_LAST + '%'))">
              <SelectParameters>
                  <asp:ControlParameter ControlID="ITEM_NUMsearch" Name="ITEM_NUM" PropertyName="Text" Type="Int32" />
                  <asp:ControlParameter ControlID="search_fname" DefaultValue="NULL" Name="NAME_FIRST" PropertyName="Text" Type="String" />
                  <asp:ControlParameter ControlID="search_lname" DefaultValue="NULL" Name="NAME_LAST" PropertyName="Text" Type="String" />
              </SelectParameters>
          </asp:SqlDataSource>

Just to check, could you take out the %s from the query and for now type them in search_fname? ISTR some problem when trying to add wildcard characters in the query rather than in the parameter value. — Andrew Morton
– Andrew Morton, Commented Nov 2, 2019 at 16:39
Oddly enough if I remove the ITEM_NUM from the search and sql it works fine but with it in it only works if I search ITEM number. So odd. — James Johnson
– James Johnson, Commented Nov 2, 2019 at 16:50
If you give the ControlParameter ITEM_NUM a default value of "-1", does that help? — Andrew Morton
– Andrew Morton, Commented Nov 2, 2019 at 16:53
That worked. Thanks. I don't know why I didn't think of a default value. Thanks so much! — James Johnson
– James Johnson, Commented Nov 2, 2019 at 17:12

Andrew Morton · Accepted Answer · 2019-11-02 17:23:31Z

1

You need to give the numeric parameter a default value too:

<asp:ControlParameter ControlID="ITEM_NUMsearch" DefaultValue="-1" Name="ITEM_NUM" PropertyName="Text" Type="Int32" />

I used "-1" because I guessed it would never be an actual value in the database, so that needs to be checked.

Note that having a DefaultValue of "NULL" in the string parameters is actually the string "NULL", not the database version of NULL. To do that properly, I suspect that you would have to use code-behind instead of <asp> controls. In the meantime, a default value of "12345" could be a better choice, just in case someone has a name with "null" in it - Null is an actual last name for some people: How does a surname of Null cause problems in many databases?

answered Nov 2, 2019 at 17:23

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

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

Collectives™ on Stack Overflow

Multi-parameter SqlDatasource search not returning any records

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related