SQL statements

[Corbinde_Bruin]

Hi all,

I'm new to SQL statements and especially within ArcGIS. I'm programming an addin against a file geodatabase in in Desktop 10.1 and I'm getting this error:

An invalid SQL statement was used.
[SELECT FeatureClassAlias FROM tblFeatureClasses WHERE IsSearchable = 'Y' ORDER BY SortSequence]

It works if I take out the ORDER BY SortSequence part, but it obviously doesn't sort at all. I've looked at all the ESRI references on SQL with File geodatabases and can't see my error. It says field names may be delimited by quotes, but that hasn't helped. It also says that ORDER BY won't work if I'm searching in all fields with SELECT *, but that shouldn't be my problem.

An invalid SQL statement was used.
[SELECT "FeatureClassAlias" FROM tblFeatureClasses WHERE "IsSearchable" = 'Y' ORDER BY "SortSequence"]

I'd greatly appreciate help with this issue from someone with SQL experience. I'll also post the code if you think it will help.

Thank you,
cdebruin

  Public Sub LoadTblToCbx(inputTable As ITable, inputFieldName As String, cbxInput As System.Windows.Forms.ComboBox)                  ' Create the query filter.         Dim queryFilter As IQueryFilter2 = New QueryFilter          ' Select the fields to be returned         queryFilter.SubFields = inputFieldName          ' Set the filter to return only searchable         queryFilter.WhereClause = """IsSearchable"" = 'Y'"          ' Use the PostfixClause to sort ascending         Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)         queryFilterDef.PostfixClause = "ORDER BY ""SortSequence"""          ' Output the returned feature class name.         Dim fieldIndex As Integer = inputTable.FindField(inputFieldName)         Dim cursor As ICursor = inputTable.Search(queryFilter, True)         Dim row As IRow = cursor.NextRow()         While Not row Is Nothing             Dim field As String = Convert.ToString(row.Value(fieldIndex))             cbxInput.Items.Add(field)             row = cursor.NextRow()         End While          Marshal.FinalReleaseComObject(cursor)      End Sub

[MichaelVolz]

Corbin:

Can you please post your code when you have it working with the file geodatabase?

Lance:

How come the NIM I referenced says that this functionality is on-hold for file geodatabases, but you say it actually does work?

[Corbinde_Bruin]

It works!

Thanks Lance, you helped a lot.

This Sub takes in 5 parameters, but it should work for anyone looking to populate a combobox from a table with SQL

Public Sub LoadTblToCbx(inputTable As ITable, searchField As String, whereClause As String, sortField As String, cbxInput As System.Windows.Forms.ComboBox)
        'CJD 11/15/2012 - uses SQL statements to query a geodatabase table and write selective values to a combobox

        cbxInput.Items.Clear() 'clear old comboboxitmes

        Dim queryFilter As IQueryFilter2 = New QueryFilter 'Create the query filter.

        'Select the fields to be returned
        queryFilter.SubFields = searchField & "," & sortField
        
        'Set the field to query
        queryFilter.WhereClause = whereClause

        ' Use the PostfixClause to sort ascending
        Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)
        queryFilterDef.PostfixClause = "ORDER BY " & sortField

        Dim fieldIndex As Integer = inputTable.FindField(searchField)
        Dim cursor As ICursor = inputTable.Search(queryFilter, True)
        Dim row As IRow = cursor.NextRow()
        While Not row Is Nothing
            Dim field As String = Convert.ToString(row.Value(fieldIndex))
            cbxInput.Items.Add(field)
            row = cursor.NextRow()
        End While

        Marshal.FinalReleaseComObject(cursor)

    End Sub

[LanceShipman]

Corbin:

Can you please post your code when you have it working with the file geodatabase?

Lance:

How come the NIM I referenced says that this functionality is on-hold for file geodatabases, but you say it actually does work?

The NIMBUS bug is for the File Geodatabase API, where we don't support ORDER BY. We added significant SQL functionality to the File Geodatabase at 10.0 and 10.1. This includes ORDER BY and GROUP BY (10.1). None of this was ported to the File Geodatabase API.