How to pass a search text with more than one search condition to bind gridview according to it?

Question

I am trying to pass searching text and based on the text entered into the textbox I want to fetch data from the database and display into gridview. I have a gridview which consists of Country name, Company Name, Product Category. Currently I am using a Textbox in which I am entering Company name and Holding this data into a session variable and passing this session variable as a where clause with the select query to display into gridview. I want that If I enter Country name and Product name in the same textbox then also the gridview data should display. How can I do this by using a single textbox? My aspx page-

<table>
<tr>
<td><asp:TextBox ID="TextBox1" runat="server" Width="167px"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Submit" 
        Width="116px" /></td>
</tr>
<tr>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</tr>
</table>

My cs Page-

public void bind()
    {
            dt = g1.return_dt("select  * from tbl1 where id is  not null  " + Session["Name"] + "  order by  compname ");
            if (dt.Rows.Count > 0)
            {
                adsource = new PagedDataSource();
                adsource.DataSource = dt.DefaultView;
                adsource.PageSize = 10;
                adsource.AllowPaging = true;
                adsource.CurrentPageIndex = pos;
                btnfirst.Enabled = !adsource.IsFirstPage;
                btnprevious.Enabled = !adsource.IsFirstPage;
                btnlast.Enabled = !adsource.IsLastPage;
                btnnext.Enabled = !adsource.IsLastPage;
                GridView1.DataSource = adsource;
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = null;
                GridView1.DataBind();
            }

}

protected void Button1_Click(object sender, EventArgs e)
    {
if (TextBox1.Text != "")
        {
            Session["Name"] = "and  compname  like '%" + TextBox1.Text + "%'";
        }
        else
        {
            Session["Name"] = null;
        }
}

I want to know How can I use this Session["Name"] object to pass Product Category and Country Name along with the Company Name. i.e. I want to know How can I display data in my gridview based on the different condition passed from this textbox. Please guide me.

Answer 1

Try below code

Session["Name"] = "and (compname  like '%" + TextBox1.Text.Trim() + "%' or ProductCategory like '%" + TextBox1.Text.Trim() + "%' or CountryName like '%" + TextBox1.Text.Trim() + "%')";

Answer 2

1.First of all you don't use session to pass values across functions.

2.Use input parameters like Bind(string textBoxValue)

3.Inside Bind(string textBoxValue), construct your query like:

dt = g1.return_dt(@"select * from tbl1 where id is  not null and (compname  like '" + textBoxValue + "' or prodname like '" + textBoxValue  + "') order by  compname ");

Answer 3

1. Split the text in the textbox into a string array.
2. Create parameterlist from splitted strings and add parameters to SqlCommand object.
3. Build where clause from criteria.

Here is a start (not tested) This code assumes that you are using Sql server as database.

private void CreateSelect(SqlCommand cmd)
{
    var criteria = TextBox1.Text.Trim().Split(null, StringSplitOptions.RemoveEmptyEntries); // null specifies to split on whitespace
    var selectStatement = new StringBuilder("select  * from tbl1 where id is not null ");
    var counter = 0;
    if(criteria.Length < 0)
    {
       selectStatement.Append(" and (");
       foreach(var str in criteria)
       {
           var paramName = "@p" + counter;
           cmd.Parameters.Add(new SqlParameter(paramName, str);
           selectStatement.Append("compname  like '%" + paramName + "%' or ProductCategory like '%" + paramName  + "%' or CountryName like '%" + paramName  + "%'")
        }
        selectStatement.Append(")");
    }
    cmd.CommandText = selectStatement.ToString();
}

Answer 4

public void bind()
{
    dt = g1.return_dt("select * from tb1 where Name='" + TextBox1.Text.Text + "' or compname='"+TextBox1.Text+"'  order by  compname ");
    if (dt.Rows.Count > 0)
    {
        adsource = new PagedDataSource();
        adsource.DataSource = dt.DefaultView;
        adsource.PageSize = 10;
        adsource.AllowPaging = true;
        adsource.CurrentPageIndex = pos;
        btnfirst.Enabled = !adsource.IsFirstPage;
        btnprevious.Enabled = !adsource.IsFirstPage;
        btnlast.Enabled = !adsource.IsLastPage;
        btnnext.Enabled = !adsource.IsLastPage;
        GridView1.DataSource = adsource;
        GridView1.DataBind();
     }
     else
     {
         GridView1.DataSource = null;
         GridView1.DataBind();
     }
}

you can also use like query in it

dt = g1.return_dt("select * from tb1 where Name like '%" + TextBox1.Text + "%' or compname like '%" + TextBox1.Text + "%' ");

Answer 5

The query should be like this for this query-

Session["Name"]=string.Format("and compname like '%{0}%' or ProductCategory like '%{0}%' or CountryName like '%{0}%'",TextBox1.Text);