Chris__T
Posts: 339
|
Posted: 05/06/2008, 9:15 AM |
|
How can I search for multiple names to bring them up in editable grid? If you search for John Doe, and hit submit, he pops up in the editable grid. How can you implement this to search on several chosen names to bring them up in editable grid?
|
|
|
wkempees
Posts: 1679
|
Posted: 05/06/2008, 9:56 AM |
|
If you search for John Doe, the grid is fed a Where clause.
Normal behaviour would be to have all rows selected that have John and/or Doe in the row's fields (on which the search is done)
So maybe you first have to make sure that you are doing an exact search whereby only rows containing "John Doe" are selected.
Second answer to your question is a bit harder and discussed a few times in this forum already, IMO one of the better posts is this : http://forums.codecharge.com/posts.php?post_id=94083
But before applying that strategy, solve #1,
and find a good way to tell your user what to type in the searchfield (textarea?)
"John Doe", "Jane Doe"
or
John Doe, Jane Doe
or even ; seaparated.
there are some good pointers in that post though.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/06/2008, 10:21 AM |
|
Actually I should have specified they will only be searching by last name. So I'd like them to search on several last names, like Doe Smith Williams....etc. That should make it easier.
It looks like his code breaks up the search string and gives each piece a where clause. Cool.
Except I need to make it work in ASP. That looks like PHP(?)
|
|
|
wkempees
Posts: 1679
|
Posted: 05/06/2008, 10:33 AM |
|
You can do it!
large part is in modyfying the where clause, ther are several of those snippets in the forum/help/docs.
The remainer then is splitting / walking the array's.
That too should be available here.
If you experience to much trouble let me know and I'll assist.
Normaly I would automatically but busy
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
wkempees
Posts: 1679
|
Posted: 05/06/2008, 10:37 AM |
|
Help File, search for" Dynamically Modify the Where"
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/06/2008, 10:55 AM |
|
Thanks Walter. Checking out the dynamically modifying the where clause helpfile I was able to do some translating to vbscript for ASP.
I'm stuck on this one though:
$Component->ds->Where .="(";
Employee2.datasource.where ??
Nevermind: got it...it just amending the ( to employee2.datasource.where
Moving on!
|
|
|
Chris__T
Posts: 339
|
Posted: 05/06/2008, 11:42 AM |
|
Translating code finally stopped giving me errors. Reloaded page, and got the white screen of nothingness. Going to figure out why.
Here is my translated code:
dim keywords(20)
dim search_columns(1)
search_columns(0)="LName"
keywords = CCGetFromGet(s_LName,"")
keywords = trim(keywords)
keywords = preg_split("/[\s,]+/", keywords)
Employee2.datasource.Where = ""
dim p
p = 0
Employee2.DataSource.Where = Employee2.DataSource.Where & "("
dim word
dim m
dim column
for each word in keywords
if (len(word) > 2) then
if (p <> 0) then
Employee2.DataSource.Where = Employee2.DataSource.Where & ") AND ("
p=1
m=0
end if
for each column in search_columns
if (m <> 0) then
Employee2.DataSource.Where = Employee2.DataSource.Where & " OR "
Employee2.DataSource.Where = Employee2.DataSource.Where & " column LIKE '%word%' "
m=1
end if
next '//end foreach column
end if
next '//end foreach word
Employee2.DataSource.Where = Employee2.DataSource.Where & ")"
|
|
|
Chris__T
Posts: 339
|
Posted: 05/06/2008, 11:48 AM |
|
Now that I'm looking at it, I just want to search off of one column, LName. Couldn't I just remove the code for the column for loop and other extra column stuff?
|
|
|
Chris__T
Posts: 339
|
Posted: 05/06/2008, 1:48 PM |
|
I'm guessing preg_split has something to do with it. I'm looking at how I can use vb's split instead of preg_split...no luck yet
|
|
|
Chris__T
Posts: 339
|
Posted: 05/08/2008, 7:02 AM |
|
Can't seem to get this working yet. I don't think preg_split works in ASP, but can't seem to find a comprable operation.
keywords = preg_split("/[\s,]+/", keywords)
|
|
|
wkempees
Posts: 1679
|
Posted: 05/08/2008, 8:09 AM |
|
split(keywords) http://www.w3schools.com/vbScript/func_split.asp
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/08/2008, 8:24 AM |
|
dim keywords(20)
dim Lnamestring
dim search_columns(1)
search_columns(0)="LName"
Lnamestring = CCGetFromGet("s_LName", Empty)
Lnamestring = trim(Lnamestring)
keywords = split(Lnamestring)
Employee2.datasource.Where = " "
dim p
p = 0
Employee2.DataSource.Where = Employee2.DataSource.Where & "("
dim word
dim m
dim column
for each word in keywords
if (len(word) > 2) then
if (p <> 0) then
Employee2.DataSource.Where = Employee2.DataSource.Where & ") AND ("
p=1
m=0
end if
for each column in search_columns
if (m <> 0) then
Employee2.DataSource.Where = Employee2.DataSource.Where & " OR "
Employee2.DataSource.Where = Employee2.DataSource.Where & " column LIKE '%word%' "
m=1
end if
next
end if
next
Employee2.DataSource.Where = Employee2.DataSource.Where & ")"
The code won't let my page load (white screen). Removing code lets my page load. Anyway, I think everything is in ASP now. I can't figure out what's holding it up. The example i got this from says it works with multiple columns. I only need to search one column, so I guess I can make the for loop simpler? Maybe it's getting held up somewhere in there? Any help would be appreciated.
|
|
|
Chris__T
Posts: 339
|
Posted: 05/08/2008, 12:21 PM |
|
Trying this one too. The form loads up and I can search normally when only one word is being searched. But add another word and it doesn't bring up anything.
dim term
dim x
dim clause
dim Lnamestring
Lnamestring = CCGetFromGet("s_LName", Empty)
If Len(Lnamestring) > 0 Then
term = Trim(LNamestring)
If InStr(term," ") > 0 Then
term = Split(term," ")
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
Employee2.DataSource.Where = Employee2.DataSource.Where & clause
End If
|
|
|
Chris__T
Posts: 339
|
Posted: 05/08/2008, 12:38 PM |
|
I got it to work...but it's only pulling x-1 items. If I have 4 search items, it's only pulling 3. Geesh!!
|
|
|
wkempees
Posts: 1679
|
Posted: 05/08/2008, 2:09 PM |
|
post back final version
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/08/2008, 2:15 PM |
|
dim term
dim x
dim clause
dim Lnamestring
Lnamestring = CCGetFromGet("s_LName", Empty)
If Len(Lnamestring) > 0 Then
term = Trim(LNamestring)
If InStr(term," ") > 0 Then
term = Split(term," ")
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
Employee2.DataSource.Where = Employee2.DataSource.Where & clause
End If
I've tested the split function by setting up textboxes and having them store each array value from term. If I have four search values, all four go into each textbox. Not a problem there. But the actual SQL is only pulling term - 1 items. So if I type in White Black Pink Red, i only get records for White Black and Pink. Then if I add Green to the end of the list, Red is now showing up. but not green. Weird. I'm thinking something to do with the for loop? I tried to make it go and extra iteration to try and catch it, but that didn't work. Maybe it's in the code where it appends the clause values?
|
|
|
wkempees
Posts: 1679
|
Posted: 05/08/2008, 3:06 PM |
|
I am no VBman but:
[ edited see next post ]
check out the last 'clause = ' I added (x)
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
wkempees
Posts: 1679
|
Posted: 05/08/2008, 3:11 PM |
|
dim term
dim x
dim clause
dim Lnamestring
Lnamestring = CCGetFromGet("s_LName", Empty)
If Len(Lnamestring) > 0 Then
term = Trim(LNamestring)
If InStr(term," ") > 0 Then
term = Split(term)
For x=0 to Ubound(term)
If x > 0 Then
clause = clause & " OR" End If
clause = clause & " LName LIKE '%" & term(x) & "%'"
Else
clause = " LName LIKE '%" & term(x) & "%'"
End If
Next
Employee2.DataSource.Where = Employee2.DataSource.Where & clause
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
wkempees
Posts: 1679
|
Posted: 05/08/2008, 3:15 PM |
|
And:
Moved the Next
For debugging with White Black Pink Red
dim term
dim x
dim clause
dim Lnamestring
Lnamestring = CCGetFromGet("s_LName", Empty)
If Len(Lnamestring) > 0 Then
term = Trim(LNamestring)
If InStr(term," ") > 0 Then
term = Split(term)
'try with White Black Pink Red
document.write(term(0) & "<br />")
document.write(term(1) & "<br />")
document.write(term(2) & "<br />")
document.write(term(3))
document.write(Ubound(term))
For x=0 to Ubound(term)
If x > 0 Then
clause = clause & " OR" End If
clause = clause & " LName LIKE '%" & term(x) & "%'"
Else
clause = " LName LIKE '%" & term(x) & "%'"
End If
Next
Employee2.DataSource.Where = Employee2.DataSource.Where & clause
End If
I just reread it and the Next was in the right place, the End If is possibly meant for some earlier code??
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
wkempees
Posts: 1679
|
Posted: 05/08/2008, 3:26 PM |
|
Also test with just White as search argument.
I do not know how Vb handles a split when there is nothing to split.
Hope it creates the 'term' as a array anyway holding just one value, then everything should work out ok.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/09/2008, 6:53 AM |
|
I put the (x) after term in the last clause=.... section and that didn't work. It doesn't work when you only have 1 word in your search. The last "end if" goes with the first "If" at the top.
It's weird because it's splitting the string correctly. If there are four words in the string, it splits them and assigns them in the array correctly. I've tested that by sticking them in textboxes. must be something with that for loop adding the last value from term into the clause? More headscratching to continue....
|
|
|
wkempees
Posts: 1679
|
Posted: 05/09/2008, 9:30 AM |
|
dim term
dim x
dim clause
dim Lnamestring
Lnamestring = CCGetFromGet("s_LName", Empty)
If Len(Lnamestring) > 0 Then 'something to search for ??
term = Trim(LNamestring) 'remove trailing spaces (not needed )
If InStr(term," ") > 0 Then 'check for at least 1 space in the string
term = Split(term," ") 'if so build array of search words, else added
For x = LBound(term) to Ubound(term) 'for lowest to highest
If x > 0 Then 'subsequent pass, so add OR
clause = clause & " OR" 'removed an End If here !?
clause = clause & " LName LIKE '%" & term(x) & "%'"
Else 'first pass, so build search clause
clause = " LName LIKE '%" & term(x) & "%'"
End If
Next
Else 'no single space in term so just one word present
clause = " LName LIKE '%" & Lnamestring & "%'"
End If
Employee2.DataSource.Where = Employee2.DataSource.Where & clause 'this is a potential problem
'as the datasource might already have content in the where
End If
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
wkempees
Posts: 1679
|
Posted: 05/09/2008, 9:33 AM |
|
I removed an End If
added an Else to catrer for single word search
AND:
installing VB just to be of more help (thanks)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/09/2008, 9:35 AM |
|
I didn't see your most recent post. I was fidgeting with the code and came up with a "cheat" to make it work. Gotta love those
After this: Lnamestring = CCGetFromGet("s_LName", Empty)
I do this: Lnamestring = Lnamestring & " ."
I add a . (period) to the end of the string (with the space in front of it) to simulate another word.
So when it does split it up, the period is in the last array value, and so it pulls all of the actual names in front of the period! Crazy! but it works!
|
|
|
wkempees
Posts: 1679
|
Posted: 05/09/2008, 9:57 AM |
|
Do not know if I am happy wih that.......
haha spend lot of time and dry debugging (no VB present)
Save your current working version and try the last post above....... please......
(have a nice Weekend)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
Chris__T
Posts: 339
|
Posted: 05/09/2008, 10:41 AM |
|
I tried your above modifications as well, but they didn't work. Kept getting n-1 terms searched on. I guess I'll use the cheat for now. But I'll keep at fixing the original problem. Thanks Walter! Enjoy the weekend!
|
|
|
GeorgeS
Posts: 206
|
Posted: 05/15/2008, 6:11 PM |
|
Chris,
do you still need help with multiple searching?
if so, I think that I have this exact piece of code in one of my old asp projects.
let me know & i'll try to find it...
_________________
GeorgeS |
|
|
wkempees
Posts: 1679
|
Posted: 05/16/2008, 5:10 AM |
|
@GeorgeS
for all of us, please do.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
GeorgeS
Posts: 206
|
Posted: 05/16/2008, 1:06 PM |
|
Function tblAllClients_DataSource_BeforeBuildSelect()
IF Request.QueryString("s_LastNames") <>"" THEN
Dim LNames: LNames = Replace(Request.QueryString("s_LastNames"), "'","''")
Dim arrLNames: arrLNames = Split(LNames, ",")
Dim sSQL, i
For i=0 to UBound(arrLNames)
IF i = UBound(arrLNames) THEN
IF sSQL ="" THEN
sSQL = sSQL & "LastName LIKE '" & Trim(arrLNames(i)) & "%'"
ELSE
sSQL = sSQL & "LastName LIKE '" & Trim(arrLNames(i)) & "%')"
END IF
ELSE
IF i=0 THEN
sSQL = sSQL & "(LastName LIKE '" & Trim(arrLNames(i)) & "%' OR "
ELSE
sSQL = sSQL & "LastName LIKE '" & Trim(arrLNames(i)) & "%' OR "
END IF
END IF
Next
tblAllClients.DataSource.where = tblAllClients.DataSource.where & sSQL
END IF
End Function
_________________
GeorgeS |
|
|
Chris__T
Posts: 339
|
Posted: 05/22/2008, 9:55 AM |
|
I'll have to try that one out too. Thanks GeorgeS
|
|
|