Bulid a serach feature .

Alomiry
Alomiry used Ask the Experts™
on
Hello,
I'm trying to build a search feature where a user enters a text value and the page returns records where the string appears in a specific field.

Can help me to find tutorials to explain the steps of building search feature by using LIKE/ SQL.

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I'm working on ASP.net C# and using Visual studio 2010 and built in database
Top Expert 2011

Commented:
You can do that using a gridview.

Here is an example of search. This actually works because I use it:

       <form id="form1" runat="server">
       <div style="text-align:center">
        <asp:Label ID="enterNamebutton" Text="<b>Enter Name</b>" runat="server" />
        <asp:TextBox ID="txtSearch" runat="server" style="height:23px"></asp:TextBox>
        <asp:Button ID="btnSearch" runat="server" TabIndex="1" height="27px" Text="GO" OnClick="btnSearch_Click" class="btn" />
        <asp:Button ID="btnClear" OnClick="ClearFilter" height="27px" Text="Reset" runat="server" class="btn" />
        </div>
        <br /><br />
         <div style="text-align:left">
         <div style="text-align:center">SEARCH By Last Name, First Name (e.g.; Doe, John) and click the <div style="color:#69e">Go</div> button
            Click the <div style="color:#69e;white-space:nowrap">Reset</div> button to SEARCH again<br /><br />
         <asp:GridView ID="GridView1" BorderWidth="0" runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" PageSize="15" DataSourceID="APOMembers" EmptyDataText="No data found" Width="564px">
         <HeaderStyle BackColor="#677617" ForeColor="White" Font-Size="Small" Font-Names="Verdana" width="100%" />
         <RowStyle BackColor="White" Font-Size="Small" Font-Names="Verdana" BorderStyle="None" />
          <AlternatingRowStyle BackColor="Gainsboro" width="100%" />
            <Columns>
                <asp:TemplateField HeaderText="Name">
                <ItemTemplate>
                <asp:LinkButton ID="lnkname" runat="server" Text='<%#Eval("Name") %>' PostBackUrl='<%#"~/Details.aspx?ID="&Eval("ID") %>'></asp:LinkButton>
                </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Cell_Phone" HeaderText="Cell Phone" SortExpression="Cell_Phone" >
                <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                </asp:BoundField>
                <asp:BoundField DataField="Phone" HeaderText="Home Phone" SortExpression="Phone" >
                <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                </asp:BoundField>
                <asp:BoundField DataField="Email_Add" HeaderText="Email Address" SortExpression="Email_Add" HtmlEncode="False">
                <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                </asp:BoundField>
            </Columns>
          </asp:GridView>
          <asp:SqlDataSource ID="Members" runat="server" ConnectionString="<%$ ConnectionStrings:APOConnectionString %>"
           ProviderName="<%$ ConnectionStrings:APOConnectionString.ProviderName %>"
            SelectCommand="SELECT ID, ([firstName]&' '& [LastName]) as Name, [Cell_Phone], [Phone], [Email_Add] FROM Africa WHERE (firstName LIKE '' & @firstName & '%' OR lastname LIKE '' & @lastName & '%')">
            <SelectParameters>
             <asp:ControlParameter ControlID="txtSearch" Name="firstName" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="txtSearch" Name="lastName" PropertyName="Text" Type="String" />
            </SelectParameters>
         </asp:SqlDataSource>
       </div>
      </div>
    </form>

Open in new window


You can also see more tutorials here.

http://www.codeproject.com/KB/custom-controls/GridViewSearch.aspx

Author

Commented:
Thanks, It's good tutorials.
But i'm working on Hotel Booking site and i have to use the search to look for available hotels.

Can you help to find the best way to do my task ?

Regards
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Top Expert 2011

Commented:
How are you looking for available hotels?

Do you have a database of a list of hotels?

Author

Commented:
I have a database for the hotels and rooms available.
and I\m using that way>

 string SearchType = Request.Params["id_input"];

 query.CommandText = " SELECT Name,Type FROM Hotels WHERE (Name LIKE '" + SearchType + "' + '%' )";


Regards
Top Expert 2011

Commented:
Try these and let me know. If you have a code different from this, show where you are having issues.

Please change whatever needs changing.

