How do I make this SQL statement return empty strings instead of NULLS?

Question

LastAccessed=(select max(modifydate) from scormtrackings WHERE 
bundleid=@bundleid and userid=u.userid),
CompletedLessons=(select Value from scormtrackings WHERE 
bundleid=@bundleid and userid=u.userid AND param='vegas2.progress'),
TotalLessons=100,
TotalNumAvail=100,
TotalNumCorrect=(SELECT Value FROM scormtrackings WHERE 
bundleid=@bundleid AND userid=u.userid AND param='cmi.score.raw')

This is only part of a large select statement used by my ASP.NET Repeater that keeps crashing when the values are NULL, I have tried ISNULL() but either it didn't work, or I did it wrong.

ISNULL((SELECT max(modifydate) FROM scormtrackings WHERE 
bundleid=@bundleid AND userid=u.userid),'') AS LastAccessed,

(...)

???

UPDATE: I've tried all these things with returning '', 0, 1, instead of the value that would be null and it still doesn't work, I wonder if the problem is with the Repeater?

Related Question:

http://stackoverflow.com/questions/584570/why-does-my-repeater-keep-crashing-on-evalnull-values

Answer 1

select max(isnull(Date,"default date")) .....

Answer 2

You can use CASE:

CASE WHEN MyField IS Null THEN ''
ELSE MyField
End As MyField

Answer 3

You can use the COALESCE function to avoid getting nulls. Basically it returns the first non-null value from the list.

SELECT COALESCE(dateField, '') FROM Some_Table

This is an ANSI standard, though I have noticed that it isn't available in Access SQL.

Answer 4

select max(isnull(modifydate, "default date") from scormtrackings where...

will work as long as there is at least one row that satisfies the where clause, otherwise you will still get NULL

select IsNull( max(modifydate), "default_date") from scormtrackings where ...

should work in all cases

Answer 5

This works too... hate to admit how often I use it!!

Declare @LastAccessed varchar(30)

Select @LastAccessed = max(modifydate) from scormtrackings

Set @LastAccessed = isnull(@LastAccessed,'')

Select @LastAccessed as LastAccessed