sql commnd query strings RSS

• • Reply
  

  darksyfer

  Member

  17 Points

  37 Posts

  sql commnd query strings

  Apr 29, 2012 01:35 PM|LINK

  hi everyone. can someone tell me whether the sql command is right because i get a blank gridview when i click the search button. The button was supposed to search the keywords entered in a textbox from 2 tables using wildcards. thank you in advance

  Private Sub bnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bnSearch.Click
          Dim myConnection As New SqlConnection(myConStr)
          Dim mySqlCommand As New SqlCommand()
          Dim mySqlDataReader As SqlDataReader
          Dim keywords As String
          Dim sqlStr As String
  
          keywords = "'%'" & txtSearch.Text
          sqlStr = "SELECT tblBooks.fldTitle, tblBooks.fldAuthor, tblBooks.fldISBN, tblBooks.fldNoCopies, tblBooks.fldInStock, tblCat.fldCatName FROM tblBooks INNER JOIN tblCat ON tblBooks.fldCatID = tblCat.fldCatID WHERE (fldTitle LIKE @keywords) AND (fldAuthor LIKE @keywords) AND (fldCatName LIKE @keywords) AND (fldISBN LIKE @keywords)ORDER BY tblBooks.fldTitle"
  
          mySqlCommand.Parameters.AddWithValue("@keywords", keywords)
          mySqlCommand.Connection = myConnection
          mySqlCommand.CommandText = sqlStr
          myConnection.Open()
          mySqlDataReader = mySqlCommand.ExecuteReader
          gvBookList.DataSource = mySqlDataReader
          gvBookList.DataBind()
      End Sub

  
  
  

• • Reply
  

  silvioyf

  Participant

  1754 Points

  340 Posts

  Re: sql commnd query strings

  Apr 29, 2012 02:46 PM|LINK

  Hi,

  Try:

  keywords = "%" & txtSearch.Text & "%"

  Hope this helps.

   

  www.imoveisemexposicao.com.br
  • Marked as answer by darksyfer on Apr 30, 2012 04:34 PM
• • Reply
  

  Shoaib Rashe...

  Member

  4 Points

  3 Posts

  Re: sql commnd query strings

  Apr 29, 2012 02:58 PM|LINK

  You try with like  %youText%

• • Reply
  

  limno

  All-Star

  117336 Points

  8003 Posts

  Moderator

  MVP

  Re: sql commnd query strings

  Apr 29, 2012 06:31 PM|LINK

  keywords =  txtSearch.Text
  sqlStr = "SELECT tblBooks.fldTitle, tblBooks.fldAuthor, tblBooks.fldISBN, tblBooks.fldNoCopies, tblBooks.fldInStock,
  		tblCat.fldCatName FROM tblBooks INNER JOIN tblCat ON tblBooks.fldCatID = tblCat.fldCatID
  		WHERE ((fldTitle LIKE '%'+ @keywords+'%')
  		Or (fldAuthor LIKE '%'+ @keywords+'%')
  Or (fldCatName LIKE '%'+ @keywords+'%')
  Or (fldISBN LIKE '%'+ @keywords+'%' )
   ) Or (@keywords IS NULL) )
   ORDER BY tblBooks.fldTitle
   "

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm