I am trying to retrieve a MAX value from a column in a table. The issue is if the table contains NO records...I get an error saying:
"Conversion from type 'DBNULL' to type 'Integer' is not valid"
I understand why I get the error because it's returning a NULL value. I'm just not sure how to structure the statement. I know how to return a MAX value in a populated table, just not in an empty table.
Public Function GetMaxID(ByVal tbl As String, ByVal keyFld As String, ByRef EH As ErrorHandling.ErrorHandler)
If EH.Bool Then
sSQL = "SELECT MAX(ISNULL(" & keyFld & ",0)) + 1 FROM " & tbl
cmd = New SqlCommand(sSQL, cnn)
EH.Number = cmd.ExecuteScalar()
Catch ex As Exception
EH.ErrorMessage = "GetMaxID() - " & ex.Message & "...Contact Engineering!" & "~E"