Get the max value from database and display it in text box using the variable

[vagaisuresh]

Friends,

I want to get and display the Max value from database records. I used some codes. It displays the error message 'NULL"
how to rectify this.

Dim CheNo As Integer
Dim rsCheNo As New ADODB.Recordset
rsCheNo.Open "SELECT max(CHEMICALS.ChemicalCode) FROM CHEMICALS", CN, adOpenDynamic, adLockOptimistic

If rsCheNo.RecordCount <> 0 Then
Do While Not rsCheNo.EOF
CheNo = rsCheNo.Fields(0)
rsCheNo.MoveNext
Loop
End If

rsCheNo.Close
CheNo = CheNo + 1
Text1 = CheNo

Thanks,
Suresh

[pureenhanoi]

Dim CheNo As Integer
Dim rsCheNo As New ADODB.Recordset
rsCheNo.Open "SELECT max(CHEMICALS.ChemicalCode) AS maxOfChCode FROM CHEMICALS", CN, adOpenDynamic, adLockOptimistic
If rsCheNo.RecordCount <> 0 Then
        CheNo = rsCheNo.Fields("maxOfChCode")
        CheNo = CheNo + 1
        Text1 = CheNo
End If
rsCheNo.Close
 

Just have only one Max value is selected. so do not need using While ... Loop