Updating my Gridview Search boxes to use LIKE instead of '= or null'

Question

I currently have a gridview that has text boxes across the top that the user can leave blank (in which case all records in the table are returned) or the user can supply exact matches to information in corresponding fields in the table in the text boxes which will restrict results to only those records have those rows that have the contents of the text box in the corresponding field. I have 14 of these text boxes and any combination of them can be populated or left unused creating a filter for the records a user desires.

But I would like to take this to the next step. Instead of requiring an 'exact match' I would prefer to do a LIKE. Essentially if I supply the word dog I would like the select to effectively function as %dog%. I've try several different ways to approach this but I can't seem to get anything to work. So I thought that I would put out my code here and a screen shot of what I have and see if anyone has any suggestions of how to go about accomplishing this.

My aspx Page looks like this:

<%@ Page Title="Inventory Listing" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="InvList.aspx.cs" Inherits="CInTrac.InvList" EnableSessionState="True" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <asp:Table runat="server" Visible="true" Width="800px">
        <asp:TableHeaderRow>
            <asp:TableCell Font-Size="X-Small">Asset ID</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Asset Type</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Asset Name</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Short Description</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Manufacturer</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Asset Model</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Asset Tag</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Asset Serial Number</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Deltek Asset ID</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Location Group</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Long Description</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Organization ID</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Primary IP Address</asp:TableCell>
            <asp:TableCell Font-Size="X-Small">Status</asp:TableCell>
        </asp:TableHeaderRow>

        <asp:TableRow>
            <asp:TableCell>
                <asp:TextBox ID="AssetIDTbx" runat="server" Width="45px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetTypeTbx" runat="server" Width="100px" AutoPostBack="True"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetNameTbx" runat="server" AutoPostBack="True"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetShortDescTbx" runat="server" AutoPostBack="True"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetManufTbx" runat="server" Width="100px" AutoPostBack="True"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetModelTbx" runat="server" Width="100px" AutoPostBack="True"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetTagTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetSerialNumTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetDTAssetIDTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetLocGrpTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetLongDescTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetOrgIDTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetPriIPAddrTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
            <asp:TableCell>
                <asp:TextBox ID="AssetStatusTbx" runat="server" Width="100px" AutoPostBack="true"></asp:TextBox></asp:TableCell>
        </asp:TableRow>
    </asp:Table>
    <asp:GridView ID="InventoryListingGV" runat="server" DataSourceID="InventoryList" OnSelectedIndexChanged="InventoryListingGV_SelectedIndexChanged" AllowPaging="True" AllowSorting="True" CellPadding="3" Font-Size="X-Small" BackColor="White" BorderColor="#3399FF" BorderWidth="1px" BorderStyle="None" GridLines="Horizontal" PagerSettings-Mode="NumericFirstLast" HeaderStyle-BackColor="#269AE9" AutoGenerateColumns="False">

        <Columns>
            <asp:CommandField ShowSelectButton="True" SelectText="Update">
                <ItemStyle BackColor="#99ccff" />
            </asp:CommandField>
            <asp:BoundField DataField="AssetID" HeaderText="AID" ReadOnly="True" SortExpression="AssetID"></asp:BoundField>
            <asp:BoundField DataField="AssetType" HeaderText="Asset Type" SortExpression="AssetType">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetName" HeaderText="Asset Name" SortExpression="AssetName"></asp:BoundField>
            <asp:BoundField DataField="AssetShortDesc" HeaderText="Short Desc" SortExpression="AssetShortDesc">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetLongDesc" HeaderText="Long Desc" SortExpression="AssetLongDesc"></asp:BoundField>
            <asp:BoundField DataField="AssetAddNotes" HeaderText="Add Notes" SortExpression="AssetAddNotes">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetManuf" HeaderText="Manufacturer" SortExpression="AssetManuf"></asp:BoundField>
            <asp:BoundField DataField="AssetModel" HeaderText="Model" SortExpression="AssetModel">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetTag" HeaderText="Asset Tag" SortExpression="AssetTag"></asp:BoundField>
            <asp:BoundField DataField="AssetSerialNum" HeaderText="Serial Num" SortExpression="AssetSerialNum">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetAcqDate" HeaderText="Date Acquired" SortExpression="AssetAcqDate"></asp:BoundField>
            <asp:BoundField DataField="AssetDTAssetID" HeaderText="Deltek Asset ID" SortExpression="AssetDTAssetID">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetLocGrp" HeaderText="Loc Grp" SortExpression="AssetLocGrp"></asp:BoundField>
            <asp:BoundField DataField="AssetLoc1" HeaderText="Loc 1" SortExpression="AssetLoc1">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetLoc2" HeaderText="Loc 2" SortExpression="AssetLoc2"></asp:BoundField>
            <asp:BoundField DataField="AssetLoc3" HeaderText="Loc 3" SortExpression="AssetLoc3">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetParent" HeaderText="Asset Parent" SortExpression="AssetParent"></asp:BoundField>
            <asp:BoundField DataField="AssetStatus" HeaderText="Status" SortExpression="AssetStatus">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetPropType" HeaderText="Property Type" SortExpression="AssetPropType"></asp:BoundField>
            <asp:BoundField DataField="AssetPrimUser" HeaderText="Primary User" SortExpression="AssetPrimUser">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetEntered" HeaderText="Entered" SortExpression="AssetEntered"></asp:BoundField>
            <asp:BoundField DataField="AssetEnteredBy" HeaderText="Entered By" SortExpression="AssetEnteredBy">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetOwner" HeaderText="Asset Owner" SortExpression="AssetOwner"></asp:BoundField>
            <asp:BoundField DataField="AssetCompany" HeaderText="Company" SortExpression="AssetCompany">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetPriIPAddr" HeaderText="Primary IP Addr" SortExpression="AssetPriIPAddr"></asp:BoundField>
            <asp:BoundField DataField="AssetPriMACAddr" HeaderText="Primary MAC Addr" SortExpression="AssetPriMACAddr">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetPriOS" HeaderText="Primary OS" SortExpression="AssetPriOS"></asp:BoundField>
            <asp:BoundField DataField="AssetPriOSSP" HeaderText="Primary OS SP" SortExpression="AssetPriOSSP">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetNotes" HeaderText="Asset Notes" SortExpression="AssetNotes"></asp:BoundField>
            <asp:BoundField DataField="AssetAdminGrp" HeaderText="Admin Grp" SortExpression="AssetAdminGrp">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetOrgID" HeaderText="Organization ID" SortExpression="AssetOrgID"></asp:BoundField>
            <asp:BoundField DataField="AssetOperType" HeaderText="Oper Type" SortExpression="AssetOperType">
                <ItemStyle BackColor="#CCCCCC"></ItemStyle>
            </asp:BoundField>
            <asp:BoundField DataField="AssetOperStatus" HeaderText="Oper Status" SortExpression="AssetOperStatus"></asp:BoundField>
        </Columns>

        <FooterStyle BackColor="White" ForeColor="#000066"></FooterStyle>

        <HeaderStyle BackColor="#99ccff" Font-Bold="True" ForeColor="Black"></HeaderStyle>

        <PagerStyle HorizontalAlign="Left" ForeColor="#000066" BackColor="White"></PagerStyle>

        <RowStyle ForeColor="#000066"></RowStyle>

        <SelectedRowStyle BackColor="#669999" ForeColor="White" Font-Bold="True"></SelectedRowStyle>

        <SortedAscendingCellStyle BackColor="#F1F1F1"></SortedAscendingCellStyle>

        <SortedAscendingHeaderStyle BackColor="#007DBB"></SortedAscendingHeaderStyle>

        <SortedDescendingCellStyle BackColor="#CAC9C9"></SortedDescendingCellStyle>

        <SortedDescendingHeaderStyle BackColor="#66ccff"></SortedDescendingHeaderStyle>
    </asp:GridView>
    <asp:SqlDataSource ID="InventoryList" runat="server" CancelSelectOnNullParameter="false" ConnectionString='<%$ ConnectionStrings:CMDB_testConnectionString %>' SelectCommand="SELECT AssetID, AssetType, AssetName, AssetShortDesc, AssetLongDesc, AssetAddNotes, AssetManuf, AssetModel, AssetTag, AssetSerialNum, AssetAcqDate, AssetDTAssetID, AssetLocGrp, AssetLoc1, AssetLoc2, AssetLoc3, AssetParent, AssetStatus, AssetPropType, AssetPrimUser, AssetEntered, AssetEnteredBy, AssetOwner, AssetCompany, AssetPriIPAddr, AssetPriMACAddr, AssetPriOS, AssetPriOSSP, AssetNotes, AssetAdminGrp, AssetOrgID, AssetOperType, AssetOperStatus FROM cmdbv_Assets_CInTrac WHERE (AssetID = ISNULL(@AssetID, AssetID)) AND (AssetName = ISNULL(@AssetName, AssetName)) AND (AssetType = ISNULL(@AssetType, AssetType)) AND (AssetManuf = ISNULL(@AssetManuf, AssetManuf)) AND (AssetModel = ISNULL(@AssetModel, AssetModel)) AND (AssetTag = ISNULL(@AssetTag, AssetTag)) AND (AssetSerialNum = ISNULL(@AssetSerialNum, AssetSerialNum)) AND (AssetDTAssetID = ISNULL(@AssetDTAssetID, AssetDTAssetID)) AND (AssetLocGrp = ISNULL(@AssetLocGrp, AssetLocGrp)) AND (AssetLongDesc = ISNULL(@AssetLongDesc, AssetLongDesc)) AND (AssetOrgID = ISNULL(@AssetOrgID, AssetOrgID)) AND (AssetPriIPAddr = ISNULL(@AssetPriIPAddr, AssetPriIPAddr)) AND (AssetStatus = ISNULL(@AssetStatus, AssetStatus))">
        <SelectParameters>
            <asp:ControlParameter Name="AssetID" ControlID="AssetIDTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetName" ControlID="AssetNameTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetType" ControlID="AssetTypeTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetManuf" ControlID="AssetManufTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetModel" ControlID="AssetModelTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetTag" ControlID="AssetTagTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetSerialNum" ControlID="AssetSerialNumTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetDTAssetID" ControlID="AssetDTAssetIDTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetLocGrp" ControlID="AssetLocGrpTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetLongDesc" ControlID="AssetLongDescTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetOrgID" ControlID="AssetOrgIDTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetPriIPAddr" ControlID="AssetPriIPAddrTbx" Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter Name="AssetStatus" ControlID="AssetStatusTbx" Type="String" ConvertEmptyStringToNull="true" />
        </SelectParameters>
    </asp:SqlDataSource>
</asp:Content>​

Hoping someone has some insight because I would love to be able to implement a LIKE to these text boxes.

Answer 1

You will need IS NULL OR LIKE in where clause.

SelectCommand="SELECT AssetID, ..., AssetOperStatus 
FROM cmdbv_Assets_CInTrac 
WHERE (@AssetID IS NULL OR AssetID LIKE '%' + @AssetID + '%') AND 
...
(@AssetStatus IS NULL OR AssetStatus LIKE '%' + @AssetStatus + '%')"

From Win: (RTRIM(LTRIM(ISNULL(@AssetID, ''))) = '' OR AssetID LIKE '%' + @AssetID + '%')