473,221 Members | 1,900 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,221 software developers and data experts.

query an access database

6
i would like to know how to query a database so that if a category from the dropdown menu is selected it will display all the records found on the table.
thank you very much.
Here is the table from the database:

Description Design Type Price
Necklace Spiral 18 ct Gold 1,725.99
Necklace Chain 9 ct Gold 150.75
Ring Sapphire 18 ct Gold 975.00
Necklace Loop 9 ct Gold 199.50
Ring Opal 18 ct Gold 225.50
Ring Ruby Silver 180.00
Necklace Sapphire Silver 375.00
Ring Diamond Silver 379.99
Watch Wrist Platinum 99.99
Watch Wrist Rolex 2,299.99

and here the code:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim Connection, Recordset
  3. Dim sSQL, sConnString
  4.  
  5. sSQL="SELECT * FROM table1"
  6.  
  7.  
  8. sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &_
  9. "Data Source=" & Server.MapPath("Jewellerydb.mdb")
  10.  
  11.  
  12. Set connection = Server.CreateObject("ADODB.Connection")
  13. Set Recordset = Server.CreateObject("ADODB.Recordset")
  14.  
  15. connection.Open sConnString
  16. recordset.Open sSQL,connection
  17.  
  18. If request.form("dropdown")="Watch" Then 
  19. Response.Write recordset("Design") & " " & recordset("Type") & " " & recordset("Price")
  20. ElseIf request.form("dropdown")="Necklace" Then 
  21. Response.Write recordset("Design")
  22. ElseIf request.form("dropdown")="Ring" Then 
  23. Response.Write recordset("Price")
  24. End if
  25.  
  26. Recordset.Close
  27. Connection.Close
  28. Set Recordset = Nothing
  29. Set Connection = Nothing
  30. %>
Thank you for your help!
Jun 18 '07 #1
3 1639
jhardman
3,406 Expert 2GB
i would like to know how to query a database so that if a category from the dropdown menu is selected it will display all the records found on the table.
thank you very much.
Here is the table from the database:

Description Design Type Price
Necklace Spiral 18 ct Gold 1,725.99
Necklace Chain 9 ct Gold 150.75
Ring Sapphire 18 ct Gold 975.00
Necklace Loop 9 ct Gold 199.50
Ring Opal 18 ct Gold 225.50
Ring Ruby Silver 180.00
Necklace Sapphire Silver 375.00
Ring Diamond Silver 379.99
Watch Wrist Platinum 99.99
Watch Wrist Rolex 2,299.99

and here the code:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim Connection, Recordset
  3. Dim sSQL, sConnString
  4.  
  5. sSQL="SELECT * FROM table1"
  6.  
  7.  
  8. sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &_
  9. "Data Source=" & Server.MapPath("Jewellerydb.mdb")
  10.  
  11.  
  12. Set connection = Server.CreateObject("ADODB.Connection")
  13. Set Recordset = Server.CreateObject("ADODB.Recordset")
  14.  
  15. connection.Open sConnString
  16. recordset.Open sSQL,connection
  17.  
  18. If request.form("dropdown")="Watch" Then 
  19. Response.Write recordset("Design") & " " & recordset("Type") & " " & recordset("Price")
  20. ElseIf request.form("dropdown")="Necklace" Then 
  21. Response.Write recordset("Design")
  22. ElseIf request.form("dropdown")="Ring" Then 
  23. Response.Write recordset("Price")
  24. End if
  25.  
  26. Recordset.Close
  27. Connection.Close
  28. Set Recordset = Nothing
  29. Set Connection = Nothing
  30. %>
Thank you for your help!
You might want to study up on sql.

I don't see anything that would cause an error in the above code, but I don't see why you would code like that. As you posted you did not make a question. Do you have a question, or did you just want to post your code sample?

Jared
Jun 18 '07 #2
Ninio
6
You might want to study up on sql.

I don't see anything that would cause an error in the above code, but I don't see why you would code like that. As you posted you did not make a question. Do you have a question, or did you just want to post your code sample?

Jared
Hi, and thank you for replying so soon.
The actual code i posted had no errors, in fact it works very well indeed.
As you can see I have a table in the database which has 4 columns and 10 rows.
So far what i can do is to connect to the database and get the drop down options to display all records. But what i need to do know is how to query the table for specific content. For instance request.form("dropdown")="Watch" to display only the rows that have watch in the description column, request.form("dropdown")="Ring" to display only the rows that have ring in the description column and so on.
I have looked around and tried different approaches but so far nothing has worked.
Thank you for your help
Jun 18 '07 #3
jhardman
3,406 Expert 2GB
try this
Expand|Select|Wrap|Line Numbers
  1. sSQL="SELECT * FROM table1"
  2.  
  3. sSQL = sSQL & " WHERE description LIKE '" & request.form("dropdown") & "'"
  4.  
  5. 'if request.form("dropdown") said "watch" then
  6. 'sSQL now says, "SELECT * FROM table1 WHERE description LIKE 'watch' 
  7.  
  8. sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &_
  9. "Data Source=" & Server.MapPath("Jewellerydb.mdb")
  10.  
  11. Set connection = Server.CreateObject("ADODB.Connection")
  12. Set Recordset = Server.CreateObject("ADODB.Recordset")
  13.  
  14. connection.Open sConnString
  15. recordset.Open sSQL,connection
Jun 18 '07 #4

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

Similar topics

15
by: Rolan | last post by:
There must be a way to enhance the performance of a query, or find a plausible workaround, but I seem to be hitting a wall. I have tried a few tweaks, however, there has been no improvement. ...
3
by: rhobson2 | last post by:
Hello, I wrote a database applicaiton using Access XP (2002) and everything has been working good for the client until they purchased a couple of new computers with Access 2003. The meetings...
2
by: Bob Alston | last post by:
If you have an access form with record source being a straightforward query and where clause in the form definition, will the query be sent to the back end jet/Access database and executed there,...
3
by: mnjkahn via AccessMonster.com | last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I right click on the field name in Query Design View, and then click Build, Access crashes before the Build window...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.