I am not c# guy but I think the codeFile looks good.

   //Hotels.aspx
   
   <%@ Page Language="C#" Debug="true" AutoEventWireup="false" Inherits="hotels" CodeFile="hotels.aspx.vb" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
      <title>Available Hotels</title>
    </head>
    <body>
       <form id="form1" runat="server">
       <div style="text-align:center">
        <asp:Label ID="enterNamebutton" Text="<b>Enter Name</b>" runat="server" />
        <asp:TextBox ID="SearchType" runat="server" style="height:23px"></asp:TextBox>
        <asp:Button ID="btnSearch" runat="server" TabIndex="1" height="27px" Text="GO" OnClick="btnSearch_Click" class="btn" />
        <asp:Button ID="btnClear" OnClick="ClearFilter" height="27px" Text="Reset" runat="server" class="btn" />
        </div>
        <br /><br />
         <div style="text-align:left">
         <div style="text-align:center">SEARCH By Name, and click the <div style="color:#69e">Go</div> button
            Click the <div style="color:#69e;white-space:nowrap">Reset</div> button to SEARCH again<br /><br />
         <asp:GridView ID="GridView1" BorderWidth="0" runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" PageSize="15" DataSourceID="Hotels" EmptyDataText="No data found" Width="564px">
         <HeaderStyle BackColor="#677617" ForeColor="White" Font-Size="Small" Font-Names="Verdana" width="100%" />
         <RowStyle BackColor="White" Font-Size="Small" Font-Names="Verdana" BorderStyle="None" />
          <AlternatingRowStyle BackColor="Gainsboro" width="100%" />
            <Columns>
                <asp:TemplateField HeaderText="Name">
                <ItemTemplate>
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="name" >
                <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                </asp:BoundField>
                <asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" >
                <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                </asp:BoundField>
            </Columns>
          </asp:GridView>
          <asp:SqlDataSource ID="Hotels" runat="server" ConnectionString="<%$ ConnectionStrings:HotesConnectionString %>"
           ProviderName="<%$ ConnectionStrings:HotesConnectionString.ProviderName %>"
            SelectCommand="SELECT Name,Type FROM Hotels WHERE (Name LIKE '' & @SearchType & '%')">
            <SelectParameters>
             <asp:ControlParameter ControlID="SearchType" Name="SearchType" PropertyName="Text" Type="String" />
            </SelectParameters>
         </asp:SqlDataSource>
       </div>
      </div>
    </form>
    </body>
    </html>
    
    //Hotels.aspx.cs

    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    
    public partial class Hotels : System.Web.UI.Page
    {
    
    	protected void Page_Load(object sender, EventArgs e)
    	{
    	}
    	protected void btnSearch_Click(object sender, EventArgs e)
    	{
    		GridView1.DataBind();
    	}
    	protected void ClearFilter(object sender, EventArgs e)
    	{
    		//  remove the filter and rebind the grid
    		this.txtSearch.Text = string.Empty;
    		this.GridView1.DataBind();
    	}
    
    }
    
//web.config: Add to your web.config or create yours

  <connectionStrings>
    <add name="HotesConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/inetpub/wwwroot/Hotels/App_Data/yourDB.mdb" providerName="System.Data.OleDb" />
  </connectionStrings>

Open in new window

Top Expert 2011

Commented:
Sorry change this line:

this.txtSearch.Text = string.Empty;

To

this.SearchType.Text = string.Empty;

Author

Commented:
But still working with grid view and that solution i don't like it, because it's a easy way .
Thanks any way

Regards
Top Expert 2011

Commented:
What exactly are you looking for?

Can you be more specific?

Author

Commented:
I'm looking to create search for my hotel booing system ?
And the result get from the database, and you can search by CITY,COUNTRY, NUMBER OF ROOM, NUMBER OF NIGHT. like www.hotel.com

Regards
Top Expert 2011

Commented:
Well if you want to use all those as your search param, then use this code:

<asp:TextBox id="txtSearch" runat="server" />


	strSQLQuery = "SELECT  Name,Type FROM Hotels " _
	& "FROM Hotels" _
	& "WHERE city LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
	& "OR country LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
	& "OR [NUMBER OF ROOM] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _				& "OR [NUMBER OF NIGHT] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
	& "ORDER BY city;"

Open in new window

Top Expert 2011

Commented:
More like this:

<asp:TextBox id="txtSearch" runat="server" />


	strSQLQuery = "SELECT  Name,Type FROM Hotels " _
	& "FROM Hotels" _
	& "WHERE city LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
	& "OR country LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
	& "OR [NUMBER OF ROOM] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
        & "OR [NUMBER OF NIGHT] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
	& "ORDER BY city;"

Open in new window


Same code; just formatting issues
Top Expert 2011

Commented:
And since you are using c#, I *think* it is something like this:

<asp:TextBox id="SearchType" runat="server" />


	query.CommandText = "SELECT  Name,Type FROM Hotels " _
	& "FROM Hotels" _
	& "WHERE city LIKE ' " + Replace(SearchType, "'", "''") + "%' " _
	& "OR country LIKE ' " + Replace(SearchType, "'", "''") + "%' " _
	& "OR [NUMBER OF ROOM] LIKE ' " + Replace(SearchType, "'", "''") + "%' " _
        & "OR [NUMBER OF NIGHT] LIKE '" + Replace(SearchType, "'", "''") + "%' " _
	& "ORDER BY city;"

Open in new window

Top Expert 2011

Commented:
also, these Replace(SearchType, "'", "''") are to protect from sql injection.

Read about sql inject here

http://msdn.microsoft.com/en-us/library/ms161953.aspx



Commented:
When i try to use Replace it's show me a error message saying:
The name Replace doesn't exit in the currently context

My code behinde:


string SearchType = Request.Params["id_city"];

            SqlConnection cnn = new SqlConnection();
            cnn.ConnectionString =
            @"Data Source=localhost\SQLEXPRESS;
                   AttachDbFilename=C:\Users\Anas\Documents\Visual Studio 2010\Projects\BookingWebApplication\BookingWebApplication\App_Data\Booking.mdf;
                   Integrated Security=True;

                   User Instance=True";

            SqlCommand query = cnn.CreateCommand();
            query.CommandText = "SELECT Name FROM Hotels WHERE city LIKE ' " + Replace(SearchType, "'", "''") + "%' ";
                     
            cnn.Open();
           
            SqlDataReader reader = query.ExecuteReader();
            while (reader.Read())
            {
                id_result.Text += reader["Name"] + "<br/>";
            }
            reader.Close();
            cnn.Close();


Regrads

Author

Commented:
Good work

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial