How to assign DBNull to a SQL field?

[Guest]

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.

 

[Joe Fallon]

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

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

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.

 

[Greg Burns]

CType(IIf(dateWitness2Date.Checked, dateWitness2Date.Value,
DBNull.Value),
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

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

 

[Matt Fischer]

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

 

[Cor Ligthert]

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