SQL LIKE% NOT SEARCHING

I want to do a simple search using the SQL LIKE function. Unfortunately, for some reason, it doesn't seem to be working. Below is my code.

private void gvbind() { connection.Open(); string sql = ""; if (txtSearch.Text.Trim() == "") { sql = "SELECT a.cname,[bid],b.[bname],b.[baddress],b.[bcity],b.[bstate],b.[bpostcode],b.[bphone],b.[bfax],b.[bemail] FROM [CLIENT] a INNER JOIN [BRANCH] b ON a.clientID=b.clientID ORDER BY a.[clientID]"; } else { sql = "SELECT a.cname,[bid],b.[bname],b.[baddress],b.[bcity],b.[bstate],b.[bpostcode],b.[bphone],b.[bfax],b.[bemail] FROM [CLIENT] a INNER JOIN [BRANCH] b ON a.clientID=b.clientID WHERE b.[bname] LIKE '%@search%' ORDER BY a.[clientID]"; } SqlCommand cmd = new SqlCommand(sql,connection); cmd.Parameters.AddWithValue("@search", txtSearch.Text.Trim()); cmd.CommandType = CommandType.Text; SqlDataAdapter adp = new SqlDataAdapter(); adp.SelectCommand = cmd; DataSet ds = new DataSet(); adp.Fill(ds); connection.Close(); if (ds.Tables[0].Rows.Count > 0) { gvBranch.Enabled = true; gvBranch.DataSource = ds; gvBranch.DataBind(); } else { ds.Tables[0].Rows.Add(ds.Tables[0].NewRow()); ds.Tables[0].Rows.Add(ds.Tables[0].NewRow()); gvBranch.DataSource = ds; gvBranch.DataBind(); int columncount = gvBranch.Rows[0].Cells.Count; gvBranch.Rows[0].Cells.Clear(); gvBranch.Rows[0].Cells.Add(new TableCell()); gvBranch.Rows[0].Cells[0].ColumnSpan = columncount; gvBranch.Rows[0].Cells[0].Text = "No Records Found"; } ds.Dispose(); } 

the specified method is called in the Page_Load () method using

 if((!Page.IsPostBack)) { gvBind(); } 

it is called when the button is pressed. However, it returns No entry when I perform a search.

+4
source share
1 answer

Use

 LIKE '%' + @search + '%' 

instead

 LIKE '%@search%' 

Full request;

 ... else { sql = "SELECT a.cname,[bid],b.[bname],b.[baddress],b.[bcity],b.[bstate],b.[bpostcode],b.[bphone],b.[bfax],b.[bemail] FROM [CLIENT] a INNER JOIN [BRANCH] b ON a.clientID=b.clientID WHERE b.[bname] LIKE '%' + @search + '%' ORDER BY a.[clientID]"; } 

And in fact, you do not need to use square brackets ( [] ) each column in your query. Use them if your identifiers or object names are a reserved keyword .

Thanks. It works, but any explanation for this?

The main problem is that your query parameter is inside quotation marks. In quotation marks, SQL Server recognizes it as a string literal , and never sees it as a parameter.

+9
source

Source: https://habr.com/ru/post/1500669/


All Articles