locked
Search bar function RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    I have the below code which I use within a search bar function using a textbox and a button. The code below works to an extent but only seems to be searching for the first word of the Client Name. I would like the search bar to search for any word within the Client Name that has been entered into the search textbox.

    Please see below my code.

    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
    String querysrch;
    sqlConnection.Open();
    querysrch = "SELECT c.Client_Name AS 'Client Name', c.Tel_No AS 'Tel No', esl.Env_Service_Level AS 'Env Service Level', hssl.HS_Service_Level AS 'H&S Service Level', hrsl.HR_Service_Level AS 'HR Service Level', qsl.Qual_Service_Level AS 'Quality Serice Level', c.Primary_Contact AS 'Primary Contact', c.health_and_Safety_Consultant1 AS 'H&S Consultant 1', c.Environmental_Consultant AS 'Env Consultant', c.Quality_Consultant AS 'Quality Consultant', c.ID FROM Customer c " +
    "LEFT JOIN EnvironmentalServiceLevels esl ON (c.Env_Service_Level = esl.ID) " +
    "LEFT JOIN HealthandSafetyServiceLevels hssl ON (c.HS_Service_Level = hssl.ID) " +
    "LEFT JOIN HumanResourcesServiceLevels hrsl ON (c.HR_Service_Level = hrsl.ID) " +
    "LEFT JOIN QualityServiceLevels qsl ON (c.Qual_Service_Level = qsl.ID) " +
    "WHERE (Client_Name like @keyword) " +
    "AND ((health_and_Safety_Consultant1 like @keyword " +
    "OR health_and_Safety_Consultant2 like @keyword " +
    "OR health_and_Safety_Consultant3 = @keyword " +
    "OR health_and_Safety_Consultant4 = @keyword " +
    "OR Human_Resources_Consultant = @jobRole " +
    "OR Environmental_Consultant = @keyword " +
    "OR Quality_Consultant = @keyword " +
    "OR Admin_Team = @adminTeam) " +
    "AND Crownford_Status = '1')";
    cmd = new SqlCommand(querysrch, sqlConnection);
    da = new SqlDataAdapter(cmd);
    cmd.Parameters.AddWithValue("@keyword", txtSearch.Text + "%");
    cmd.Parameters.AddWithValue("@userName", Session["userName"]);
    cmd.Parameters.AddWithValue("@jobRole", Session["jobRole"]);
    cmd.Parameters.AddWithValue("@adminTeam", sessionUser.HasRole("ROLE_ADMIN"));
    dt.Clear();
    da.Fill(dt);

    if (dt.Rows.Count > 0)
    {
    CustomerGridView.DataSource = dt;
    CustomerGridView.DataBind();
    }

    else
    {
    ClientScript.RegisterStartupScript(typeof(Page), "Live Client", "<script>alert('Unable to find Client');</script>");
    }

    sqlConnection.Close();
    }

    Any help is greatly appreciated.

    Many thanks

    Jonny

    Friday, March 2, 2018 12:10 PM

Answers

  • User475983607 posted

    The SQL syntax is 

    WHERE field LIKE '%WordToFind%'

    See the T-SQL documentation.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 2, 2018 12:45 PM
  • User409696431 posted

    You are missing the opening % in your query parameter value.  The way you have it, it means "match the text, and anything after", which means the matched text must be at the beginning.  You want "match anything before the matched text and anything after the matched text".

    Change:

    cmd.Parameters.AddWithValue("@keyword", txtSearch.Text + "%");

    To:

    cmd.Parameters.AddWithValue("@keyword", "%" + txtSearch.Text + "%");

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 2, 2018 8:52 PM

All replies

  • User475983607 posted

    The SQL syntax is 

    WHERE field LIKE '%WordToFind%'

    See the T-SQL documentation.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 2, 2018 12:45 PM
  • User409696431 posted

    You are missing the opening % in your query parameter value.  The way you have it, it means "match the text, and anything after", which means the matched text must be at the beginning.  You want "match anything before the matched text and anything after the matched text".

    Change:

    cmd.Parameters.AddWithValue("@keyword", txtSearch.Text + "%");

    To:

    cmd.Parameters.AddWithValue("@keyword", "%" + txtSearch.Text + "%");

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 2, 2018 8:52 PM
  • User-1838255255 posted

    Hi jonnygareth30,

    According to your description, i think you could use sql like keyword to search something, then to realize your needs. Please check the following tutorials:

    SQL LIKE Operator: 

    https://www.w3schools.com/sql/sql_like.asp  

    LIKE (Transact-SQL): 

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql 

    Best Regards,

    Eric Du 

    Wednesday, March 7, 2018 10:13 AM
  • User-1901014284 posted

    Hi all,

    Thank you for all your responses, the information has been of great help and helped me resolve my issue.

    Thanks again.

    Jonny

    Wednesday, March 7, 2018 11:44 AM