Good evening everyone and hope you all had a good weekend. I am working on a site which allows the user to search database records based on entering either a reference number of a person' last name and then clicking the submit button. This brings the user to a results page which uses the gridview data control in asp.net. I can get each search criteria to work by itself (only include one textbox on the search page) and produce the correct results, and I can get them both to work together (user inputs reference number and last name) using "AND" in my where statement and produce the correct results. However, I need for the customer to have the option to enter either a last name OR a reference ID number and to be able to have my results page produce the correct results. I don't want the user to need to enter information in both the ID and Last Name field, but only one or the other. I have tried to accomplish this by changing AND to OR in my WHERE clause, but the search results will not produce any results with OR in my WHERE clause.

Here is applicable code on my results page:

SelectCommand="SELECT [ID], [F_Name], [L_Name], [Address], [City], [State], [Zip] FROM [Surplus_Funds] WHERE (([ID] = @ID) OR ([L_Name] LIKE '%' + @L_Name + '%'))">
                <SelectParameters>
                    <asp:FormParameter FormField="ID" Name="ID" Type="Int32" />
                    <asp:FormParameter FormField="L_Name" Name="L_Name" Type="String" />
                </SelectParameters>

Any tips or help with how to accomplish this task would be appreciated. I need results to be produced based on either or textbox having information input and searched on, but not both textboxes. Thanks in advance and I look forward to talking with someone soon.

Set the DefaultValue,

<SelectParameters>
<asp:FormParameter FormField="ID" Name="ID" Type="Int32" DefaultValue="0" />
<asp:FormParameter FormField="L_Name" Name="L_Name" Type="String" DefaultValue=" " />
                </SelectParameters>

Adatapost, thank you for your time and assistance, it is greatly apppreciated. Your suggestion worked exactly as desired. I am marking this thread as solved, but if you have a chance and can explain how and/or why that worked to help me learn more, I would appreciate that greatly too. Thanks again, and take care.

You're welcome. Thanks, I'm glad you got it working. Text From the MSDN page -

Controls that bind data to the parameter might throw an exception if a FormParameter is specified but no corresponding form variable is passed. They might also display no data if the form variable is passed with no corresponding value. Set the DefaultValue to avoid these situations where appropriate.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.