Retrieving maximum value from sql-vb.net

Question

I am trying to retrieve a maximum value from sql - vb.net

This is my code: I get this error:

Unable to cast object of type 'System.DBNull' to type 'Oracle.DataAccess.Client.OracleDataReader'

Please help. :(

Dim cmd2 As New OracleCommand
cmd2.Connection = conn
'SELECT MAX(LASTNO) FROM d001005 where lbrcode = '104' and CODE1 = 'CASH' and lnodate = '14-NOV-08' 
Dim datepara As String = Date.Now.ToString("dd-MMM-yy")
'datepara = "14-NOV-08"
cmd2.CommandText = "SELECT MAX(LASTNO) FROM d001005 where lbrcode = '" + lbr + "' and CODE1 = 'CASH' and lnodate = '" + datepara + "'"
cmd2.CommandType = CommandType.Text
Dim dr2 As OracleDataReader = cmd2.ExecuteScalar
Dim result As Decimal = dr2.Item("LASTNO")

Answer 1

Method ExecuteScalar() returns System.Object so change your code.

 Dim value=cmd2.ExecuteScalar()
 IF Not IsNoThing(value) Then
    result=Decimal.Parse(value.ToString())
 End IF    

Or you can use ExecuteReader,

   Dim dr2 As OracleDataReader = cmd2.ExecuteReader()
    Dim value as Decimal
    If dr2.Read() Then
       IF Not dr2.IsDbNull(0)
           Value=dr2.GetDecimal(0)
       End IF
    End If

And always use parameters instead of hardcoded sql string.

cmd2.CommandText = "SELECT MAX(LASTNO) FROM d001005 where lbrcode=@lbrcode and CODE1='CASH' and lnodate=@lnodate"
cmd2.Parameters.Add("@lbrcode",SqlDbType.VarChar,20).Value=lbr
.....