Search a table in sql server via single text box

Question

I have a table in sql server database and have some columns. I want to search it via a text box and here is the scenario: For example,there are 3 columns named: " name , number , code" I want to type number 88 in the text box and after hitting search button,Every row that have a cell containing 88 will be shown to me in grid view.(and the 88 is a part of that cell,for example,there is a cell with 2256887 in it,and I want that cell to be shown) I tried to solve,but there are two problems.first is: I can only search one column at a time,and second,if I even type 225688 , it will not show me anything until I type the whole 2256887 in the text box.
Here is the code in aspx page:

<asp:Label ID="Label1" runat="server" Text="متن ورودی برای جستجو:"></asp:Label>

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

<asp:Button ID="btnSearch" runat="server" Text="search" OnClick="btnSearch_Click" />

<asp:GridView ID="searchresults" runat="server"></asp:GridView>

and the code in page cs:

using System.Data;
using System.Data.SqlClient;

protected void btnSearch_Click(object sender, EventArgs e)
    {
        String strConn = "Data Source=DESKTOP-MQ1PNVA\\SQLEXPRESS;Initial Catalog=linkfinderdb;Integrated Security=True";
        SqlConnection conn = new SqlConnection(strConn);
        conn.Open();
        SqlCommand cmd = new SqlCommand("Select * FROM linktest WHERE phone=@txtSearch", conn);

        try
        {

            SqlParameter search = new SqlParameter();
            search.ParameterName = "@txtSearch";
            search.Value = txtSearch.Text.Trim();

            cmd.Parameters.Add(search);
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);

            searchresults.DataSource = dt;
            searchresults.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

phone is a column name in table.
Thanks in advance.

You are using = in your SQL query, you want to use LIKE. – Keith Jul 03 '17 at 09:27 — Keith, Jul 03 '17 at 09:27

RN92 · Answer 1 · 2017-07-03T10:45:54.457

0

Try using LIKE with OR condition.

Eg : if your column names are name,phone and code,

 String strConn = "Data Source=DESKTOP-MQ1PNVA\\SQLEXPRESS;Initial Catalog=linkfinderdb;Integrated Security=True";
    SqlConnection conn = new SqlConnection(strConn);
    conn.Open();

    string CommandText= "Select * FROM linktest WHERE phone LIKE @txtSearch OR name LIKE @txtSearch OR code LIKE @txtSearch";

    try
    {
        string searchTerm = string.Format("%{0}%", txtSearch.Text.Trim());
        Command = new SqlCommand(CommandText, conn);
        Command.Parameters.Add(new SqlParameter("@txtSearch", searchTerm));
        SqlDataReader dr = Command.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(dr);

        searchresults.DataSource = dt;
        searchresults.DataBind();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        conn.Close();
    }

edited Jul 03 '17 at 10:45

answered Jul 03 '17 at 09:31

RN92

1,380
1
13
32

Thanks.But I received error: Incorrect syntax near '@txtSearch'. I have just replaced your code line with mine – Farzadj Jul 03 '17 at 10:03
At which point do you get the error? Is it at `SqlDataReader dr = cmd.ExecuteReader();` – RN92 Jul 03 '17 at 10:11
Try this `SqlCommand cmd = new SqlCommand("Select * FROM linktest WHERE phone LIKE @txtSearch OR name LIKE @txtSearch OR code LIKE @txtSearch", conn);` – RN92 Jul 03 '17 at 10:13
When I hit search,I see the error abov the text box. I deleted the ' ' around txtSearch and error gone,but there is a big problem remaining. I have to type whole text of a cell to find that and show the row. For example, There are 4 rows with this 4 codes in it: 22336541 , 22889654 , 77558847 , 66332588. I want to type 88 in text box and see all 4 rows containing 88 in their code cell, But now I have to type 66332588 to see that row. How can I make this ok? – Farzadj Jul 03 '17 at 10:15
Use wildcard option, refer the answer of this [link](https://stackoverflow.com/questions/19730941/how-to-use-wildcards-in-sql-query-with-parameters) please – RN92 Jul 03 '17 at 10:19
Using wildcard, I received error "Incorrect syntax near '@txtSearch'." again – Farzadj Jul 03 '17 at 10:22
Try the edited code, hope this one will work for you. – RN92 Jul 03 '17 at 10:46

Search a table in sql server via single text box

1 Answers1