LittleMan22
Technical User
I have a form on a web page that transfers values to another page to query an Access database. The first query uses form fields and returns a value called 'band' from the AllBand table (see below):
<% Option Explicit %>
<html>
<head>
<title>Bands</title>
</head>
<body>
<h2>Quick Quote</h2>
<ul>
<%
Dim dbBand
Dim rsBand
Dim oneRec
Dim Form_Amount
Dim Form_Plan
Form_Amount = Request.Form("Amount"
Form_Plan = Request.Form("Plan"
Set dbBand = Server.CreateObject("ADODB.Connection"
dbBand.Open("rates_131708"
oneRec = "SELECT * FROM AllBands WHERE RateInfoID=2 AND Plan="&Form_Plan&" AND MinAmount <= "&Form_Amount&" AND MaxAmount > "&Form_Amount&""
Set rsBand = Server.CreateObject("ADODB.Recordset"
rsBand.Open oneRec, dbBand
Do While Not rsBand.EOF
Response.Write "<li><p>" & rsBand("Band" & "</p></li>"
rsBand.MoveNext
Loop
If rsBand.BOF Then
Response.Write "<p>No Entries in the database!</p>" & VBCrLf
End If
rsBand.Close
dbBand.Close
%>
</ul>
</body>
</html>
So like I mentioned before this query is returning the value located in the band field. What I want it to do though is to copy this value into the following SQL query so that I can use it to query another table and return a different value (rate). Here's what that query needs to look like:
<%
Dim dbBand
Dim rsBand
Dim oneRec
Dim Form_Sex
Dim Form_Age
Dim Form_Plan
Form_Sex = Request.Form("Sex"
Form_Age = Request.Form("Age"
Form_Plan = Request.Form("Plan"
Set dbBand = Server.CreateObject("ADODB.Connection"
dbBand.Open("rates_131708"
oneRec = "SELECT * FROM Can_Life WHERE RateInfoID=2 AND Sex="&Form_Sex&" AND Class=0 AND Age="&Form_Age&" AND Plan="&Form_Plan&" AND Band="Value_from_1st_SQL""
So somehow I have to insert the value that was returned from the first query into this second query (just so you know they are querrying different tables).
How do I write a 'joined' or 'double' query?
Thanks again,
-Ryan
<% Option Explicit %>
<html>
<head>
<title>Bands</title>
</head>
<body>
<h2>Quick Quote</h2>
<ul>
<%
Dim dbBand
Dim rsBand
Dim oneRec
Dim Form_Amount
Dim Form_Plan
Form_Amount = Request.Form("Amount"
Form_Plan = Request.Form("Plan"
Set dbBand = Server.CreateObject("ADODB.Connection"
dbBand.Open("rates_131708"
oneRec = "SELECT * FROM AllBands WHERE RateInfoID=2 AND Plan="&Form_Plan&" AND MinAmount <= "&Form_Amount&" AND MaxAmount > "&Form_Amount&""
Set rsBand = Server.CreateObject("ADODB.Recordset"
rsBand.Open oneRec, dbBand
Do While Not rsBand.EOF
Response.Write "<li><p>" & rsBand("Band" & "</p></li>"
rsBand.MoveNext
Loop
If rsBand.BOF Then
Response.Write "<p>No Entries in the database!</p>" & VBCrLf
End If
rsBand.Close
dbBand.Close
%>
</ul>
</body>
</html>
So like I mentioned before this query is returning the value located in the band field. What I want it to do though is to copy this value into the following SQL query so that I can use it to query another table and return a different value (rate). Here's what that query needs to look like:
<%
Dim dbBand
Dim rsBand
Dim oneRec
Dim Form_Sex
Dim Form_Age
Dim Form_Plan
Form_Sex = Request.Form("Sex"
Form_Age = Request.Form("Age"
Form_Plan = Request.Form("Plan"
Set dbBand = Server.CreateObject("ADODB.Connection"
dbBand.Open("rates_131708"
oneRec = "SELECT * FROM Can_Life WHERE RateInfoID=2 AND Sex="&Form_Sex&" AND Class=0 AND Age="&Form_Age&" AND Plan="&Form_Plan&" AND Band="Value_from_1st_SQL""
So somehow I have to insert the value that was returned from the first query into this second query (just so you know they are querrying different tables).
How do I write a 'joined' or 'double' query?
Thanks again,
-Ryan