Click here to Skip to main content
15,867,453 members
Home / Discussions / Database
   

Database

 
GeneralRe: return Table name + Column Name in sql query Pin
Harvey Saayman17-Jul-08 20:19
Harvey Saayman17-Jul-08 20:19 
GeneralRe: return Table name + Column Name in sql query Pin
Mycroft Holmes17-Jul-08 20:44
professionalMycroft Holmes17-Jul-08 20:44 
AnswerRe: return Table name + Column Name in sql query Pin
MBCDC21-Jul-08 0:39
MBCDC21-Jul-08 0:39 
Questiondynamically applyin styles based on DB Value to reports Pin
ch.ramesh16-Jul-08 19:06
ch.ramesh16-Jul-08 19:06 
AnswerRe: dynamically applyin styles based on DB Value to reports Pin
Ashfield16-Jul-08 20:13
Ashfield16-Jul-08 20:13 
GeneralRe: dynamically applyin styles based on DB Value to reports Pin
ch.ramesh16-Jul-08 20:21
ch.ramesh16-Jul-08 20:21 
GeneralRe: dynamically applyin styles based on DB Value to reports Pin
Ashfield17-Jul-08 2:24
Ashfield17-Jul-08 2:24 
QuestionMultiple queries within an ADO connection Pin
Airickjay16-Jul-08 9:50
Airickjay16-Jul-08 9:50 
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 = 0

      For Each sLeaseList In aLeaseList
            if LabelCount <> 0 then
                  FinalLabel = vbNewLine + FinalLabel
            End If

            LabelCount = 1

            FinalLabel = sLeaseList + FinalLabel

      Next

      Erase aLeaseList

'If there is only one lease in the parcel

Else

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

'      Dim rsLse
'      set rsLse = CreateObject("ADODB.Recordset")

'      rsLse.Open strLseQuery, ADOConn, 3, 1, 1



      'building text string if there is a name

'      If rsLse.Fields("L_NAME").Value <> "" then
            
'            FinalLabel = rsLse.Fields("L_NAME").Value

'            if IsNull(rsLse.Fields("F_NAME1") = "False" then
'                  strLabelInfo = rsLse.Fields("L_NAME")&", "&LEFT(rsLseFields("F_NAME1"), 1)
'            End If

'            if IsNull(rsLse.Fields("F_NAME2") = "False" then
'                  strLabelInfo = strLabelInfo + "/" & LEFT(rsLse.Fields("F_NAME2"), 1)
'            End If

'            if IsNull(rsLse.Fields("ADD_INFO") = "False" then
'                  strLabelInfo = strLabelInfo & " " & rsLse.Fields("ADD_INFO")
'            End If
'      End If

FinalLabel = aLeaseList

End If

'closing connections

'rsLse.Close
'Set rsLse = Nothing
ADOConn.Close
Set ADOConn = Nothing

FindLabel = FinalLabel

End Function

AnswerRe: Multiple queries within an ADO connection Pin
tsaunders16-Jul-08 15:09
tsaunders16-Jul-08 15:09 
AnswerRe: Multiple queries within an ADO connection Pin
andyharman17-Jul-08 7:15
professionalandyharman17-Jul-08 7:15 
AnswerRe: Multiple queries within an ADO connection Pin
Airickjay17-Jul-08 8:30
Airickjay17-Jul-08 8:30 
AnswerRe: Multiple queries within an ADO connection Pin
MBCDC21-Jul-08 0:59
MBCDC21-Jul-08 0:59 
Questionerror when deleting with the WITH clause? Pin
harcaype16-Jul-08 7:01
harcaype16-Jul-08 7:01 
AnswerRe: error when deleting with the WITH clause? Pin
tsaunders16-Jul-08 15:02
tsaunders16-Jul-08 15:02 
GeneralRe: error when deleting with the WITH clause? Pin
harcaype16-Jul-08 19:13
harcaype16-Jul-08 19:13 
AnswerRe: error when deleting with the WITH clause? Pin
leoinfo17-Jul-08 4:19
leoinfo17-Jul-08 4:19 
GeneralRe: error when deleting with the WITH clause? Pin
harcaype17-Jul-08 5:56
harcaype17-Jul-08 5:56 
QuestionHow to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 1:51
fracalifa16-Jul-08 1:51 
AnswerRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 2:18
A Wong16-Jul-08 2:18 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 3:01
fracalifa16-Jul-08 3:01 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 3:15
A Wong16-Jul-08 3:15 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 3:32
fracalifa16-Jul-08 3:32 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 3:41
A Wong16-Jul-08 3:41 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 4:10
fracalifa16-Jul-08 4:10 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 4:38
A Wong16-Jul-08 4:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.