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]]

Re: Passing Null value to SQL

amber <amber@discussi ons.microsoft.c om> wrote:[color=blue]
> 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??[/color]

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

--
Jon Skeet - <skeet@pobox.co m>

http://www.pobox.com/~skeet

If replying to the group, please do not mail me too

[Sriram Krishnan]

Re: Passing Null value to SQL

Try using DBNull

--
Sriram Krishnan

http://www.dotnetjunkies.com/weblog/sriram

"amber" <amber@discussi ons.microsoft.c om> wrote in message
news:129004EF-3C97-4424-A70D-9A338BF7D543@mi crosoft.com...[color=blue]
> 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[/color]

[amber]

Re: Passing Null value to SQL

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

I get the error:

An unhandled exception of type 'System.Invalid CastException' occurred in
microsoft.visua lbasic.dll

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

Amber

[Mike S.]

Re: Passing Null value to SQL

Amber,
I'm not sure how your object is configured but the problem may be your
cLP.DateApprove d 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.DateApprove d is a object of type date,
you can cLP.DateApprove d = 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.publi c.dotnet.framew ork.adonet -
where the gurus of ado hang out. Someone could probably answer this one
correctly in 5 minutes.


"amber" wrote:
[color=blue]
> If I use the code:
> cLP.DateApprove d = Convert.DBNull. value
>
> I get the error:
>
> An unhandled exception of type 'System.Invalid CastException' occurred in
> microsoft.visua lbasic.dll
>
> Additional information: Cast from type 'DBNull' to type 'Date' is not valid.
>
> Amber[/color]

[Raj]

Re: Passing Null value to SQL

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:
[color=blue]
> If I use the code:
> cLP.DateApprove d = Convert.DBNull. value
>
> I get the error:
>
> An unhandled exception of type 'System.Invalid CastException' occurred in
> microsoft.visua lbasic.dll
>
> Additional information: Cast from type 'DBNull' to type 'Date' is not valid.
>
> Amber[/color]