sql question, LIKE search using variable RSS

• • Reply
  

  zin

  Member

  5 Points

  1 Post

  sql question, LIKE search using variable

  May 05, 2003 05:36 AM|LINK

  How can i do a LIKE search specified by the variable in text box??? For example, the user types a search word in the text box. The word is stored in variable @text. Now i want to perform a LIKE search using that variable. here's my sql statement and part of my code: myComm.CommandText = "SELECT firstName AS 'First Name' FROM Customer WHERE firstName LIKE '@text%' " myComm.Parameters.Add("@text", txtSearch.Text) myConn.Open() Dim myReader As SqlClient.SqlDataReader = myComm.ExecuteReader() DataGrid1.DataSource = myReader DataGrid1.DataBind() myConn.Close() I have a customer called 'David' in my database. When i searched for 'Dav' it doesn't return any rows. If i use myComm.CommandText = "SELECT firstName AS 'First Name' FROM Customer WHERE firstName = @text " , it works fine. Looks like the LIKE statement is wrong. Thanks for any help.
• • Reply
  

  Ken Schaefer

  Participant

  2000 Points

  400 Posts

  ASPInsiders

  MVP

  Re: sql question, LIKE search using variable

  May 05, 2003 06:50 AM|LINK

  IIRC: myComm.CommandText = "SELECT firstName AS 'First Name' FROM Customer WHERE firstName LIKE @text + '%' " Cheers Ken
• • Reply
  

  Pierre

  Participant

  1405 Points

  289 Posts

  Re: sql question, LIKE search using variable

  May 05, 2003 11:17 PM|LINK

  Whoa - sorry - my mistake.... Ignore me
• • Reply
  

  charon319

  Member

  5 Points

  1 Post

  Re: sql question, LIKE search using variable

  Aug 01, 2003 06:50 PM|LINK

  The correction: myComm.CommandText = "SELECT firstName AS 'First Name' FROM Customer WHERE firstName LIKE '" & txtSearch.Text & "%'" '- you don't need this next line. '- you'd use @text only when calling a stored procedure, but in this case, you are ' using embedded SQL. 'myComm.Parameters.Add("@text", txtSearch.Text) myConn.Open() Dim myReader As SqlClient.SqlDataReader = myComm.ExecuteReader() DataGrid1.DataSource = myReader DataGrid1.DataBind() myConn.Close()
• • Reply
  

  joteke

  All-Star

  46284 Points

  6896 Posts

  ASPInsiders

  MVP

  Re: sql question, LIKE search using variable

  Aug 01, 2003 07:01 PM|LINK

  Bit old thread, huh? Using named parameter in embedded SQL is completely valid as was demonstrated. It is not limited to stored procedures. In fact, if you want to avoid SQL injection, it is pretty recommendable to use parameters and not to use string concatenation.
  Thanks,
  
  Teemu Keiski
  Finland, EU