AnsweredAssumed Answered

VB.Net code works differently between 9.3 Oracle and 10.2.2 SQL Server

Question asked by AmyCOD on Jun 2, 2017
Latest reply on Jun 5, 2017 by AmyCOD


We have a tool that has a task to look at an existing feature class to find the highest number in a field called INDEX and return the next number. It then adds the next numbers to features that will be loaded into the main feature class.


When the tool is used and the data source is a 9.3 Oracle SDE point feature class it works correctly by returning the next highest number, but when I tried using it on a 10.2.2 SQL Server point feature class, it returns the next "available" number. In this case a feature had been deleted freeing up a number that was not the highest.  It was this number that the tool returned for the SQL Server feature class.


Here is the code for the function:  

Any ideas why it is doing this?  Is there a difference in how the code behaves between the two versions?

I am going to create a point feature class that has no number gap and see if it works to confirm the possible issue.


Any thoughts appreciated,

Amy Rose


    Public Function GetNextHighestINDEX() As Long


            Dim pDS As IDataset

            Dim pTable As ITable

            Dim pCursor As ICursor

            Dim pRow As IRow

            Dim pQueryFilter As IQueryFilter

            Dim i As Long


            Dim pMxDoc As IMxDocument

            Dim pMap As IMap


            Dim pFLayer As IFeatureLayer

            Dim pFCls As IFeatureClass


            pMxDoc = My.ArcMap.Document

            pMap = pMxDoc.ActiveView


            '--- Set the input Point feature class


            pFCName = "GIS.POINTS"


            For i = 0 To pMap.LayerCount - 1


                If TypeOf pMap.Layer(i) Is IFeatureLayer Then


                    pFLayer = pMap.Layer(i)

                    pFCls = pFLayer.FeatureClass

                    pDS = pFCls


                    If UCase(pDS.Name) = UCase(Trim(pFCName)) Then

                        NodesFC = pFCls

                        Exit For

                    End If


                End If


            Next i


            If NodesFC Is Nothing Then

                MsgBox("No GIS.POINTS Layer Found in Map")

                Exit Function

            End If


            pTable = NodesFC


            pQueryFilter = New QueryFilter

            pQueryFilter.WhereClause = "INDEX is not null and (INDEX Like '1_____'  or INDEX Like '2_____' or INDEX Like '3_____'  or INDEX Like '4_____' or INDEX Like '5_____'  or INDEX Like '6_____' or INDEX Like '7_____'  or INDEX Like '8_____' or INDEX Like '9_____'  or INDEX Like '10_____')"


            Dim pTableSort As ITableSort = New TableSortClass()

            'pTableSort.Table = Table


            With pTableSort

                .Fields = "INDEX"

                .Ascending("INDEX") = False 'False for descending order

                .QueryFilter = pQueryFilter

                .Table = pTable

            End With


            'apply sort



            'Use cursor to find highest number

            pCursor = pTableSort.Rows


            'There's no need to loop cause you just want the highest number so the first row should be it

            'Dim pRow As IRow

            pRow = pCursor.NextRow


            GetNextHighestINDEX = pRow.Value(NodesFC.Fields.FindField("INDEX")) + 1

        Catch ex As Exception

            MsgBox(ex.Message & ex.StackTrace)

        End Try

    End Function