Table Query

1944
17
Jump to solution
07-12-2012 09:37 AM
DaveCouture
New Contributor III
I'm trying to perform a Table Query using the PerformAttributeQuery snipet and I'm just trying to figure out how to select a table, for the function.


     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

         Dim queryFilter As String = "Code = '" + ComboBox1.Text.Trim + "'"
         Dim table as ESRI.ArcGIS.Geodatabase.ITable = ?????       
       
           MsgBox(PerformAttributeQuery(table, queryFilter)) 
  
     End Sub


    Public Function PerformAttributeQuery(ByVal table As ESRI.ArcGIS.Geodatabase.ITable, ByVal whereClause As System.String) As ESRI.ArcGIS.Geodatabase.ICursor

        Dim queryFilter As ESRI.ArcGIS.Geodatabase.IQueryFilter = New ESRI.ArcGIS.Geodatabase.QueryFilterClass()
        queryFilter.WhereClause = whereClause
        Dim cursor As ESRI.ArcGIS.Geodatabase.ICursor = table.Search(queryFilter, False)

        Return cursor

    End Function
0 Kudos
1 Solution

Accepted Solutions
KenBuja
MVP Honored Contributor
James, credit was given to Ken.  Ken usually always figure out my problems, so I'm never shy to give him credits..haha!  The WhereClause is fine.  You need single quotes to query strings.

The code doesn't produces an error, it just act like the query is always true, even when it's not.


The snippet it will always return a cursor, whether something was found or not. If you want to do additional testing to figure out if the whereclause will return anything, you can do something like this.

Public Function PerformAttributeQuery(ByVal table As ESRI.ArcGIS.Geodatabase.ITable, ByVal whereClause As System.String) As ESRI.ArcGIS.Geodatabase.ICursor     Dim queryFilter As ESRI.ArcGIS.Geodatabase.IQueryFilter = New ESRI.ArcGIS.Geodatabase.QueryFilterClass()      queryFilter.WhereClause = whereClause ' create the where clause statement       if table.RowCount(queryFilter) = 0 then Return Nothing      ' query the table passed into the function and use a cursor to hold the results     Dim cursor As ESRI.ArcGIS.Geodatabase.ICursor = table.Search(queryFilter, False)     Return cursor  End Function

View solution in original post

0 Kudos
17 Replies
KenBuja
MVP Honored Contributor
What's table do you want to perform this on? Here are some different ways to get a table.

If you want an external table that you get via a path name, use

    Public Function OpenTable(ByVal directory As String, ByVal TableName As String, ByVal Type As String) As ESRI.ArcGIS.Geodatabase.ITable
        Dim pWS As ESRI.ArcGIS.Geodatabase.IWorkspace
        Dim pWSFactory As ESRI.ArcGIS.Geodatabase.IWorkspaceFactory
        Dim pFWorkspace As ESRI.ArcGIS.Geodatabase.IFeatureWorkspace
        Dim pTable As ESRI.ArcGIS.Geodatabase.ITable

        Try
            If Type = "DBF" Then
                pWSFactory = New ESRI.ArcGIS.DataSourcesFile.ShapefileWorkspaceFactory
            ElseIf Type = "File Geodatabase" Then
                pWSFactory = New ESRI.ArcGIS.DataSourcesGDB.FileGDBWorkspaceFactory
            ElseIf Type = "Personal Geodatabase" Then
                pWSFactory = New ESRI.ArcGIS.DataSourcesGDB.AccessWorkspaceFactory
            ElseIf Type = "SDE" Then
                pWSFactory = New ESRI.ArcGIS.DataSourcesGDB.SdeWorkspaceFactory
            End If
            pWS = pWSFactory.OpenFromFile(directory, 0)
            pFWorkspace = pWS

            pTable = pFWorkspace.OpenTable(TableName)

            Return pTable
        Catch ex As Exception
            Windows.Forms.MessageBox.Show("Open Table error", ex.Message)
            Return Nothing
        End Try

    End Function


For a table from an existing featureclass, use


Dim table as ESRI.ArcGIS.Geodatabase.ITable = CType(pFeatureClass,  ESRI.ArcGIS.Geodatabase.ITable)


For a table you've selected in your content, use the Snippet "Get Selected Table in Contents View"
0 Kudos
DaveCouture
New Contributor III
Thanks Ken.  I'm trying to access an SDE Table that's already loaded in ArcMap.  I just need to know how to assign the table (by its name), in the same fashion as you would assign a Feature Class by it's name.I really want to use the PerformAttributeQuery Snipet and I just need to know how to pass the ITable parameter in the function.

Public Function PerformAttributeQuery(ByVal table As ESRI.ArcGIS.Geodatabase.ITable, ByVal whereClause As System.String) As ESRI.ArcGIS.Geodatabase.ICursor


