Table Query

2766
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
17 Replies
DaveCouture
New Contributor III
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

0 Kudos
JamesCrandall
MVP Frequent Contributor
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!!!
0 Kudos
DaveCouture
New Contributor III
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.
0 Kudos
JamesCrandall
MVP Frequent 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.


😄

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.
0 Kudos
KenBuja
MVP Esteemed 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
0 Kudos
DaveCouture
New Contributor III
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 😉
0 Kudos
JamesCrandall
MVP Frequent Contributor
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.
0 Kudos
DaveCouture
New Contributor III
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 😉
0 Kudos