ASP Split and SQL Select

[Scorpion657]

Hey guys, I am really new at this and I am trying to do the following:

I need to split keywords entered into search box eg. if someone enters 'Research Design' in, it needs to split the words and then build an Sql query that will give me the following output :

SELECT [ProjectName], [ProjectID]
FROM [ProjectList]
WHERE ([ProjectName] LIKE '%' + 'Research' + '%') AND ([ProjectName] LIKE '%' + 'Design' + '%'

I figured I would use this method as a basic database search engine.

Thanks guys.

[DrBunchman]

Hi Scorpion, you don't say but I presume that you have already written the code to open the database connection and display your records etc? If not then there are lots of tutorials on the web and you can always come back here to ask any questions.

You can split the search string using the following bit of code as an example. It will create an array from the search string and then build the sql string from it.

<%
'Define your variables
Dim sSearchString
Dim sSQL
Dim i
Dim sSearchArray

'Get the value of the search box (I've called it sInput)
sSearchString = Request("sInput")

'Split the search string into separate words (The split function splits the string at the specified character - in this case " ")
sSearchArray= Split(sSearchString , " ")

'Build the first part of the sql string
sSQL = " SELECT [ProjectName], [ProjectID] FROM [ProjectList] "

'Add the first search word to the sql string
If UBound(sSearchArray) > 0 Then
sSQL = sSQL & " WHERE ([ProjectName] LIKE %" & sSearchArray(0) & "%) "
End If

'Add the rest of the words
If UBound(sSearchArray) > 1 Then
For i = 1 To UBound(sSearchArray)
sSQL = sSQL & " AND ([ProjectName] LIKE %" & sSearchArray(i) & "%) "
Next
End If

%>

Let me know how it goes or if you need any more help,

Dr B

[Scorpion657]

Thanks a lot Dr. B for your help. Now when I try to display the data into a gridview, my guess was that I would need to create a dataset and fill it with the results and later bind it to the gridview. I tried the following but it did not work:

Dim sSearchString
Dim myConn As New Data.SqlClient.SqlConnection
myConn.ConnectionString = "Data Source=servername;Initial Catalog=databasename;Integrated Security=True"
myConn.Open()

Dim sSQL
Dim i
Dim sSearchArray
Dim ds As New DataSet


'Get the value of the search box (I've called it sInput)
sSearchString = Request.Cookies("search").Value

'Split the search string into separate words (The split function splits the string at the specified character - in this case " ")
sSearchArray = Split(sSearchString, " ")

'Build the first part of the sql string
sSQL = " SELECT [ProjectName], [ProjectID] FROM [ProjectList] "

'Add the first search word to the sql string
If UBound(sSearchArray) > 0 Then
sSQL = sSQL & " WHERE ([ProjectName] LIKE %" & sSearchArray(0) & "%) "
End If

'Add the rest of the words
If UBound(sSearchArray) > 1 Then
For i = 1 To UBound(sSearchArray)
sSQL = sSQL & " AND ([ProjectName] LIKE %" & sSearchArray(i) & "%) "
Next
End If

Dim ad As New Data.SqlClient.SqlDataAdapter(sSQL, myConn)
ad.Fill(ds)

GridView1.DataSource = ds
GridView1.DataBind()


myConn.Close()


I'm obviously doing something wrong there but I can't seem to figure out what.

[Scorpion657]

There was some " ' " missing before the % that's why it was giving me an error.

Now For some reason, it worked but with some minor mistakes. Here is what it does:

When the search box has exactly 1 word:
sSQL = " SELECT [ProjectName], [ProjectID] FROM [ProjectList] "

instead of:

sSQL = sSQL & " WHERE ([ProjectName] LIKE '%" & sSearchArray(0) & "%') "

When the search box has exaclty 2 words:
sSQL = sSQL & " WHERE ([ProjectName] LIKE '%" & sSearchArray(0) & "%') "

instead of:

sSQL = sSQL & " AND ([ProjectName] LIKE '%" & sSearchArray(i) & "%') "

For 3 or more words, it work fine.

I dont seem to see what's wrong ??

This is the code:

<%
'Define your variables
Dim myConn As New Data.SqlClient.SqlConnection
Dim ds As New DataSet
Dim sSearchString
Dim sSQL
Dim i
Dim sSearchArray

'Get the value of the search box (I've called it sInput)
sSearchString = Request.Cookies("search").Value

'Split the search string into separate words (The split function splits the string at the specified character - in this case " ")
sSearchArray = Split(sSearchString, " ")

'Build the first part of the sql string
sSQL = " SELECT [ProjectName], [ProjectID] FROM [ProjectList] "

'Add the first search word to the sql string
If UBound(sSearchArray) > 0 Then

sSQL = sSQL & " WHERE ([ProjectName] LIKE '%" & sSearchArray(0) & "%') "

End If

'Add the rest of the words
If UBound(sSearchArray) > 1 Then
For i = 1 To UBound(sSearchArray)
sSQL = sSQL & " AND ([ProjectName] LIKE '%" & sSearchArray(i) & "%') "
Next
End If


myConn.ConnectionString = "Data Source=servername;Initial Catalog=databasename;Integrated Security=True"
myConn.Open()


Dim ad As New Data.SqlClient.SqlDataAdapter(sSQL, myConn)
ad.Fill(ds)
results.DataSource = ds
results.DataBind()

myConn.Close()


End Sub
%>

[DrBunchman]

Scorpion, are you using classic ASP or ASP.NET ? This is a classic ASP forum and my initial answer to you was on that basis - if you're using .NET then we should change the code to reflect that.

Dr B

[Scorpion657]

You are correct. I am using ASP.NET. I think I found a trick that makes it work just fine.

If Ubound(sSearchArray) + 1 > 0 then
....

End If.

That works for now. If there is a better way, please let me know. Otherwise, Thanks a whole lot.

Scorpion, are you using classic ASP or ASP.NET ? This is a classic ASP forum and my initial answer to you was on that basis - if you're using .NET then we should change the code to reflect that.

Dr B

[DrBunchman]

Glad you got it sorted. If you have any more questions then post them in the .NET forum and the experts in there will be able to give you better advice.

As the script I gave you above was written for classic ASP there are a few things you can change to update it to ASP.NET.

Things like changing sSQL = sSQL & to sSQL +=

And sSearchArray = Split(sSearchString, " ") to sSearchArray = sSearchString.Split(" ")

Amongst other little things. Good luck with the project.

Dr B