Multiple queries within an ADO connection

Airickjay

First off, I really don't know much about vbScript, so hopefully what I have thrown together won't look too awful. I am trying to come up with a way to label parcels in ArcGIS, and I am about ready to throw my computer out the window. I have to use vbScript to create the labels by connecting to a database pulling the data from multiple tables, assembling a text string and assigning it to a polygon. Unfortunately the vbScript engine in ArcGIS doesn't support joins, so I came up with a way to create one query, get the data I need from one table and stuff it into an array (if necessary) for the second query. Unfortunately, I am getting bombarded with errors when I try to do the second query, and I have scoured search engines for a solution with no luck at all. Everything I found tells me how to do one query, but doesn't address the problem of running multiple queries. Any help that I can be given would be greatly appreciated. Everything works fine until I try to do the second query. I'm not sure what the procedure is to do another query, or if it's even possible. Here is what I have written so far, including the portions that don't work (they are commented out). Thanks a lot in advance!

Function FindLabel ([FEATURE_ID])

Dim strPrclQry, strLseQry, strinfo, LeaseNum, aLeaseList, LabelCount, FinalLabel, sLeaseList

'form query string
strPrclQry = "SELECT LEASE_NAME FROM TRACTS WHERE TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"' ORDER BY LEASE_NAME ASC"

Dim ADOConn
set ADOConn = CreateObject("ADODB.Connection")
Dim rsPrcl
set rsPrcl = CreateObject("ADODB.Recordset")

ADOConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=.\DART_PROJECTS.mdb"

ADOConn.CursorLocation = 3

rsPrcl.Open strPrclQry, ADOConn, 3, 1, 1

'if no record is found, return empty string

Select Case rsPrcl.RecordCount
Case -1, 0
strInfo = " "
Case 1
'reading only the first record

  LeaseNum = rsPrcl.Fields("LEASE\_NAME").Value
  strInfo =  LeaseNum + strInfo  
  aLeaseList = strInfo

Case Else

  for I = 1 to rsPrcl.RecordCount
        if I <> 1 then
           strInfo = " " + strInfo
        End If

        LeaseNum = rsPrcl.Fields("LEASE\_NAME").Value
        strInfo = LeaseNum + strInfo
        rsPrcl.Movenext
  Next

  aLeaseList = Split(strInfo)

End Select

'closing connections

rsPrcl.Close
Set rsPrcl = Nothing

'put arrays into labels

If IsArray(aLeaseList) <> 0 then

LabelCount

tsaunders

Try releasing your ADO.Recordset - you are using it twice in your code without a release - or create two record sets.

andyharman

I would say that the following line looks iffy:

strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"

If LeaseNum values are numbers then your aLeaseList should be comma-separated, then use:

strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID IN ("&aLeaseList&")"

If that doesn't work then post the value of your strLseQry variable to this thread.

Airickjay

Thanks for the quick responses, I've made some real progress and have gotten the queries to work!

MBCDC

Don´t know exactely if this will help, but you do not necessarily must use "JOIN" to get data out of several tables. You can define the connection between two or more tables in the "Where" clause. In your case it would look something like this: SELECT TRACTS.LEASE_NAME, LEASES.L_NAME, LEASES.F_NAME1, LEASES.F_NAME2 FROM TRACTS, LEASES WHERE TRACTS.LEASE_NAME=LEASES.L_NAME AND TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"' ORDER BY LEASE_NAME ASC" You will receive 1 recordset with all needed data. There is one little blemish within that kind of SQL. To connect several tables by a "JOIN" statement works quicker than only using the "Where" clause because "JOIN" is an optimized procedure. But in Your case that doesn´t matter!