Error on ExecuteScalar() - NullReferenceException

[rnooraei]

Here is the code
Dim SqlMetroDateMin As String = "select top 1 Format(E13A101.TIME, 'yyyy/mm/dd')AS tmin from E13A101 where fmin=(select min(fmin) from E13A101 where month(TIME)= " & ddlMonth.SelectedValue & " and year(TIME)= " & ddlYear.SelectedValue & " ) and month(TIME)= " & ddlMonth.SelectedValue & " and year(TIME)= " & ddlYear.SelectedValue
Dim cmd As New OleDbCommand(SqlMetroDateMin, conn)
conn.Open()
lblDateMin.Text = cmd.ExecuteScalar().ToString()
conn.Close()

if there is no min date, I will get NullReferenceException error. I was wondering how can I check for the null before I run cmd.ExecuteScalar().ToString command.

Thanks

 

[jbenson001]

You'd have to run another query to do that.
First I suggest you use stored procedures
Second, you can use ISNULL and a case statement to avoid the error.

 

[jmeckley]

1. Always use parameterized queries. they are secure. they are easier to read. they are more dynamic (refactoring to reusable code)
2. don't format the date on the database, just get the value. formatting is for user presentation only.
3. use a better query.

select	top 1 TIME AS tmin  
from	E13A101 
where	month(TIME)= @mont and year(TIME)= @year
order by fmin desc

finally check for a dbnull value 1st. then set the value.

conn.Open()
object possibleDate = cmd.ExecuteScalar();
conn.Close()

if(!(possibleDate == null) && (possibleDate == DbNull.Value))
   lblDateMin.Text = possibleDate.ToString();

Jason Meckley
Programmer
Specialty Bakers, Inc.