Using Multiple Recordsets

[ITisSpatial]

I'm trying to use the results from one recordset. To query the database to establish a second recordset.

I'm brand new to vbscript but have done this many times in PHP. I'm having some trouble with the second recordset

Here's my initial attempts:

''Having connected to the database
''Open the RecordSet
Dim SQL
SQL = "SELECT DISTINCT COUNTYNO,City_FIPS FROM tblALL_ZIP9 WHERE ZIPNINE = " & Zip9 & ""

Dim RecSet
Set RecSet = CreateObject("ADODB.RecordSet")
RecSet.Open SQL, dbconnect

While Not RecSet.EOF
Response.Write(RecSet("COUNTYNO") &"<br>")

RecSet.MoveNext
Wend
''''''Everything works fine here. I'm getting the return I want


Now I want to take the 'COUNTYNO' that is returned and query the SQL database for a new Recordset

I've tried to mimmick what I've got above for a second recordset. But it doesn't like it.

SQL2 = "SELECT * FROM tblSSTP_RATE WHERE (JURISDICTION_FIPS = " & RecSet("COUNTYNO") & ""

Dim RecSet2
Set RecSet2 = CreateObject("ADODB.RecordSet")
RecSet2.Open SQL2, dbconnect
While Not RecSet2.EOF
Response.Write(RecSet2("GEN_TAX_RATE_INTRASTATE"))
Wend

Like I said. The SQL statements and the logic work fine in PHP. I'm just required to use ASP for this project.

Any help would be great, Thanks

 

[PHV]

Why not simply use a single Recordset with an INNER JOIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ITisSpatial]

Thanks, I realized what I was saying, after I posted the question. I've started working on the JOIN and am getting an incorrect syntax near keyword "INNER". It works elsewhere, and I don't see anything glaring.

Here's the syntax:

Dim SQL
SQL = "SELECT DISTINCT tblSSTP_RATE.GEN_TAX_RATE_INTRASTATE"
SQL = SQL & "FROM tblALL_ZIP9 INNER JOIN tblSSTP_RATE"
SQL = SQL & "ON tblSSTP_RATE.JURISDICTION_FIPS = tblALL_ZIP9.COUNTYNO OR "
SQL = SQL & "tblSSTP_RATE.JURISDICTION_FIPS = tblALL_ZIP9.CITY_FIPS"
SQL = SQL & "WHERE (tblALL_ZIP9.ZIPNINE = " & Zip9 & ") "

Dim dbconnect
Set dbconnect = CreateObject("ADODB.Connection")
dbconnect.Open "Provider=SQLOLEDB; Data Source=<Server>; Initial Catalog=<db>; User ID=<user>; Password=<pass>;"
Dim RecSet
Set RecSet = CreateObject("ADODB.RecordSet")
RecSet.Open SQL, dbconnect


While Not RecSet.EOF
response.Write("tblSSTP_RATE.GEN_TAX_RATE_INTRASTATE")
RecSet.MoveNext
Wend

Any help would be great. Thanks

 

[PHV]

Lack of space before FROM, ON and WHERE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244