Sean
Posts: 39
|
| Posted: 04/14/2005, 7:30 AM |
|
I am trying to create a search grid that allows a user to select multiple zip codes. I got the multiple selection list box working, but the SQL doesn't seem to work.
The search will return a result anytime I select a single zip code, but when I select multiple ones, it returns no results (even though the query string has each zip code listed).
I am trying this over a single search parameter s_keyword with no other selections available against a DB2 ODBC connection.
Any help would be appreciated.
|
 |
 |
smalloy
Posts: 107
|
| Posted: 04/14/2005, 11:56 AM |
|
I didn't try your example in a search from but I use the multi select often and this is how I use it.
If the user selects a more than one item the list is returened in a comma separated list, i.e. 18175, 19063, 08085. If they are numbers your OK, if text youll need to surround the text with the proper ""'s since we are readying the return for use with an IN Clause. (I use SQL but I am sure that DB2 uses it).
Here is some code to play with:
Dim strZipCodes
strZipCodes = Request("multipleselectname")
strZipCodes = REPLACE (strZipCodes, ", ", "', '")
Then go to the grid or whatever you are showing the results an in the parameters look for the IN operator. Don't worry, if a user only chooses one value it will still work!
Hope I've helped.
_________________
Anything can be done, just give me time and money. |
|
|
|
Sean
Posts: 39
|
| Posted: 04/14/2005, 1:00 PM |
|
smalloy,
Thanks for the reply. I tried what you suggested, but still not luck. I debugged the output and noticed that the Replace just added single quotes at the commas, so I modified the replace a bit to the following:
strZipCodes = "'" & REPLACE (strZipCodes, ", ", "', '") & "'"
I am doing the Replace in the grid's Before Show event.
This is still not working. When I debug what I added, I get the list of Zip Codes surrounded by single quotes.
My SQL Statement in the grid is as follows:
SELECT *
FROM TABLENAME
WHERE ZipCode IN '{s_keyword}'
Keywork parameter is set as s_keyword variable as Text.
Please help. I really need to get this working ASAP.
Thanks,
Sean
|
|
|
|
smalloy
Posts: 107
|
| Posted: 04/14/2005, 1:15 PM |
|
Hmmm, SQL server uses singe quotes so its good for me.
Your - strZipCodes = "'" & REPLACE (strZipCodes, ", ", "', '") & "'" was close but literally its outcome is 2 quotes and then anything in the middle is still single.
I know there's a way to use 3 quotes but I like your reasoning so try
REPLACE (strZipCodes, ", ", double quote, 2 singles , 2 singles double quote)
Sound good. Let me know, In the mean time I'll look for the quote's fix. Although, this may work!
_________________
Anything can be done, just give me time and money. |
|
|
|
smalloy
Posts: 107
|
| Posted: 04/14/2005, 1:22 PM |
|
Yup, that should do it, here's what I used in my VB days:
Public Function FixQuotes(ByVal strValue As String) As String
FixQuotes = Replace(strValue, " ' ", " ' ' ")
End Function
I used a function that literally took every ' (single) and made 2 singles (a double)
Try your query with the IN Clause outside of CCS (for me its query analyzer) and make sure the query itself works.
FIANLLY, I'm not sure the BEFORE SHOW is the best place to do the REPLACE - I'm guessing that BEFORE BUILD SELECT is the better choice!
_________________
Anything can be done, just give me time and money. |
|
|
|
Sean
Posts: 39
|
| Posted: 04/14/2005, 1:43 PM |
|
No luck.
It still does not work. I originally had the code in the before build select, but changed it to Before Show because it did not retrun any results.
When I print out your new replace, the print out on the screen does not have any quotes around anything....
Thanks.
|
|
|
|
Sean
Posts: 39
|
| Posted: 04/14/2005, 1:50 PM |
|
This is my print when I use this:
REPLACE (strZipCodes, ", ", double quote, 2 singles , 2 singles double quote)
URL
http://www.domain.com/page.asp?s_keyword=77053&s_keyword=77083
Output
77053'','' 77083
|
|
|
|
smalloy
Posts: 107
|
| Posted: 04/14/2005, 2:00 PM |
|
OK, try to build the WHERE clause, again definitly in the BEFORE BUILD SELECT - always build datasoruce SQL, WHERE and ORDER in BEFORE BUILD SELECT, directly.
If gridname.datasource.where <> Empty Then
gridname.datasource.where = gridname.datasource.where & " AND Zipcodes IN(" & stringZips & ")"
Else
gridname.datasource.where = "Zipcodes IN(" & stringZips & ")"
End If
Also, do you have anyway to see what the DB is receiving? I have profiler where I see what the built queries look like when the hit the DB, its very helpful.
If not, try and do
Response.Write gridname.datasource.SQL
Response.END ' important!
then cut and past into your DB and see if it flies.
_________________
Anything can be done, just give me time and money. |
|
|
|
smalloy
Posts: 107
|
| Posted: 04/14/2005, 2:05 PM |
|
OK, from your last post lets combine our soutions to fix this!!!
Use this:
strZipCodes = " ' ' " & (strZipCodes, ", ", double quote, 2 singles , 2 singles double quote) & " ' ' "
I used spaces between the " ' ' " to help you see the differences (quite confusing huh) so remove the spaces and, God willing, you should have it!
_________________
Anything can be done, just give me time and money. |
|
|
|
Sean
Posts: 39
|
| Posted: 04/15/2005, 8:58 AM |
|
That didn't do it either. This is really starting to frustrate me.
I do appreciate you helping with this though 
|
|
|
|
smalloy
Posts: 107
|
| Posted: 04/15/2005, 9:46 AM |
|
OK lets scrap the whole replace thing and go with the Codegcharge solution, it is as follows (from the http://examples.codecharge.com/ExamplePack/MultiSelectS...electSearch.php example).
1)Switch to HTML mode and locate the code for the s_project ListBox then delete the <option> tag for the default 'Select Value' option (i.e. <option value="" selected>Select Value</option>).
2)Open the 'Data Source' window by clicking on the [...] button next to the Data Source property of the grid form, then select 'SQL' in the "Data Source Type" field.
Add the 's_keyword' URL parameter to the grid's data source with "0" as a Default Value. Then modify the SQL statement in the Data Source property by adding the "IN" statement.
I always use the strZipCodes = Request("multipleselectname")
strZipCodes = REPLACE (strZipCodes, ", ", "', '") since its what I'm used to. Give the above a try, see if it helps!
If it doesn't, PM me with your email address and perhaps you could email be your page so I can trouble shoot.
Steve
_________________
Anything can be done, just give me time and money. |
|
|
|
Sean
Posts: 39
|
| Posted: 04/15/2005, 10:23 AM |
|
PM sent
|
|
|
|
Sean
Posts: 39
|
| Posted: 04/18/2005, 7:39 AM |
|
OK, It's working! smalloy (Steve) suggested running the SQL statement directly on the AS/400. I did not have access to directly run SQL statements on the DB2 (AS/400), so I contacted a programmer and had him do it. It turns out there was an additional space in the s_keyword replace string.
ORIGINAL REPLACE STRING
strZipCodes = Replace(strZipCodes,",","','")
NEW REPLACE STRING
strZipCodes = Replace(strZipCodes,", ","','")
They are the same, execpt in the 2nd REPLACE STRING, I added a space after the , in the first parameter to replace ", " with "','".
I now notice that Steve had this working before, but when I re-typed it in, I did not notice the space.
I want to thank Steve for all his help. It's helpful people like Steve (and others) that make this forum great!
Sean
|
|
|
|
|
