Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-26 : 11:35:02
|
| Justin writes "Dear SQL Team,I am an intermediate Ultradev 4 user (ASP Access solutions) and need your advice about a curly search challenge.Imagine a drop down menu which displays labels from a 'description' field and values from a 'code' field, like this: Code Description ---- ----------- AU Australia DE Germany FI Fiji FR France GB Britain IT Italy TH Tahiti ZA ZambiaYou know the deal ... the code is stored, the description is what the user sees in the drop down. In this particular example the user is actually selecting a code which will be used as search criteria.However, things get a bit more complicated than that as the Description values in my table at times comprise multiple codes, for example: Code Description ---- ----------- AU Australia AU FI TH Asia Pacific <-- collections of codes FR IT DE GB Europe <-- collections of codes DE Germany FR France IT Italy FI Fiji TH Tahiti GB Britain ZA ZambiaNow, the challenge is this - the single codes are easy to pass to an SQL statement for searching, eg WHERE 'code' like "AU", HOWEVER what syntax would I use to pass the collection of codes such that records having only country codes of, say, "FR" or "IT" turned up when "Europe" was picked from the pull down (ie "FR IT DE GB") was selected as the criteria?The solution I have in mind is simply improved syntax, or even, changed data structure. If you are fresh out of ideas in that department, then any suggestions would be appreciated.Many thanks,Justin" |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-26 : 11:59:29
|
| maybe you should explain your schema a little better ... is the code value for say "AU FI TH" "Asia Pacific" actually stored multiple times? or is the code value actually "AU FI TH" and the name "Asia Pacific" ? depending on the situation we'll see what we can scrape up... |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-08-26 : 15:50:03
|
| How about if you change your multiple code representation to be comma delimited, then use IN and either dynamic SQL or the "dynamic IN without dynamic SQL" technique as described here: http://accesshelp.net/content/Report.asp?REPORT=4&PARAM_ID=46For instance, the code for "Asia Pacific" would become "AU,FI,TH" and the query would become WHERE 'code' in ('AU','FI','TH').Cheers-b |
|
|
|
thiruna
Starting Member
41 Posts |
Posted - 2002-08-27 : 00:20:59
|
| justin,i can able to help if you can display the code you already have and table structure here...thanksthiru |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-08-27 : 07:57:55
|
heheheIt's rare that we can help properly without table definitions and everyone is asking for them  What your query needs is an "IN" in your where clause..I.E. WHERE CountryCode IN ('FR', 'IT', 'DE', 'GB')That should do it.Can I make a suggestion though...wean yourself as FAR AWAY from Ultradev as you can now and learn what is really going on in your ASP and SQL code. I guarantee it will make your code 100% more efficient.Damian |
|
|
|
justinmoss
Starting Member
2 Posts |
Posted - 2002-09-29 : 17:40:09
|
| Dear Onamuji, aiken, thiruna and Merkin,Firstly, THANK YOU all for your feedback. Unfortunately though, I have tried to integrate 'IN' with my WHERE statement, however, it doesn't return the expected result. Perhaps I should recap my setup:Setup: - Hosted Access 2000 database (not SQL) - ASP pagesDatabase structure: CountryCode (eg, "AU", "NZ", "US") Country (eg. "Australia", "New Zealand", "United States") Search Page: DropDown Menu with individual CountryCodes AND collections, eg: AU <------- for "Australia" AU, NZ <------- for "ASIA PACIFIC" (a collection!) NZ <------- for "New Zealand" US <------- for "United States" So, as you can see from the above, what I need to do is search the CountryCode field for any records that contain individual matching CountryCodes *OR* codes that are included in a collection of codes, eg, "ASIA PACIFIC", "EUROPE", "ASIA"This is how I've modifed my RecordSet in Ultradev: SELECT * FROM tblImages WHERE countrycode in ('var_co')If I set "AU" as the Default Value to test the query, it works fine, however, if I set the Default Value to "'AU','NZ'" it returns nothing, whereas it SHOULD return all records matching AU and NZ. I have tried several ways of passing the Default Values to the WHERE Select statement, however it returns nothing (no errors, no results).The IN operator seemed like excellent advice, but I just can't make it work. Your assistance would be greatly appreciated.Many thanks,Justin |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-09-29 : 20:37:22
|
| are you sure it should return values? did you run the query and get the expected results? can you output the query before its executed? |
|
|
|
justinmoss
Starting Member
2 Posts |
Posted - 2002-10-01 : 01:09:59
|
| Onamuji wrote:> are you sure it should return values? well, no .. I was hoping someone here could clarify whether the "IN" function would work with Access in this way> did you run the query and get the expected results? no, although I was led to believe it wouldl > can you output the query before its executed?using Ultradev Recordset Definition dialogue - yes (but so far, it does not work the way it had been suggested it should).SO, does anyone have any thoughts on how to solve this puzzle??Thanks in advance,Justin |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-10-01 : 01:29:37
|
| The SQL, from the info given, looks valid. IN should work fine for you. It looks, from the info given, that Ultradev could be mangling it somewhere. Either that or there is something else you are not telling us. Certainly when you put the values in as you suggested it should have worked.I think the point Onamuji is trying to make, is that there are a bunch of tests you should run to make sure what you think is going back to the database is what is actually going back to the database.Hope that helpsDamian |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-10-01 : 05:00:32
|
Hi Justin,been reading your problem and answers given, came up with a solution:-break your combined drop down menu in two-in the first you select the "description" (Australia, Germany,...)-in the second you filter out the codes based on the first menuI don't see a point in having only one drop down menu (or combobox) that holds both parameters(how do you search for a record, which field value are you using,...?).What will happen to the menu if one country has, lets say, 15 codes?I hope the users have big monitors...If your description holds multiple codes, there has to be a one to many relationship in your DB, on which you could easily filter out records for the second menu. If this is not the case, pick up a book on DB and GUI design, and think about this quote:
Can I make a suggestion though...wean yourself as FAR AWAY from Ultradev as you can now and learn what is really going on in your ASP and SQL code. I guarantee it will make your code 100% more efficient.
HND (Have a Nice Day) |
|
|
|
ashok
Yak Posting Veteran
57 Posts |
Posted - 2002-10-01 : 05:32:01
|
ultradev might be mangling your single quotes..try setting the default value to just : AU, NZand in your vbscript code do this :...'build your sql querystrQuery =" Where CountryCode IN ('" +replace(varCtryCodes, ", ", "','") + "')"what the code basically does is , convert your AU,NZ to 'AU', 'NZ' format...you can get the code for a vbscript 'replace()' function by doing a search on google or msdn...quote:
If I set "AU" as the Default Value to test the query, it works fine, however, if I set the Default Value to "'AU','NZ'" it returns nothing, whereas it SHOULD return all records matching AU and NZ.
|
|
|
|
|
|
|
|
|
Ultradev Multikey Search SQL - ideas?