multiple search textboxes in asp.net

Question

i have multiple text-boxes and one drop-down list in my system and i'm using the Query builder to select the data from multiple tables in the database (SQL server), and display them in the grid view

Suggestion ID : (textbox) Staff ID: (textbox) Status: (drop down list) Staff Name: (textbox)

..............................

I want the searching result to work by entering one field or multiple fields. the problem is, when i browse in the internet to test if the queries worked or not, i find that it works only by entering all the fields.

this is the Query generated in the query builder (Visual studio 2010)

SELECT
SuggestionDetail.SuggestionDescription, SuggestionDetail.SuggestionID,
SuggestionDetail.DateOfSubmission, Employee.EmployeeID, Employee.City,
Employee.EmployeeName, Category.CategoryName, SuggestionStatusDetail.DateOfLastUpdate,
SuggestionStatus.StatusName

FROM
Employee

INNER JOIN
SuggestionDetail

ON
Employee.EmployeeID = SuggestionDetail.EmployeeID

INNER JOIN
Category

ON
SuggestionDetail.CategoryID = Category.CategoryID

INNER JOIN
SuggestionStatusDetail

ON
SuggestionDetail.SuggestionID = SuggestionStatusDetail.SuggestionID

INNER JOIN
SuggestionStatus

ON
SuggestionStatusDetail.StatusID = SuggestionStatus.StatusID

WHERE
(Employee.EmployeeName LIKE N'%' + @empname + N'%')
OR
(Employee.EmployeeID LIKE '%' + @empid + '%')
OR
(SuggestionDetail.SuggestionID LIKE '%' + @txtDocID + '%')
OR
(SuggestionStatus.StatusName LIKE N'%' + @StatusName + N'%')

And this the code generated inside the HTML :

     <asp:GridView ID="GridView1" runat="server" BackColor="White" class="GridView"
         BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" 
         EnableModelValidation="True" ForeColor="Black" GridLines="Vertical" 
         AutoGenerateColumns="False" 
         DataSourceID="SqlDataSource2">
         <AlternatingRowStyle BackColor="White" />
         <Columns>
             <asp:BoundField DataField="SuggestionDescription" HeaderText="SuggestionDescription" 
                 SortExpression="SuggestionDescription" />
             <asp:BoundField DataField="SuugestionID" HeaderText="SuggestionID" 
                 InsertVisible="False" ReadOnly="True" SortExpression="SuggestionID" />
             <asp:BoundField DataField="DateOfSubmission" HeaderText="DateOfSubmission" 
                 SortExpression="DateOfSubmission" />
             <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
                 SortExpression="EmployeeID" />
             <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
             <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" 
                 SortExpression="EmployeeName" />
             <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" 
                 SortExpression="CategoryName" />
             <asp:BoundField DataField="DateOfLastUpdate" HeaderText="DateOfLastUpdate" 
                 SortExpression="DateOfLastUpdate" />
             <asp:BoundField DataField="StatusName" HeaderText="StatusName" 
                 SortExpression="StatusName" />
         </Columns>

any help please, i am still new in visual studio.

     <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
         ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

         SelectCommand="SELECT SuggestionDetail.SuggestionDescription, SuggestionDetail.SuggestionID, SuggestionDetail.DateOfSubmission, Employee.EmployeeID, Employee.City, Employee.EmployeeName, Category.CategoryName, SuggestionStatusDetail.DateOfLastUpdate, SuggestionStatus.StatusName FROM Employee INNER JOIN SuggestionDetail ON Employee.EmployeeID = SuggestionDetail.EmployeeID INNER JOIN Category ON SuggestionDetail.CategoryID = Category.CategoryID INNER JOIN SuggestionStatusDetail ON SuggestionDetail.SuggestionID = SuggestionStatusDetail.SuggestionID INNER JOIN SuggestionStatus ON SuggestionStatusDetail.StatusID = SuggestionStatus.StatusID WHERE (Employee.EmployeeName LIKE N'%' + @empname + N'%') OR (Employee.EmployeeID LIKE '%' + @empid + '%') OR (SuggestionDetail.SuggestionID LIKE '%' + @txtDocID + '%') OR (SuggestionStatus.StatusName LIKE N'%' + @StatusName + N'%')">
         <SelectParameters>
             <asp:ControlParameter ControlID="txtStaffName" Name="empname" 
                 PropertyName="Text" />
             <asp:ControlParameter ControlID="txtStaffID" Name="empid" PropertyName="Text" />
             <asp:ControlParameter ControlID="txtDocID" Name="txtDocID" 
                 PropertyName="Text" />
             <asp:ControlParameter ControlID="ddStatus" Name="StatusName" 
                 PropertyName="SelectedValue" />
         </SelectParameters>
     </asp:SqlDataSource>

Please look at this answer: http://stackoverflow.com/a/4080801/1380428 — Adil Mammadov, May 13 '13 at 09:50

score 0 · Answer 1 · answered May 13 '13 at 08:53

IF joins and other things are working fine then see i had change some fields in where clause. Which gives you back whole result when all parameters are null/blank(like nullable) and returns filtered result according to any one parameter is null/blank.

CREATE/ALTER STOREDPROCEDURE SP_NAME
@empname varchar(50) = NULL,
@empid int = NULL,
@txtDocID  int = NULL,
@StatusName varchar(50) = NULL
AS
BEGINE
    SELECT
    SuggestionDetail.SuggestionDescription, SuggestionDetail.SuggestionID,
    SuggestionDetail.DateOfSubmission, Employee.EmployeeID, Employee.City,
    Employee.EmployeeName, Category.CategoryName, SuggestionStatusDetail.DateOfLastUpdate,
    SuggestionStatus.StatusName

    FROM
    Employee

    INNER JOIN
    SuggestionDetail

    ON
    Employee.EmployeeID = SuggestionDetail.EmployeeID

    INNER JOIN
    Category

    ON
    SuggestionDetail.CategoryID = Category.CategoryID

    INNER JOIN
    SuggestionStatusDetail

    ON
    SuggestionDetail.SuggestionID = SuggestionStatusDetail.SuggestionID

    INNER JOIN
    SuggestionStatus

    ON
    SuggestionStatusDetail.StatusID = SuggestionStatus.StatusID

    WHERE
    (Employee.EmployeeName LIKE N'%' + @empname + N'%' OR @empname IS NULL)
    OR
    (Employee.EmployeeID LIKE '%' + @empid + '%' OR @empid IS NULL)
    OR
    (SuggestionDetail.SuggestionID LIKE '%' + @txtDocID + '%' OR @txtDocID IS NULL)
    OR
    (SuggestionStatus.StatusName LIKE N'%' + @StatusName + N'%' OR @StatusName IS NULL)
END

multiple search textboxes in asp.net

1 Answers1