I tried something like this, but it doesn't work:


  Dim pMap As IMap = My.ArcMap.Document.ActiveView
            Dim queryFilter As String = "FileNumber = '175980'"
            Dim table As ITable = DirectCast(pMap.FindByName("CSJGISSDE.GISDATA.Subdivision_Plans_Table"), FeatureLayer).ITable

            MsgBox(PerformAttributeQuery(table, queryFilter))


The Get Selected Table in Contents View snipet isn't the function that I'm looking for, because it returns the selected Table. The table won't be selected, it will just be in the TOC.
0 Kudos
KenBuja
MVP Honored Contributor
Use IStandaloneTableCollection. Something like this


Private Function GetTable(InTableName As String, pMap as IMap) As ITable

    Dim pStTabColl As IStandaloneTableCollection

    pStTabColl = pMap

    For j as Integer = 0 To pStTabColl.StandaloneTableCount
        If (pStTabColl.StandaloneTable(j).Name = InTableName) Then
            Return pStTabColl.StandaloneTable(j)
        End If
    Next j

    Return Nothing

End Function
0 Kudos
DaveCouture
New Contributor III
Thanks again Ken, but that didn't work.
0 Kudos
KenBuja
MVP Honored Contributor
What kind of table are you working with? A table associated with a feature class? A separate table?
0 Kudos
JamesCrandall
MVP Frequent Contributor
Thanks Ken.  I'm trying to access an SDE Table that's already loaded in ArcMap.  I just need to know how to assign the table (by its name), in the same fashion as you would assign a Feature Class by it's name.



Try:

        
'assumes you have set your m_pApp reference already
        Dim i As Integer
        Dim pLayer As IFeatureLayer
        pLayer = Nothing

        pDoc = m_pApp.Document
        pMap = pDoc.FocusMap

        For i = 0 To pMap.LayerCount - 1
            If pMap.Layer(i).Name = "Your SDE Table Name Here" Then
                pLayer = pMap.Layer(i)
                Exit For
            End If
        Next


'now set your ITable
Dim pTable As ITable
Set pTable = pLayer.FeatureClass
0 Kudos
DaveCouture
New Contributor III
Ken, as I've mentionned, I'm working with an SDE Table (single Table inside an SDE (no feature classes or joins or relates)).  I found a VB6 code that does what I want.  I'm trying to convert it to VB.NET.  All I want to do really, is query a Single Table that's already loaded in ArcMap.


VB6 Code:



Public Sub TableSel()
  Dim pMxDoc As IMxDocument
  Dim pMap As IStandaloneTableCollection
  Dim pStdAloneTbl As IStandaloneTable
  Dim pTableSel As ITableSelection
  Dim pQueryFilt As IQueryFilter
  Dim pSelSet As ISelectionSet
  ' Get the standalone table from the map
  Set pMxDoc = Application.Document
  Set pMap = pMxDoc.FocusMap
  Set pStdAloneTbl = pMap.StandaloneTable(0)
  Set pTableSel = pStdAloneTbl
' Make the query filter
  Set pQueryFilt = New QueryFilter
  pQueryFilt.WhereClause = "POP1990 > 10000000"
' Perform the selection
  pTableSel.SelectRows pQueryFilt, esriSelectionResultNew, False
' Report how many rows were selected
  Set pSelSet = pTableSel.SelectionSet
  MsgBox pSelSet.Count & " rows selected in " & pStdAloneTbl.Name
End Sub

0 Kudos
DaveCouture
New Contributor III
Thanks James, but that didn't work.  I think it's because you make reference to a Feature Class Table.  I want to query a Single (stand-alone) Table, that's inside an SDE.
0 Kudos
JamesCrandall
MVP Frequent Contributor
Thanks James, but that didn't work.  I think it's because you make reference to a Feature Class Table.  I want to query a Single (stand-alone) Table, that's inside an SDE.  I can't believe how complicated this is..lol


Ken's approach didn't work?  (sorry I cannot test this right now).  What exactly happens?  Perhaps you passed in the incorrect name (I think there are some strict requirements for SDE referenced tables perhaps.  you might need to add the schema name in the table name "dbo.TableName" --- not sure).

Private Function GetTable(InTableName As String, pMap as IMap) As ITable

    Dim pStTabColl As IStandaloneTableCollection

    pStTabColl = pMap

    For j as Integer = 0 To pStTabColl.StandaloneTableCount
        If (pStTabColl.StandaloneTable(j).Name = InTableName) Then
            Return pStTabColl.StandaloneTable(j)
        End If
    Next j

    Return Nothing

End Function
0 Kudos