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

Hello,

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

        Try

            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

            pTableSort.Sort(Nothing)

 

            '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

Outcomes