query an access database

[Ninio]

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:

<%
Dim Connection, Recordset
Dim sSQL, sConnString
 
sSQL="SELECT * FROM table1"
 
 
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Server.MapPath("Jewellerydb.mdb")
 
 
Set connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
 
connection.Open sConnString
recordset.Open sSQL,connection
 
If request.form("dropdown")="Watch" Then 
Response.Write recordset("Design") & " " & recordset("Type") & " " & recordset("Price")
ElseIf request.form("dropdown")="Necklace" Then 
Response.Write recordset("Design")
ElseIf request.form("dropdown")="Ring" Then 
Response.Write recordset("Price")
End if
 
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
%>

Thank you for your help!

[jhardman]

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:

<%
Dim Connection, Recordset
Dim sSQL, sConnString
 
sSQL="SELECT * FROM table1"
 
 
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Server.MapPath("Jewellerydb.mdb")
 
 
Set connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
 
connection.Open sConnString
recordset.Open sSQL,connection
 
If request.form("dropdown")="Watch" Then 
Response.Write recordset("Design") & " " & recordset("Type") & " " & recordset("Price")
ElseIf request.form("dropdown")="Necklace" Then 
Response.Write recordset("Design")
ElseIf request.form("dropdown")="Ring" Then 
Response.Write recordset("Price")
End if
 
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
%>

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

[Ninio]

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

[jhardman]

try this

sSQL="SELECT * FROM table1"
 
sSQL = sSQL & " WHERE description LIKE '" & request.form("dropdown") & "'"
 
'if request.form("dropdown") said "watch" then
'sSQL now says, "SELECT * FROM table1 WHERE description LIKE 'watch' 
 
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Server.MapPath("Jewellerydb.mdb")
 
Set connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
 
connection.Open sConnString
recordset.Open sSQL,connection