Passing Null value to SQL

[amber]

Hello,
I'm not sure if this question should go in the SQL forum or this one...

I'm using VB.NET and SQL Server.
I'm using stored procedures to update and create new records in my database.
On the form I'm working on, I have 2 date fields.
I want the user to have the option of leaving these blank.
I do not want default values.
I'm getting error messages not matter what I try.
What syntax can I use to get VB to pass this null value instead of a date,
into SQL??

Thanks!
amber

[Jon Skeet [C# MVP]]

amber <am***@discussions.microsoft.com> wrote:

I'm not sure if this question should go in the SQL forum or this one...

I'm using VB.NET and SQL Server.
I'm using stored procedures to update and create new records in my database.
On the form I'm working on, I have 2 date fields.
I want the user to have the option of leaving these blank.
I do not want default values.
I'm getting error messages not matter what I try.
What syntax can I use to get VB to pass this null value instead of a date,
into SQL??

Use a parameter and set the value to DBNull.Value.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Sriram Krishnan]

Try using DBNull

--
Sriram Krishnan

http://www.dotnetjunkies.com/weblog/sriram
"amber" <am***@discussions.microsoft.com> wrote in message
news:12**********************************@microsof t.com...

Hello,
I'm not sure if this question should go in the SQL forum or this one...

I'm using VB.NET and SQL Server.
I'm using stored procedures to update and create new records in my
database.
On the form I'm working on, I have 2 date fields.
I want the user to have the option of leaving these blank.
I do not want default values.
I'm getting error messages not matter what I try.
What syntax can I use to get VB to pass this null value instead of a date,
into SQL??

Thanks!
amber

[amber]

If I use the code:
cLP.DateApproved = Convert.DBNull.value

I get the error:

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'Date' is not valid.

Amber

[Mike S.]

Amber,
I'm not sure how your object is configured but the problem may be your
cLP.DateApproved is a date type which you can't assign a type of
DBNull.Value. You'll have to assign the underlying db object (the column) the
value of DBNull.Value. If indeed cLP.DateApproved is a object of type date,
you can cLP.DateApproved = Date.MinValue which is still a date value, just a
real old one. Unfortunately, that won't put Null into your table. Also
remember that your column in the table must allow nulls.

Try posting your question in microsoft.public.dotnet.framework.adonet -
where the gurus of ado hang out. Someone could probably answer this one
correctly in 5 minutes.
"amber" wrote:

If I use the code:
cLP.DateApproved = Convert.DBNull.value

I get the error:

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'Date' is not valid.

Amber

[Mike S.]

Amber,
I'm not sure how your object is configured but the problem may be your
cLP.DateApproved is a date type which you can't assign a type of
DBNull.Value. You'll have to assign the underlying db object (the column) the
value of DBNull.Value. If indeed cLP.DateApproved is a object of type date,
you can cLP.DateApproved = Date.MinValue which is still a date value, just a
real old one. Unfortunately, that won't put Null into your table. Also
remember that your column in the table must allow nulls.

Try posting your question in microsoft.public.dotnet.framework.adonet -
where the gurus of ado hang out. Someone could probably answer this one
correctly in 5 minutes.
"amber" wrote:

If I use the code:
cLP.DateApproved = Convert.DBNull.value

I get the error:

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'Date' is not valid.

Amber

[Raj]

You cannot set a DBNull value to Datefield, Instead you are supposed to use
store procedure and through Parameter you have to set the DBNull to the
required filed.

Raj

"amber" wrote:

If I use the code:
cLP.DateApproved = Convert.DBNull.value

I get the error:

An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from type 'DBNull' to type 'Date' is not valid.

Amber