conversion problem: dbnull to date

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
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.
 
Thanks

Paul Shapiro said:
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 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
 
Back
Top