topic Table Query in ArcObjects SDK Questions

Table Query

DaveCouture — Sun, 12 Dec 2021 15:55:36 GMT

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

Re: Table Query

KenBuja — Fri, 10 Dec 2021 20:53:09 GMT

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"

Re: Table Query

DaveCouture — Fri, 10 Dec 2021 20:53:12 GMT

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.

Re: Table Query

KenBuja — Fri, 10 Dec 2021 20:53:15 GMT

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

Re: Table Query

DaveCouture — Fri, 13 Jul 2012 12:41:50 GMT

Thanks again Ken, but that didn't work.

Re: Table Query

KenBuja — Fri, 13 Jul 2012 12:55:30 GMT

What kind of table are you working with? A table associated with a feature class? A separate table?

Re: Table Query

JamesCrandall — Fri, 10 Dec 2021 20:53:17 GMT

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

Re: Table Query

DaveCouture — Fri, 10 Dec 2021 20:53:20 GMT

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

Re: Table Query

DaveCouture — Fri, 13 Jul 2012 13:25:29 GMT

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.

Re: Table Query

JamesCrandall — Fri, 10 Dec 2021 20:53:23 GMT

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

Re: Table Query

DaveCouture — Fri, 10 Dec 2021 20:53:26 GMT

Ken's code does find the table, but I'm having problem with what it returns to work with the PerformAttributeQuery snipet. This is what I have

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

        Try

            Dim pMap As IMap = My.ArcMap.Document.ActiveView
            Dim queryFilter As String = "FileNumber = '175980'"
           

            If PerformAttributeQuery(GetTable("CSJGISSDE.GISDATA.Subdivision_Plans_Table", pMap), queryFilter) Is Nothing Then
                MsgBox("Not Found")
            Else
                MsgBox("Found")
            End If


        Catch ex As Exception
            MsgBox("This action cannot be done. Save your project, then restart ArcMap.")
        End Try
    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 ' create the where clause statement

        ' 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


    Private Function GetTable(ByVal InTableName As String, ByVal 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

Re: Table Query

JamesCrandall — Fri, 10 Dec 2021 20:53:29 GMT

Ken's code does find the table, but I'm having problem with what it returns to work with the PerformAttributeQuery snipet. This is what I have, which always end up to the Exception Error Message.:

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

        Try

            Dim pMap As IMap = My.ArcMap.Document.ActiveView
            Dim queryFilter As String = "FileNumber = '175980'"
           
           PerformAttributeQuery(GetTable("CSJGISSDE.GISDATA.Subdivision_Plans_Table", pMap), queryFilter)

        Catch ex As Exception
            MsgBox("This action cannot be done. Save your project, then restart ArcMap.")
        End Try
    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 ' create the where clause statement

        ' 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


    Private Function GetTable(ByVal InTableName As String, ByVal 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

Ah. I was under the impression that his code didn't work. It does, please give him some credit!!! I was also misunderstanding you and thinking you couldn't just get the ITable reference.

I don't see the error message (didn't make it into your last post). But the only thing I do notice is in your WHERE clause, you are wrapping what appears to be an integer/whole value into single quotes:

Dim queryFilter As String = "FileNumber = '175980'"

maybe change this to:

Dim queryFilter As String = "FileNumber = 175980"

Hope that helps!!!

Re: Table Query

DaveCouture — Fri, 13 Jul 2012 14:05:59 GMT

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.

Re: Table Query

JamesCrandall — Fri, 13 Jul 2012 14:12:18 GMT

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.

😄

So 175980 is a string? hmmmmmm. "FileNumber" suggests it is a whole number. May want to have a talk with the SDE administrator! 😄

Sorry I couldn't help more.

Re: Table Query

KenBuja — Fri, 13 Jul 2012 14:26:28 GMT

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

Re: Table Query

DaveCouture — Fri, 13 Jul 2012 15:43:12 GMT

Ken, thank you, that did it!!

James, thanks for trying..haha! We call the field FileNumber and it's a String Field, because File Numbers can be alpha-numeric 😉

Re: Table Query

JamesCrandall — Fri, 13 Jul 2012 18:18:06 GMT

Ken, thank you, that did it!!

James, thanks for trying..haha! We call the field FileNumber and it's a String Field, because File Numbers can be alpha-numeric 😉

Yes! Glad you are up and running, Dave. Contributors like Ken make this place such a great resource.

You sent me off on a research stint for a while as I have recently found myself moved into more of a database/data modeling role. The naming of your field �??FileNumber�?� and not storing actual numbers but rather alpha-numeric/character string values made me take a closer look at some existing table structures we have.

Initially, I thought your problem might have been related to placing single quotes around
the FileNumber value in your where clause. And I think this could actually be a potential issue for some, and prove to be a headache for developers in larger organizations certainly.

It might seem like a nitpick, but it sounds like that field is really more of a unique identifier: "File_Id". "FileNumber" seems like it should model an actual number rather than a . Obviously it works for you all -- I am just a big proponent of modelling database objects as closely as what they should represent. It has actually been a benefit over the long run in following that approach.

So then I was asking myself, �??why would anyone want to name a field that holds character values with �??Number�?� in the name?�?�.

I posed this on the MSDN forums and got an excellent reply. Basically, my curiosity as to why you would name your character field with �??Number�?� in the name can be explained by how we all are conditioned to hearing things. For example, when asked to write my Driver�??s license it typically asks �??write your Driver�??s License NUMBER�?�. Well, it isn�??t *really* a number, rather it�??s a string of characters and numbers.

However, it could be important in database design and implementation, as well as programming against that database design, to make that distinction clear and name something what it really is.

Re: Table Query

DaveCouture — Mon, 16 Jul 2012 11:29:16 GMT

James - I appreciate your concern, but the reason we named the field this way, is because our Archives make reference to File Numbers (old system). For consistency, we named the field this way. This field is not the Unique ID, it's just a reference number to the paper documents, that are in the Archives. I might be new at .NET, but I've been managing Geodata for 15 years 😉