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.C hemicalCode) FROM CHEMICALS", CN, adOpenDynamic, adLockOptimisti c

If rsCheNo.RecordC ount <> 0 Then
Do While Not rsCheNo.EOF
CheNo = rsCheNo.Fields( 0)
rsCheNo.MoveNex t
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) [U]AS maxOfChCode[/U] 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