By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,162 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,162 IT Pros & Developers. It's quick & easy.

help trying to streamline my sql

100+
P: 347
I have a website that uses asp and sql to access an access database and am trying to streamline the code, im not very good with access and sql and was wondering if someone could help?

i have sql code along the lines of this on all pages
Expand|Select|Wrap|Line Numbers
  1. Set yaketyConnection=Server.CreateObject("ADODB.Connection")
  2. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
  3. yaketyConnection.Open DatabaseDetails
  4. yaketyQuery="SELECT * FROM celebs WHERE voicetypea like '%"&voiceid&"%' and sex like 'm' or voicetypeb like '%"&voiceid&"%' and sex like 'm' or voicetypec like '%"&voiceid&"%' and sex like 'm' or voicetyped like '%"&voiceid&"%' and sex like 'm' or voicetypee like '%"&voiceid&"%' and sex like 'm' or voicetypef like '%"&voiceid&"%' and sex like 'm' or voicetypeg like '%"&voiceid&"%' and sex like 'm' or voicetypeh like '%"&voiceid&"%' and sex like 'm' or voicetypei like '%"&voiceid&"%' and sex like 'm' or voicetypej like '%"&voiceid&"%' and sex like 'm' or voicetypek like '%"&voiceid&"%' and sex like 'm' or voicetypel like '%"&voiceid&"%' and sex like 'm' or voicetypem like '%"&voiceid&"%' and sex like 'm'  or voicetypen like '%"&voiceid&"%' and sex like 'm' or voicetypem like '%"&voiceid&"%' and sex like 'm' or voicetypeo like '%"&voiceid&"%' and sex like 'm' or voicetypep like '%"&voiceid&"%' and sex like 'm' or voicetypeq like '%"&voiceid&"%' and sex like 'm' or voicetyper like '%"&voiceid&"%' and sex like 'm' or voicetypes like '%"&voiceid&"%' and sex like 'm' or voicetypet like '%"&voiceid&"%' and sex like 'm' or voicetypeu like '%"&voiceid&"%' and sex like 'm' or voicetypev like '%"&voiceid&"%' and sex like 'm' or voicetypew like '%"&voiceid&"%' and sex like 'm' order by surname,firstname"
  5. Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
i have attached a selection of pages and the access database to look at

I hope someone can assist i would like to speed up our website and im sure the code and database is messy
Attached Files
File Type: zip yaketynew1.zip (732.9 KB, 50 views)
File Type: zip pages.zip (41.1 KB, 43 views)
Apr 28 '11 #1
Share this Question
Share on Google+
2 Replies


jhardman
Expert 2.5K+
P: 3,405
The problem is you have over twenty columns with the same (almost) name and description. What's up with that? change it to a single memo field so you can enter a paragraph if you want, then you just have to search one field. The only other reasonable solution is to have a separate table with a list of voicetypes, and another table relating that to the celebs:
Expand|Select|Wrap|Line Numbers
  1. voicetypes table
  2. voicetype_id     voicetype_description
  3. 1                hearty
  4. 2                whiny
  5. 3                motherly
  6. etc
  7.  
  8. celeb voicetypes table
  9. celebid     voicetypeid
  10. 1            1
  11. 1            3
  12. 1            17
  13. 2            6
  14. 3            5
  15. 3            19
  16. 3            8
  17.  
That is the real power of a relational database even if you have an easy way to add new voicetypes. For example, when a celeb enters his voicetype, check to see if it's already in the db, if it is, add celeb 16 and voicetype 6 to the celeb voicetype table. If not, add a new voicetype and then use that new number to add to the celeb voicetype table, and the next time a celeb enters that voicetype it will already be in the db.

Jared
Jun 10 '11 #2

100+
P: 347
Hi Jared

Thanks for that i now have a DB that is as above except the voicetypeid is worded and not numbered, i am trying to get my sql to work now and tried the following but i am going wrong somewhere

Expand|Select|Wrap|Line Numbers
  1. yaketyQuery="SELECT * FROM celebs INNER JOIN celebsvoicetypes ON celebs.idnumber = celebsvoicetypes.celebid where celebsvoicetypes.voicetype = '" &voiceid& "' and celebs.sex like 'f' order by celebs.surname,celebs.firstname"
Jun 10 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.