how to work with null data in vb.net 2005

[douglas]

I have the following two questions:

1. If a date field is passed to a vb.net 2005 application as null, what
code needed so that the vb.net application can handle the 'null' date and
work with it as a 'blank' field?
2. In a stored procedure that is called by the vb.net 2005 application, a
'blank date' is returned as '1/2/1900'. How would you pass the date as
'blank' instead of '1/2/1900' to the vb.net application? The following is the
stored procedure:
reate table testtable (tablekey as int,testdate as datetime)

insert into testtable values (1,getdate())

insert into testtable values(2,'')


The source for the first insert statement will contain a valid date.
The source for the second insert statement will not contain any date.
that is why I want the value in the second inswert statement to be blank.

Thus can you tell me how to pass a blank date value?

 

[Cor Ligthert[MVP]]

Douglas,

First of all try to avoid reading a value by a numeric indexer. Use the name
instead.
However, why not using a simple tableadapter, probably it is faster and for
sure more reliable.

A DateTime can never be spaces in SqlServer other then 2008 and in VB,
therefore it is maybe interesting as you tell us what you use as DataBase
Server because the combination VB 2004 and SqlServer 2008 is not so usable.

In all Net languages the type of a DBNull.Value = DBNull.Value. It
represents a non existing value.

Cor

 

[douglas]

Douglas,

First of all try to avoid reading a value by a numeric indexer. Use the name
instead.
However, why not using a simple tableadapter, probably it is faster and for
sure more reliable.

A DateTime can never be spaces in SqlServer other then 2008 and in VB,
therefore it is maybe interesting as you tell us what you use as DataBase
Server because the combination VB 2004 and SqlServer 2008 is not so usable.

In all Net languages the type of a DBNull.Value = DBNull.Value. It
represents a non existing value.

Cor

 

[douglas]

"Cor Ligthert[MVP]":

Thank you for your reply and I have the following questions/comments:

1. I am using a data adpater when I am passing the data from sql server
2005 to vb.net 2005.
2. In the date field, when I want the date value to be empty or blank,
sql server is responding with the date of 1/2/1900. I guess this is the
default date for sql server?
3. Thus I can put null in the column that I am passing to .net, but .net
does not like null values. Thus are you saying that if I check for dbnull and
the value is true, I can replace the invalid value with a space bu doing a
dbnull test?

Thanks!

 

[Jack Jackson]

Your SQL query is probably returning 1/2/1900 because that is what is
stored in your tables. The minimum value for a smalldatetime field is
1/1/1900. This value often ends up in SQL server when dates are
imported from a system that does not support NULL.

If you return NULL in a field, you will get a value of DBNULL.Value in
..NET.

 

[Cor Ligthert[MVP]]

Douglas

1. I am using a data adpater when I am passing the data from sql server
2005 to vb.net 2005.
2. In the date field, when I want the date value to be empty or blank,
sql server is responding with the date of 1/2/1900. I guess this is the
default date for sql server?

There is no default, in SQL Server 2005 the oldest date to be set is
1/1/1900 in a SmallDateTime (a little bit legacy) the standard DateTime can
have as oldest date 1/1/1753 the introduction year of the Georgian Calendar
in the Brittish Empier

3. Thus I can put null in the column that I am passing to .net, but .net
does not like null values. Thus are you saying that if I check for dbnull
and
the value is true, I can replace the invalid value with a space bu doing a
dbnull test?

You cannot set a datetime column to spaces or blank, you can set or leave it
null as is told to the server that a row can contain unused columns, which
has the name Null

And unused item can be checked by a non strongly dataset by DBnulll.Value or
as it is in a datareader by the method IsDBNull in a strongly typed dataset
there are also methods generated which incorporates the name IsNullValue

Cor