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
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
"
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 Subsilvioyf
Participant
1754 Points
340 Posts
Re: sql commnd query strings
Apr 29, 2012 02:46 PM|LINK
Hi,
Try:
Hope this helps.
Shoaib Rashe...
Member
4 Points
3 Posts
Re: sql commnd query strings
Apr 29, 2012 02:58 PM|LINK
You try with like %youText%
limno
All-Star
117336 Points
8003 Posts
Moderator
MVP
Re: sql commnd query strings
Apr 29, 2012 06:31 PM|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm