topic Re: query stand alone table. in ArcObjects SDK Questions

query stand alone table.

JayKappy — Mon, 07 Jun 2010 13:13:25 GMT

I am trying to query a stand alone table. I have some code that I can select records in the table but need to send the selected records to a list box in a form.
Right now I am simply running the query from a button on a form. I can later move this run on form open.

I assume that I would have to loop through with the query and set it to the list box....but I am confused on how to do this. Maybe soemthing like this?

Any thoughts?

            Dim varquery As Variant

            For Each varquery In Something
                     frm_Images.ListBox1.AddItem ("ADD VALUE")
            Next vFile


Dim pDoc As IMxDocument
Dim pStandaloneTableColl As IStandaloneTableCollection
Dim pStandaloneTable As IStandaloneTable
Dim pSelectionSet As ISelectionSet
Dim pTableSelection As ITableSelection

'Get the table collection from the map
Set pDoc = ThisDocument
Set pStandaloneTableColl = pDoc.FocusMap

'Get the first table
Set pStandaloneTable = pStandaloneTableColl.StandaloneTable(0)

'Select rows based on a query filter
Dim pQf As IQueryFilter
Set pQf = New QueryFilter
pQf.WhereClause = "UNIQUE_ID1 = 335"
Set pSelectionSet = pStandaloneTable.Table.Select(pQf, esriSelectionTypeIDSet, esriSelectionOptionNormal, Nothing)

'Apply the selection
Set pTableSelection = pStandaloneTable
Set pTableSelection.SelectionSet = pSelectionSet

Re: query stand alone table.

TerryGiles — Sun, 12 Dec 2021 00:24:14 GMT

Use IselectionSet.Search to get a cursor to enumerate through the records selected -

dim pCur as icursor
dim pRow as iRow

pSelectionSet.search nothing,false, pCur
set pRow = pcur.nextrow
do until prow is nothing
  frm_Images.listbox1.additem(pRow.value(2)) 
  set prow = pcur.nextrow
loop

Re: query stand alone table.

JayKappy — Sun, 12 Dec 2021 00:24:17 GMT

Thanks for your response. Although I dont have a selected set of records. What I am doing is looking at a standalone table. Finding the Records that match the feature class selected. Its a table that houses historical data on a feature.
This is an example that I found but dotn really understand why I have to sort.
Is there an easier way....than what I have?

The user selects a point feature, a form opens, and displays the data in the feature class. It then looks at the unique ID of that feature and then populates a list box with the records that match from the dbf table.

I can populate the listbox with the code I have below...BUT what I really want to do is have about 12 attributes from the dbf display in the listbox....right now I have only one begining displayed.
So I either have to :
write more code that will fill some textboxes when that value is clicked in the listbox
or
find a way to simply display 12 or so attributes in the lsitbox....

Does that make sense?

Private Sub CommandButton1_Click()

' Get the UNIQUEID of the feature, this will be used later to the related records
Dim varID As String
varID = frm_Images.TextBox8.Text

Dim pMxDoc As IMxDocument
Set pMxDoc = Application.Document

Dim pMap As IMap
Set pMap = pMxDoc.FocusMap

Dim pTables As IStandaloneTableCollection
Set pTables = pMap

Dim strTableName1 As String
strTableName1 = "oufalls_historical"

Dim i As Integer
For i = 0 To pTables.StandaloneTableCount - 1

  If pTables.StandaloneTable(i).Name = strTableName1 Then
        
            Dim pTable As IStandaloneTable
            Set pTable = pTables.StandaloneTable(i)
        
        ' Hook the Table Class
            Dim tClass As ITable
            Set tClass = pTable.Table

        ' Hook the Table Cursor
            Dim tCursor As ICursor
            Set tCursor = tClass.Insert(True)

        ' Hook the Fields
            Dim tFields As IFields
            Set tFields = tClass.Fields    

    Else
    End If

Next

    Dim pTableSort As ITableSort
    Dim pTrackCancel As ITrackCancel

 'Sort the table based on the field
    Set pTableSort = New TableSort
    Set pTrackCancel = New CancelTracker
    Set pTableSort.Table = pTable
    pTableSort.Fields = "INSPECT_YR"
    pTableSort.Ascending("INSPECT_YR") = False
    pTableSort.Sort pTrackCancel
    Dim AddressCollection As New Collection

    Dim pSortedCursor As ICursor
    Dim pRowBuff As IRowBuffer
        'Set cursor to increment through sorted table, one row at a time
            Set pSortedCursor = pTableSort.Rows
            Set pRowBuff = pSortedCursor.NextRow

        'Loop through table and create a collection of values
            Do While Not pRowBuff Is Nothing
                If IsNull(pRowBuff.Value(7)) Then
                    Set pRowBuff = pSortedCursor.NextRow
                Else
                    Dim varValue As String
                    varValue = pRowBuff.Value(23)
                    'MsgBox varValue
                    If varValue = varID Then
                        'AddressCollection.Add pRowBuff.Value(7)
                        frm_Outfalls_Images.ListBox2.AddItem (pRowBuff.Value(7))
                    Else
                    End If
                    Set pRowBuff = pSortedCursor.NextRow
                End If
            Loop

End Sub

Re: query stand alone table.

TerryGiles — Mon, 07 Jun 2010 21:02:15 GMT

You can set up a listbox to support multiple columns but it think you're limited to 10 of them so that might not help. You may need to look into displaying the data a different way ,e.g. in textboxes, as you mentioned. There are also some VBA datagrid style controls but I've only seen them used bound to tables in Access or Excel.

I think the sorting in the code you have is simply so the values are sorted when added to the listbox.

Sorry, that's all I have..

Re: query stand alone table.

JayKappy — Sun, 12 Dec 2021 00:24:19 GMT

Thanks for your response.....

I simply reused the code in my previous entry above and then changed this part to actually populate a few textboxes usign the OnClick event of the Listbox.....so the form opens and uses the code in the previous entry above to populate the listbox. I then use this code to control what happens when the user selects one of the values in the listbox.

I know there is a better way to do this..I am open to any ideas....

        'Loop through table and create a collection of values
            Do While Not pRowBuff Is Nothing
                If IsNull(pRowBuff.Value(7)) Then
                    Set pRowBuff = pSortedCursor.NextRow
                Else
                    Dim varValue As String
                    varValue = pRowBuff.Value(7)
                    'MsgBox varValue
                    If varValue = varCollectedDate Then
                        ' populate textboxes with attributes of selected related record.
                        varUTIL_ID = pRowBuff.Value(1)
                        varGRATE_ = pRowBuff.Value(2)
                        varCONDTN = pRowBuff.Value(3)
                        varOBSTRUCTN = pRowBuff.Value(4)
                        varOF_RECWTR = pRowBuff.Value(5)
                        varCOMMENTS = pRowBuff.Value(6)
                        
                        frm_Images.TextBox17.Value = varUTIL_ID
                        frm_Images.TextBox18.Value = varGRATE_
                        frm_Images.TextBox19.Value = varCONDTN
                        frm_Images.TextBox20.Value = varOBSTRUCTN
                        frm_Images.TextBox21.Value = varOF_RECWTR
                        frm_Images.TextBox22.Value = varCOMMENTS

                    Else
                    End If
                    Set pRowBuff = pSortedCursor.NextRow
                End If
            Loop