AnsweredAssumed Answered

SQL statements

Question asked by cdebruin on Nov 16, 2012
Latest reply on Nov 16, 2012 by lshipman-esristaff
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,

  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