3 
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:

Why does my repeater keep crashing on Eval(NULL) values?

Improve this question

6 Answers 6

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

COALESCE is actually ANSI SQL. It's not a vendor extension.
@derobert Thanks, I updated the answer to reflect your comment.
4

You can use CASE:

CASE WHEN MyField IS Null THEN ''
ELSE MyField
End As MyField
Improve this answer

Comments

2 
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

Improve this answer

Comments

1

You can use NVL.

NVL(<possible null value>,<value to return when arg1 is null>)

Improve this answer

Comments

0 
select max(isnull(Date,"default date")) .....
Improve this answer

Comments

0

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

Improve this answer

1 Comment

You can do it in single select as well: Select @LastAccessed = max(modifydate), @LastAccessed = isnull(@LastAccessed,'') from scormtrackings

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.