467,211 Members | 1,218 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,211 developers. It's quick & easy.

query an access database

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
  • viewed: 1488
Share:
3 Replies
jhardman
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
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
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

Post your reply

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

Similar topics

3 posts views Thread by rhobson2@triad.rr.com | last post: by
2 posts views Thread by Bob Alston | last post: by
3 posts views Thread by mnjkahn via AccessMonster.com | last post: by
2 posts views Thread by existential.philosophy@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.