Chris__T
Posts: 339
|
| Posted: 12/05/2008, 2:00 PM |
|
I had a search form with one textbox where you added the words you wanted to search on (in this case, last names). The code below broke up the textbox string into individual search words and added it to the sql query, so if I had 4 last names (separated by a space) in the search field, it brought up these four names (and their respective data in the grid below the search field.)
It's kind of like a web search, where you enter all the words on one line, and it searches on each word (or something like that)
dim term
dim x
dim clause
dim Lnamestring
dim keywords
dim temp
dim tempe
Lnamestring = CCGetFromGet("s_Job_Site", Empty)
Lnamestring = Lnamestring & " ."
If Len(Lnamestring) > 0 Then
term = Trim(Lnamestring)
term = Replace(term,"'","' '")
If InStr(term," ") > 0 Then
term = Split(term," ")
'temp = Ubound(term)
'tempe = temp + 1
'term(4) = "."
For x=0 to Ubound(term)
If x > 0 Then
clause = clause & " OR"
End If
clause = clause & " LName LIKE '%" & term(x) & "%'"
Next
Else
clause = " LName LIKE '%" & term & "%'"
End If
'sql = " WHERE " & clause
Employee2.DataSource.Where = Employee2.DataSource.Where & clause
'sql = "SELECT * FROM location WHERE" & clause
'Response.Write sql
End If
I want to modify this code to be able to use the search string, break it up, and search on more than one field in the sql query. So if someone types in "Jones White Car" in search field, it will break those up into three different search fields "Jones" "White" "Car" and search through LastName field, Description field, Location field, etc...
Haven't been able to figure this out yet.
|
 |
 |
Chris__T
Posts: 339
|
| Posted: 12/09/2008, 7:56 AM |
|
Also tried this code:
dim keywords(20)
dim Lnamestring
dim tempy
dim search_columns(4)
search_columns(0)="Permit_No"
search_columns(1)="Job_Site"
search_columns(2)="Project_Type"
search_columns(3)="Contractor_Name"
search_columns(4)="Contractor_Firm_Name"
Lnamestring = CCGetFromGet("s_Job_Site", Empty)
tempy = trim(Lnamestring)
keywords = split(tempy)
Permit1.Datasource.Where = " "
dim p
p = 0
Permit1.DataSource.Where = Permit1.DataSource.Where & "("
dim word
dim m
dim column
for each word in keywords
if (len(word) > 2) then
if (p <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & ") AND ("
p=1
m=0
end if
for each column in search_columns
if (m <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & " OR "
Permit1.DataSource.Where = Permit1.DataSource.Where & " search_columns(column) LIKE '%keywords(word)%' "
m=1
end if
next 'end foreach column
end if
next ' foreach word
Permit1.DataSource.Where = Permit1.DataSource.Where & ")"
It doesn't work either. Also just gives me a blank white webpage so there is some malformed code I need to look at as well
|
|
|
|
Oper
Posts: 1195
|
| Posted: 12/09/2008, 11:22 AM |
|
for each word in keywords
if (len(word) > 2) then
if (p <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & ") AND ("
p=1
m=0
end if
for each column in search_columns
if (m <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & " OR "
end if
Permit1.DataSource.Where = Permit1.DataSource.Where & " search_columns(column) LIKE '%keywords(word)%' "
m=1
next 'end foreach column
end if
next ' foreach word
Permit1.DataSource.Where = Permit1.DataSource.Where & ")"
Check the small change on the if
Try this also for easy help could you please
add this at the bottom of that code:
response.write "---------<br>"
response.write Permit1.DataSource.Where
response.end
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/09/2008, 1:50 PM |
|
Ok, did the small change to the if statement
I also added the code you wanted at the end of the code.
Try it, and it loads a blank white page. So I guess something in my code is malformed.
|
|
|
|
Oper
Posts: 1195
|
| Posted: 12/10/2008, 5:26 PM |
|
let isloated some step
at the begining of teh script
add this:
response.write "FACE 1...............<br>"
in soem other place put
response.write "FACE 2...............<br>"
but keep the first code i added at teh end.
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/11/2008, 6:30 AM |
|
Thanks for helping me out, Oper.
Ok, so I added these two new lines....close to the beginning and they showed up.
Then started adding another line (Face 3.......) until it wouldn't show up.
It appears keywords= split(tempy) is my problem line
I thought that was a valid line. I defined keywords at the beginning as an array. Then wouldn't keywords = split(tempy) stick it into the array? Or would I need a for loop to do that?
|
|
|
|
Oper
Posts: 1195
|
| Posted: 12/13/2008, 5:41 AM |
|
Chrish got the mistake, will post when i got back home
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Oper
Posts: 1195
|
| Posted: 12/13/2008, 1:46 PM |
|
Code Working "Tested".
Test Link: http://www.7bz.com/code-charge-studio-sample/basic/tc.asp
<%
Response.write "Chris Sample:<br><br>"
dim keywords
dim Lnamestring
dim tempy
dim search_columns(4)
search_columns(0)="Permit_No"
search_columns(1)="Job_Site"
search_columns(2)="Project_Type"
search_columns(3)="Contractor_Name"
search_columns(4)="Contractor_Firm_Name"
Lnamestring = "Word1 Word2 Word3" 'Force for the sample
tempy = trim(Lnamestring)
keywords = split(tempy," ")
TWhere = " "
dim p
p = 0
TWhere = TWhere & "("
dim m
dim column
for Each word In keywords
' response.write "."
if (len(word) > 2) then
if (p <> 0) then
Twhere = Twhere & ") AND<br> (" '<br> Inserted here just for Debug Print, Remove before use
end if
p=1
m=0
for each column in search_columns
' response.write "*"
if (m <> 0) then
Twhere = Twhere & " OR "
end if
Twhere = Twhere & " " & column & " LIKE '%" & word & "%' "
m=1
next 'end foreach column
end if
next ' foreach word
Twhere = Twhere & ")"
response.write tWhere
%>
but maybe the logic required OR insted of the AND
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/15/2008, 1:59 PM |
|
thanks Oper. I'm going to take a look at it tomorrow when I have time and try to incorporate it into my code and see how it works.
|
|
|
|
Oper
Posts: 1195
|
| Posted: 12/18/2008, 7:35 AM |
|
Resolved or Pending?
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/18/2008, 12:38 PM |
|
Pending. I have to hold while I convert my database to mysql. MS Access wasn't very accomodating :)
|
|
|
|
Oper
Posts: 1195
|
| Posted: 12/19/2008, 4:06 PM |
|
The Mysql Tool have one nice soft for the convertion
but not the Views/Querys
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
