Strongly Typed DataSet, Date Field and DBNull

Strongly Typed DataSet, Date Field and DBNull

Post by JimM.. » Wed, 09 Apr 2003 05:54:37



Hi folks,

I recall back in the beta days of VS.NET, we had to jump through hoops in a
situation where there's a DataSet wrapping a table that has a datetime field
that allows DBNull values.

I'm running into the same problem nowadays.  I've got a SQL Server table
with two datetime fields that allow Nulls.

As I'm populating the new row, I put Nothing in the row value (VB Date
type).  This ends up with "01/01/0001 12:00:00 AM" as the value.  From what
I can tell, the auto-generated code-behind for the strongly typed DataSet is
supposed to handle the conversion to DBNull when the record gets posted back
for update.

For some reason, this is no longer working.  Regardless of the date I use in
the codegen:nullValue attribute the DataSet code-behind still generates a
date of "01/01/01" which evaluates to year 2001...clearly not acceptable to
stand for a DBNull value.

How do I put a DBNull value into a SQL Server table using a strongly typed
DataSet?  It was a bug back then, but I can't seem to find how it works now.

--- Jim ---

 
 
 

Strongly Typed DataSet, Date Field and DBNull

Post by Kathleen Dollar » Thu, 10 Apr 2003 12:06:13


Jim,

Set<columnname>Null

Kathleen


Quote:> Hi folks,

> I recall back in the beta days of VS.NET, we had to jump through hoops in
a
> situation where there's a DataSet wrapping a table that has a datetime
field
> that allows DBNull values.

> I'm running into the same problem nowadays.  I've got a SQL Server table
> with two datetime fields that allow Nulls.

> As I'm populating the new row, I put Nothing in the row value (VB Date
> type).  This ends up with "01/01/0001 12:00:00 AM" as the value.  From
what
> I can tell, the auto-generated code-behind for the strongly typed DataSet
is
> supposed to handle the conversion to DBNull when the record gets posted
back
> for update.

> For some reason, this is no longer working.  Regardless of the date I use
in
> the codegen:nullValue attribute the DataSet code-behind still generates a
> date of "01/01/01" which evaluates to year 2001...clearly not acceptable
to
> stand for a DBNull value.

> How do I put a DBNull value into a SQL Server table using a strongly typed
> DataSet?  It was a bug back then, but I can't seem to find how it works
now.

> --- Jim ---


 
 
 

1. Strongly typed CUSTOM Dataset DBNull handling vs generated dataset

When I use a dataset generated from a .xsd file, and bind it to a DataGrid,
everything works fine.  Datagrid goes through the fields, and displays them,
and if it finds a DBNull it just displays a blank cell, even in cases where
you are talking about Integer, date, and other fields.  I know how to tell
the generated dataset to return String.Empty when it encounters a DBNull,
but that doesn't apply to strongly typed fields such as integer and
DateTime, etc.

I have a custom collection inheriting from the CollectionBase class, I can
bind it to the (web) DataGrid and it works fine until it gets a null value,
at which point it still calls my property accessors, which should return an
Int value, generating exception on a null value.  However, if I bind the
generated dataset to the Datagrid it works fine.  It doesn't appear that
when I bind the dataset directly that it even CALLS the datarow's accessors,
or calls the IsXYZNull() function to see if it is null.

Does anybody know at all what the DataRow/DataTable objects do that allow
the DataGrid to bind to it, and not call those accessors, thus allowing it
to always return String.Empty?  In doing research (and looking at mono.net
source code) it appears that since it inherits the IListSource Interface,
DataGrid calls GetList() which returns a DataView of itself.  Its not very
easy to track what exactly is happening though, since the VS.NET debugger
can't step through any of the datarow/datatable code, so I can watch what
its doing).

Originally what I hoped would happen is that the DataGrid would call a
generic accessor, perhaps the Item, with a string value for the column, and
inside there if I saw DBNull I could return String.Empty, and the DataGrid
could just display that.

I've read through as many messages as I could find through google groups
that are remotely related to this issue, and I have anticipated some of the
questions/responses I will get

1a) Use annotations to make the underlying dataset return String.Empty
1b) This is not feasible with non-string fields

2a) Using the annotations, return a default integer value, such as zero
2b) In my database there is a huge difference between 0 and Null, putting 0
in the display would tell the user something that is highly incorrect

3a) Return an exception
3b) This already occurs, and is what I wish to avoid

4a) Return null
4b) This would cause an exception when it tried to cast a null as an Int32

If there is anybody that can shed light on this area of DataBinding to the
System.Web.UI.WebControls.DataGrid object, I would be eternally grateful, as
I have been fighting this issue for 2 weeks.

Thanks!

Mark E. Scott Jr.

2. Efficient Speedstream 3060 win2k drivers desperately needed!

3. Strongly typed dataset - can't find strongly typed row type

4. Use multiple modems on same phone line?

5. How do you handle DBNull and strongly typed datasets?

6. How can i use codecs in Windows ?

7. Problem setting field to null in a strongly typed dataset.

8. Processor Design

9. Help req:Null fields in strongly typed datasets

10. Strongly typed DataTable with loosely typed DataSet

11. Experiences with DataReader \ DataSet \ Custom Strongly Type DataSet

12. Major Problem downcasting from DataSets to Strongly typed datasets across dlls

13. Problem Casting from Dataset to Strongly Typed Dataset