conversion problem: dbnull to date

[Bob]

Hi,

i need the most recent date but only for a certain category:
'dat' is DateTime type in sql server.

dim dat as datetime
sql = "select max(dat) from mytable where cat='x'
comd = New SqlCommand(sql, mConnection)
dat = comd.ExecuteScalar

If there are no cat= 'x', comd.executescaler returns Null and so i get the
error:
"Conversion from type 'DBNull' to type 'Date' is not valid."

I tried this: If Not IsDBNull(0) Then dat = comd.ExecuteScalar
or with .HasRows ...
but it doesn't work.

Thanks for help
Bob

 

[Paul Shapiro]

Declare dat as a nullable date. I don't know the VB.net syntax, but you
should be able to find it in the documentation. Or you could use
Select count(dat) as RowCount, max(dat) as DateMax ..., and then open the
query instead of using ExecuteScalar. You can check that the row count is
greater than 0 before setting the max date variable.

 

[Bob]

Thanks

Declare dat as a nullable date. I don't know the VB.net syntax, but you
should be able to find it in the documentation. Or you could use
Select count(dat) as RowCount, max(dat) as DateMax ..., and then open the
query instead of using ExecuteScalar. You can check that the row count is
greater than 0 before setting the max date variable.

 

[imransyed63]

Dim objDat as Object = nothing
dim dat as datetime
sql = "select max(dat) from mytable where cat='x'
comd = New SqlCommand(sql, mConnection)
objDat = comd.ExecuteScalar
if not (objDat is DBNULL.Value Or objDat is nothing) then dat = cdate(objDat)

 

[Hans Kesting]

Bob submitted this idea :

Hi,

i need the most recent date but only for a certain category:
'dat' is DateTime type in sql server.

dim dat as datetime
sql = "select max(dat) from mytable where cat='x'
comd = New SqlCommand(sql, mConnection)
dat = comd.ExecuteScalar

If there are no cat= 'x', comd.executescaler returns Null and so i get the
error:
"Conversion from type 'DBNull' to type 'Date' is not valid."

I tried this: If Not IsDBNull(0) Then dat = comd.ExecuteScalar
or with .HasRows ...
but it doesn't work.

Thanks for help
Bob

If nullable date doens't work, then first put the result from
comd.ExecuteScalar into a plain object. Then check for DBNull.Value, if
not *then* you can assign that object to the date variable.

I guess the VB syntax would be:
dim dat as datetime
dim res as object
sql = "select max(dat) from mytable where cat='x'
comd = New SqlCommand(sql, mConnection)
res = comd.ExecuteScalar

if res is not DBNull.Value then
dat = res
end if


Hans Kesting