You must Sign In to post a response.
  • Category: SQL Server

    Sql function for dynamic search key words.

    Hi,

    I had a search box.If i enter a key word like "guru" in that box my sql query becomes:

    Select * from employee where empname like '%guru%'

    If i enter key words like "guru kumar" in that box my sql query becomes:

    Select * from employee where empname like '%guru%' or empname like '%kumar%'

    If i enter key words like "guru kumar rao" in that box my sql query becomes:

    Select * from employee where empname like '%guru%' or empname like '%kumar%' or empname like '%rao%'

    and so on...


    Here i would like to generate where condition dynamically based on number of keywords entered in search box.

    Can any one help me the solution for this...?
  • #652594
    To use AutoComplete you may use AjaxControlToolikit's AutoCompleteExtender control. For that you need a Web Service to read the data from the Database.

    Please check the following code:

    WebService.cs
    -------------


    /// <summary>
    /// Summary description for WebService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    [System.Web.Script.Services.ScriptService]
    public class WebService : System.Web.Services.WebService
    {

    public WebService()
    {

    //Uncomment the following line if using designed components
    //InitializeComponent();
    }

    [WebMethod]
    public string[] GetNames(string prefixText, int count)
    {
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
    count = 10;
    string str = "SELECT * FROM student WHERE sname like '%" + prefixText + "%'";
    con.Open();
    SqlCommand com = new SqlCommand(str, con);
    SqlDataReader dr = com.ExecuteReader();
    int i = 0;
    ArrayList AlNames = new ArrayList();
    if (dr.HasRows)
    {
    while (dr.Read())
    {
    string StrTemp = dr["sname"].ToString();
    AlNames.Add(StrTemp);
    i++;
    }
    }
    return AlNames.ToArray(typeof(string)) as string[];
    }
    }


    Design
    ------


    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="TextBox1"
    ServicePath="WebService.asmx" ServiceMethod="GetNames" MinimumPrefixLength="0"
    CompletionInterval="100" EnableCaching="true" CompletionSetCount="12">
    </asp:AutoCompleteExtender>


    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM

  • #652745
    Hi bulli

    Before pass the value in query replace space as "%" like below code


    protected void Button1_Click(object sender, EventArgs e)
    {
    string s = TextBox1.Text;
    s = s.Replace(" ", "%");
    sqlcon.Open();
    //This uery return your expected output data try it
    Sqlcmd = new SqlCommand("select * from emp where empname like '%" + s + "%'",sqlcon);
    da = new SqlDataAdapter(Sqlcmd);
    da.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    sqlcon.Close();
    }

    Regards
    N.Ravindran
    Your Hard work never fails

  • #653074

    protected void btnSearch_Click(object sender, EventArgs e)
    {
    string strSearch = txtSearch.Text;

    sqlcon.Open();

    Sqlcmd = new SqlCommand("select * from emp where empname like '%" + strSearch + "%'",sqlcon);
    da = new SqlDataAdapter(Sqlcmd);
    da.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    sqlcon.Close();
    }



    Regards,
    Naveen


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.