LIKE statement in ASP.net

Lowyat.NET Lowyat.NET Rules and Regulations

FAQ Help Search Members Calendar

Outline · [ Standard ] · Linear+

LIKE statement in ASP.net, in a parameterized query

Share on

| Track this topic | Email this topic | Print this topic

jason83

Jul 1 2009, 02:34 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #1

On my way

Group: Senior Member
Posts: 598
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003

hi all,

its me again.

this time i have a sql question for my search page. I am using a loop below to search the terms entered, as below

CODE

For Each strSearchTerm In strSearchTerms
If strSQL <> "" Then
strSQL = strSQL + " OR "
Else
strSQL = "Title LIKE '%" + txtSearchTerm.Text + "%' OR "
End If
strSQL = strSQL + "Title LIKE '%" + strSearchTerm + "%'"
Next

where strSearchTerm will have the terms entered by user e.g. "Conan the Barbarian". What it does is to put in "Conan", "the" and "Barbarian" into strSearchTerms

my query is something like (and it used to work)

CODE

SQLString = "Select * From Movie WHERE (" & strSQL & ")"

then i try to change it to parameterized query,

CODE

SQLString = "Select * From Movie WHERE (@strSQL)"

and i add the paremeter as follows

CODE

SQLCommand..Parameters.Add(New MySqlParameter("@strSQL", strSQL))

and it doesnt work anymore. the problem is to use %, you gotta add in at the parameter itself, please refer to example
http://aspnet101.com/aspnet101/tutorials.aspx?id=10

How do i apply the above method to my parameterized query ya??

unrealweapon

Jul 1 2009, 08:34 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #2

\(^0^)/

Group: Senior Member
Posts: 571
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2008
From: a box within a box

it wont work this way..

build your query from the loop and add to

CODE

SQLString = "Select * From Movie WHERE (" & strSQL & ")"

Parameterized query only work like this

it does not work on

CODE

select * from table where @asdf

this is syntax error.

therefore u need some kind of loop count such as

CODE

count=1
For Each strSearchTerm In strSearchTerms
If strSQL <> "" Then
strSQL = strSQL + " OR "
Else
strSQL = "Title LIKE '%@test " + count + "%' OR "
End If
strSQL = strSQL + "Title LIKE '%@test " + count + "%' OR "
count=count+1
Next

and add to

CODE

"select * from table where " + strSQL

after that u add the parameter by loop since you already know it starts with 1 and ends with count.

jason83

Jul 2 2009, 12:59 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #3

On my way

Group: Senior Member
Posts: 598
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003

hey thank you si fu!!!!!

thats a great great idea!! ill try to implement to my code. thanks again!!!

Added on July 2, 2009, 1:34 amerrr sifu, doesnt work ler...

show you my full code

CODE

Dim strSearchTerms As String() = txtSearchTerm.Text.Split(" ")
Dim strSearchTerm As String
Dim strSQL As String = ""
Dim intCount As Integer = 1

For Each strSearchTerm In strSearchTerms
If strSQL <> "" Then
strSQL = strSQL & " OR "
Else
strSQL = "Review.ReviewTitle LIKE '@MainSearchTerm' OR "
End If
strSQL = strSQL & "Review.ReviewTitle LIKE '@SearchTerm" & intCount & "'"
intCount = intCount + 1
Next

SQLCommandString = "SELECT * FROM Review, User WHERE (" & strSQL & ") AND Review.ReviewUser = User.UserEmail AND Review.ReviewStatus = '1'"

With SQLCommand
.Connection = SQLConnection
.CommandType = CommandType.Text
.CommandText = SQLCommandString
.Parameters.Clear()
.Parameters.Add(New MySqlParameter("@MainSearchTerm", "%" & txtSearchTerm.Text & "%"))
End With

intCount = 1
For Each strSearchTerm In strSearchTerms
SQLCommand.Parameters.Add(New MySqlParameter("@SearchTerm" & intCount, "%" & strSearchTerm & "%"))
intCount = intCount + 1
Next

what missing?

This post has been edited by jason83: Jul 2 2009, 01:34 AM

unrealweapon

Jul 2 2009, 08:41 AM

Show posts by this member only |This post's rating (0+, 0-) | Post #4

\(^0^)/

Group: Senior Member
Posts: 571
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2008
From: a box within a box

QUOTE(jason83 @ Jul 2 2009, 12:59 AM)

hey thank you si fu!!!!!

thats a great great idea!! ill try to implement to my code. thanks again!!!

Added on July 2, 2009, 1:34 amerrr sifu, doesnt work ler...

show you my full code

CODE

Dim strSearchTerms As String() = txtSearchTerm.Text.Split(" ")
Dim strSearchTerm As String
Dim strSQL As String = ""
Dim intCount As Integer = 1

For Each strSearchTerm In strSearchTerms
If strSQL <> "" Then
strSQL = strSQL & " OR "
Else
strSQL = "Review.ReviewTitle LIKE '@MainSearchTerm' OR "
End If
strSQL = strSQL & "Review.ReviewTitle LIKE '@SearchTerm" & intCount & "'"
intCount = intCount + 1
Next

SQLCommandString = "SELECT * FROM Review, User WHERE (" & strSQL & ") AND Review.ReviewUser = User.UserEmail AND Review.ReviewStatus = '1'"

With SQLCommand
.Connection = SQLConnection
.CommandType = CommandType.Text
.CommandText = SQLCommandString
.Parameters.Clear()
.Parameters.Add(New MySqlParameter("@MainSearchTerm", "%" & txtSearchTerm.Text & "%"))
End With

intCount = 1
For Each strSearchTerm In strSearchTerms
SQLCommand.Parameters.Add(New MySqlParameter("@SearchTerm" & intCount, "%" & strSearchTerm & "%"))
intCount = intCount + 1
Next

what missing?

u will get

SELECT * FROM Review, User WHERE (Review.ReviewTitle LIKE '@MainSearchTerm' OR Review.ReviewTitle LIKE '@SearchTerm1' OR Review.ReviewTitle LIKE '@SearchTerm2' OR Review.ReviewTitle LIKE '@SearchTerm3') AND Review.ReviewUser = User.UserEmail AND Review.ReviewStatus = '1'

for parameter, u do not need ' ', but u need to specify what parameter type it's, string/interger/decimal...

jason83

Jul 2 2009, 06:59 PM

Show posts by this member only |This post's rating (0+, 0-) | Post #5

On my way

Group: Senior Member
Posts: 598
Ratings earned: 0+, 0-
Ratings given: 0+, 0-

Joined: Jan 2003

QUOTE(unrealweapon @ Jul 2 2009, 08:41 AM)

u will get

SELECT * FROM Review, User WHERE (Review.ReviewTitle LIKE '@MainSearchTerm' OR Review.ReviewTitle LIKE '@SearchTerm1' OR Review.ReviewTitle LIKE '@SearchTerm2' OR Review.ReviewTitle LIKE '@SearchTerm3') AND Review.ReviewUser = User.UserEmail AND Review.ReviewStatus = '1'

for parameter, u do not need ' ', but u need to specify what parameter type it's, string/interger/decimal...

thanks bro, it works after i removed the ' ' as adviced by you

thanks a lot!!

« Next Oldest · Codemasters · Next Newest »