SQLServer IsNull() & DataReader

[Brent]

I'm trying to keep the logic of my web pages to a minimum. One of the
problems I face regularly is dealing with DBNull's. I know how to code my
way out of them, but I thought perhaps it'd be easier to take care of it at
the database level, rather than the asp.net level.

The SQL Server function IsNull() would seem to do the trick. But I'm having
problems. If, for example, I use this SQL statement ...

Dim strSQL as String = "SELECT IsNull(myDate,'1/1/1900') as
myDateCol from myTable;"

.... execute it properly (using a SQLDataReader) against the database, and
then refer to it in my <asp:repeater> as ...
<%# Container.DataItem("myDateCol")%>

.... I'll get the error that the index is out of range (or some such error),
indicating that the SQL statement isn't producing such a column. Remove the
IsNull() function, reference the column in the <asp:repeater> instead as
"myDate", and all works again.

Any ideas how to make this work?

Thanks for any help!

--Brent

[Cowboy \(Gregory A. Beamer\)]

It depends on what you are doing. For pure display, you can run some SQL
like the following:

SELECT CASE WHEN myDate IS NULL THEN ''
ELSE CAST(myDate AS VARCHAR(11)) END AS myDateCol -- Everything becomes
a character, not a date
FROM myTable

This is problematic if you are using UPDATES and INSERTS, however.

With Strongly Typed DataSets, you have a bit more control, although you
still have to worry about the test for NULL on output.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message
news:5m*****************@newssvr27.news.prodigy.co m...

I'm trying to keep the logic of my web pages to a minimum. One of the
problems I face regularly is dealing with DBNull's. I know how to code my
way out of them, but I thought perhaps it'd be easier to take care of it at the database level, rather than the asp.net level.

The SQL Server function IsNull() would seem to do the trick. But I'm having problems. If, for example, I use this SQL statement ...

Dim strSQL as String = "SELECT IsNull(myDate,'1/1/1900') as
myDateCol from myTable;"

... execute it properly (using a SQLDataReader) against the database, and
then refer to it in my <asp:repeater> as ...
<%# Container.DataItem("myDateCol")%>

... I'll get the error that the index is out of range (or some such error), indicating that the SQL statement isn't producing such a column. Remove the IsNull() function, reference the column in the <asp:repeater> instead as
"myDate", and all works again.

Any ideas how to make this work?

Thanks for any help!

--Brent