By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,417 Members | 1,088 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,417 IT Pros & Developers. It's quick & easy.

How to assign DBNull to a SQL field?

P: n/a
Hi,

I am trying to assign NULL to a datetime field in the SQL Server database.
Here is the code that does NOT work:

INSERT INTO ...
....
VALUES ...
....
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value, DBNull.Value), DateTime), & ...

It says "Cast from DBNull to Data is not valid"

If I relpace DBNull.Value with Nothing, then it seem to work but assigns MinDate instead of Null to the value.

Can you please give me some advise how to do this? Thank you.
Nov 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Omit the parameter completely:
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value), DateTime), &
....

This assumes the Database field has AllowNull= True.
--
Joe Fallon


"Dursun" <Dursun @discussions.microsoft.com> wrote in message
news:07**********************************@microsof t.com...
Hi,

I am trying to assign NULL to a datetime field in the SQL Server database.
Here is the code that does NOT work:

INSERT INTO ...
...
VALUES ...
...
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value, DBNull.Value), DateTime), & ...
It says "Cast from DBNull to Data is not valid"

If I relpace DBNull.Value with Nothing, then it seem to work but assigns MinDate instead of Null to the value.
Can you please give me some advise how to do this? Thank you.

Nov 20 '05 #2

P: n/a
> > CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value,
DBNull.Value),
DateTime), & ...
Why are you doing CType(<blah>, DateTime) ?

dateWintness2Date.Value is already of that type, no?

and you can't cast DBNull.Value to DateTime and you wouldn't even want to

Just use:
cmd.parameters.add("@myDate", sqldbtype.datetime).value =
IIf(dateWitness2Date.Checked, dateWitness2Date.Value, DBNull.Value)

Although I prefer to avoid the IIf function and do:

if dateintness2Date.Checked then
cmd.parameters.add("@myDate", sqldbtype.datetime).value =
dateWitness2Date.Value
else
cmd.parameters.add("@myDate", sqldbtype.datetime).value = DBNull.Value
end if

You are using parameters???

Greg
"Dursun" <Du****@discussions.microsoft.com> wrote in message
news:EE**********************************@microsof t.com...
Good idea but it reqires me to have an object for the FALSE part of the IIF statement. I even tried to put the "," and nothing else but that did not
work either. Any other ideas? Please...
"Joe Fallon" wrote:
Omit the parameter completely:
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value), DateTime),

& ....

This assumes the Database field has AllowNull= True.
--
Joe Fallon


"Dursun" <Dursun @discussions.microsoft.com> wrote in message
news:07**********************************@microsof t.com...
Hi,

I am trying to assign NULL to a datetime field in the SQL Server database. Here is the code that does NOT work:

INSERT INTO ...
...
VALUES ...
...
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value,
DBNull.Value), DateTime), & ...

It says "Cast from DBNull to Data is not valid"

If I relpace DBNull.Value with Nothing, then it seem to work but
assigns MinDate instead of Null to the value.

Can you please give me some advise how to do this? Thank you.


Nov 20 '05 #3

P: n/a
I use the If..Else method also, but generally employ stored procedures for
all updates and inserts. For optional values I specify a parameter default
in the stored procedure of null... so I only pass the parameters to the sp
if my input has text/is checked, etc:

If dateintness2Date.Checked Then
cmd.parameters.add("@myDate", sqldbtype.datetime).value =
dateWitness2Date.Value
End If


"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:uv**************@TK2MSFTNGP11.phx.gbl...
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value, DBNull.Value), DateTime), & ...
Why are you doing CType(<blah>, DateTime) ?

dateWintness2Date.Value is already of that type, no?

and you can't cast DBNull.Value to DateTime and you wouldn't even want to

Just use:
cmd.parameters.add("@myDate", sqldbtype.datetime).value =
IIf(dateWitness2Date.Checked, dateWitness2Date.Value, DBNull.Value)

Although I prefer to avoid the IIf function and do:

if dateintness2Date.Checked then
cmd.parameters.add("@myDate", sqldbtype.datetime).value =
dateWitness2Date.Value
else
cmd.parameters.add("@myDate", sqldbtype.datetime).value = DBNull.Value
end if

You are using parameters???

Greg
"Dursun" <Du****@discussions.microsoft.com> wrote in message
news:EE**********************************@microsof t.com...
Good idea but it reqires me to have an object for the FALSE part of the IIF statement. I even tried to put the "," and nothing else but that did

not work either. Any other ideas? Please...

"Joe Fallon" wrote:
Omit the parameter completely:
CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value),
DateTime), & ....

This assumes the Database field has AllowNull= True.
--
Joe Fallon


"Dursun" <Dursun @discussions.microsoft.com> wrote in message
news:07**********************************@microsof t.com...
> Hi,
>
> I am trying to assign NULL to a datetime field in the SQL Server database. > Here is the code that does NOT work:
>
> INSERT INTO ...
> ...
> VALUES ...
> ...
> CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value, DBNull.Value), DateTime), & ...
>
> It says "Cast from DBNull to Data is not valid"
>
> If I relpace DBNull.Value with Nothing, then it seem to work but assigns MinDate instead of Null to the value.
>
> Can you please give me some advise how to do this? Thank you.


Nov 20 '05 #4

P: n/a
Hi Dursun,

I would not use the IIF forever however for sure not in this case.

Testing to dbnull.value is testing to an object what means

If dr(0)(0) Is DbNull.value

I hope this helps?

Cor
Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.