SELECT MAX ID from feature

JayKappy · ‎05-04-2010

I have some code that calls a sub to get the largest unique number from a attribute.

THIS WORKS GREAT ON MY PC, but as soon as I send it over to my Trimble Juno it erros out on the msgbox....

How can this work on my PC and not in the Juno????? I am besides myself right now....Why is the error occuring on a simple message box?????

I then created 3 more fields in the dataset. I made them (Long integer, Double, Text) none of them worked...
But they work fine on my PC....

UGGGGGGGG

Any thoughts?

PLEASE HELP!!!!!!!


Option Explicit 
        Dim varLargestValue 

Sub X
     ' Some code
     Call FindLargestValue

       ' Show to largest value from the sub being called...
     Msgbox varLargestValue 

     ' Some Code
End Sub

Sub FindLargestValue

 Dim strFileName2
 strFileName2 = varAXFFileName_2

       '++ open the selected AXF file
       Dim pDS6
       Set pDS6 = OpenAXF(strFileName2)
       If (pDS6 Is Nothing) Then
                       Console.Print "Open DataSource failed"
                       Exit Sub
       End If

 ' Set up recordset query
 Dim  pRS6
 Set pRS6 = pDS6.Execute("SELECT MAX(UNIQUEID2) AS MaximumID FROM Outfalls")

 '++ step through the records and output the first attribute to the Console window
 pRS6.MoveFirst
        
 Do While Not pRS6.EOF 

                 MaxID = (pRS6("MaximumID").value)
  pRs6.MoveNext 
 Loop

varLargestValue = MaxID
Msgbox varLargestValue 


  ' Close Recordset
  pRS6.Close
  Set pRS6 = Nothing
End Sub

AndrewDuff · ‎05-06-2010

I have the same exact problem. Here is my version of the code that causes the same thing. I have tried two different pda's (Trimble Juno ST and GETAC PS535F). This is driving me crazy!! Is this a bug in the software - SQL Server Compact or something?

Andy

Public Function ReturnNextLineID(strAXFFilePath, strAnimalIDValue)
'Built on the notion you are working with one animal per day
    'open the selected AXF file
    Dim pDS
    Set pDS = OpenAXF(strAXFFilePath)
    If (pDS Is Nothing) Then
        Application.MessageBox "Open DataSource failed, Line 457 of Triangulation_Tools.vbs", , "WDFW Telemetry Tools"
        Exit Function
    End If

'Create the SQL Statement
Dim strSQL
strSQL = "SELECT MAX(LINEID) AS MAXLINEID FROM [TELEMETRYLINES] WHERE ANIMALID = '" & strAnimalIDValue & "'"

Dim pRS, MaxValue, NextValue
Set pRS = pDS.Execute(strSQL)

Application.MessageBox "SQL SENT Value =" & CStr(strSQL)

Application.MessageBox "Last Global Value =" & CStr(gblLineID)

    If (Not pRS Is Nothing) Then
        pRS.MoveFirst
Do While Not pRS.EOF
   'If IsNull(pRS.Fields("MAXLINEID").value) = True then
   If (pRS.Fields("MAXLINEID").IsNull) = True then
    '
       NextValue = 1

   Else
    'Application.MessageBox pRS.Fields("MAXLINEID").value
    'What is returned here on the device is different than on the desktop, WTF?
    MaxValue = CInt(pRS.Fields("MAXLINEID").value)
    'Application.MessageBox MaxValue
    NextValue = MaxValue + 1
   End If
   pRs.MoveNext

        Loop

If IsNull(pRS.Fields("MAXLINEID").value) = False Then
   Application.MessageBox "MAXLINEID Value =" & CStr(pRS.Fields("MAXLINEID").value)
Else
   Application.MessageBox "MAXLINEID Value is evaluated to Null"
End If
Application.MessageBox "NextValue =" & NextValue

Else
Application.Message "An error Occurred Getting the LineID's", , "WDFW Telemetry Tools"
End If

    pRS.Close
    Set pRS = Nothing
pDS.Close
    Set pDS = Nothing
ReturnNextLineID = NextValue
End Function

AndrewDuff · ‎05-06-2010

Its definately in that max SQL statement because the following works just fine.

'this will return the next line ID using and AXF and SQL
Public Function ReturnNextLineID_NoMax(strAXFFilePath, strAnimalIDValue)
'Built on the notion you are working with one animal per day
    'open the selected AXF file
    Dim pDS
    Set pDS = OpenAXF(strAXFFilePath)
    If (pDS Is Nothing) Then
        Application.MessageBox "Open DataSource failed, Line 457 of Triangulation_Tools.vbs", , "WDFW Telemetry Tools"
        Exit Function
    End If

'Create the SQL Statement
Dim strSQL
strSQL = "SELECT LINEID FROM [TELEMETRYLINES] WHERE ANIMALID = '" & strAnimalIDValue & "'"

Dim pRS, MaxValue, NextValue, CurrentValue
Set pRS = pDS.Execute(strSQL)

'Application.MessageBox "SQL SENT =" & CStr(strSQL)
'Application.MessageBox "Last Global Value =" & CStr(gblLineID)

MaxValue = 0

    If (Not pRS Is Nothing) Then
        pRS.MoveFirst
Do While Not pRS.EOF
   If (pRS.Fields("LINEID").IsNull) = True then
    Exit Do
   Else
    CurrentValue = CInt(pRS.Fields("LINEID").value)
    'Application.MessageBox "CurrentValue= " & CStr(CurrentValue)
    'Application.MessageBox "MaxValue= " & CStr(MaxValue)
    If CurrentValue > MaxValue Then
     MaxValue=CurrentValue
    End If

   End If
   pRs.MoveNext

        Loop

NextValue = MaxValue + 1
'Application.MessageBox "NextValue At End Of Routine (Will Be Used)= " & CStr(NextValue)
Else
Application.Message "An error Occurred Getting the LineID's", , "WDFW Telemetry Tools"
End If

    pRS.Close
    Set pRS = Nothing
pDS.Close
    Set pDS = Nothing
ReturnNextLineID_NoMax = NextValue
End Function

JayKappy · ‎05-07-2010

I was able to find a solution....it works for me.....
I dont know what was going on although I do know that I was relating the tables by the UNIQUEID....I went back and added another field UNIQUEID2 and copied all the values from UNIQUEID to UNIQUEID2....In the form I am simply updating both fields when I enter a new feature. This is working for me...

Option Explicit
Dim varLargestValue

Sub Something

' snip 
  
     ' Get the largest value 
     Dim largestValue
     largestValue = 0
     Call FindLargestValue(largestValue)

     varLargestValue = largestValue

' snip

End Sub



Sub FindLargestValue(varlargestValue)

 Dim MaxID 

 Dim strFileName2
 strFileName2 = varAXFFileName_2

       '++ open the selected AXF file
       Dim pDS6
       Set pDS6 = OpenAXF(strFileName2)
       If (pDS6 Is Nothing) Then
                    Console.Print "Open DataSource failed"
                    Exit Sub
       End If

 ' Set up recordset query
 Dim  pRS6
 Set pRS6 = pDS6.Execute("SELECT MAX(UNIQUEID2) AS MaximumID FROM Outfalls")

 ' Move first
 pRS6.MoveFirst
 ' Loop recordset and get number of records         
 Do While Not pRS6.EOF 
  MaxID = (pRS6("MaximumID").value)
  pRs6.MoveNext 
 Loop

 varLargestValue = MaxID
 
 ' Clear Variables     
 MaxID = ""

 ' Close Recordset
 pRS6.Close
 Set pRS6 = Nothing

End Sub