473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

help trying to streamline my sql

347 100+
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, 77 views)
File Type: zip pages.zip (41.1 KB, 70 views)
Apr 28 '11 #1
2 1516
jhardman
3,406 Expert 2GB
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
colinod
347 100+
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

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

Similar topics

5
by: John T | last post by:
I am trying to make a function that takes an optional parameter that gets passed by reference. Here is the first line of my function definition: function funQueryDatabase($strQuery,...
6
by: Alan Silver | last post by:
Hello, I have an ASP that takes a connection string and SQL statement in the querystring and is supposed to return the XML representation of the recordset to the Response stream (don't worry,...
3
by: Mool | last post by:
I'm trying to figure a way to use document.image.src to place either ..jpg over a named image (which we know works fine) or Flash .swf files...my guess is that you can't...but is there a way to use...
1
by: RWC | last post by:
Hello, I have just converted a database from A97 to A2K2. I had a procedure for changing start up properties that worked really well. For some reason, they don't work in A2K2 and I'm having a...
3
by: MatGyver | last post by:
I am going nuts trying to figure this out, any help will be appreciated. I have an existing table called "Parts". And in this table I have the following columns: "ID" "Part Number" "Part...
0
by: dantan98 | last post by:
Hello, all! I am hoping you can help me. I am trying to find an online article about transferring an image via a web service. It involves encoding on one end into a byte stream, passing it to...
1
by: Rico | last post by:
Hello, Hope someone can help here, I'm trying to modify the following code to use an automation object so I don't have deal with CRAPPY REFERENCES! The reason being is that this will be going...
4
by: cpptutor2000 | last post by:
Could some C guru help me please? I am using the following program to open a SSH connection to a remote host and eventually run a program on that remote host. #include <stdio.h> #include...
4
by: l!m!t | last post by:
I am very new to PHP hopefully someone can help me I am trying to use a wildcard for a simple "if else" code. I cant seem to figure out how to do this. e.g. if($cPath == '1') { echo "this...
6
by: AppleBag | last post by:
I'm having the worst time trying to login to myspace through code. Can someone tell me how to do this? Please try it yourself before replying, only because I have asked this a couple of times in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.