How to convert/insert NULL date value into MS SQL?

[darrel]

I have the following right now to enter a date into SQL getting the data
from some pull down menus:

-------------------------------------------------
dim dateCCJApproved Date as DateTime

if cbx_ccjDateNone .Checked = True then
dateCCJApproved Date = ctype("", DateTime)
else
dateCCJApproved Date = ctype(ddl_CCJDa teMonth.Selecte dValue.tostring &
"/01/" & ddl_CCJDateYear .SelectedValue. tostring,
System.Data.Sql Types.SqlDateTi me)
End If
-------------------------------------------------

That works if there is a date to enter. But fails if there isn't, as "" is a
string and can't be converted to a date/time.

So, I did a bit of googling, and came up with this:

-------------------------------------------------
dim dateCCJApproved Date as System.Data.Sql Types.SqlDateTi me

if cbx_ccjDateNone .Checked = True then
dateCCJApproved Date = System.Data.Sql Types.SqlDateTi me.null
else
dateCCJApproved Date = ctype(ddl_CCJDa teMonth.Selecte dValue.tostring &
"/01/" & ddl_CCJDateYear .SelectedValue. tostring,
System.Data.Sql Types.SqlDateTi me)
End If
-------------------------------------------------

But I have the opposite problem...I can use the null value, but I can't
convert the second set of data to SQLDateTime.

So, I seem to be trying to use/cast two different types of data to the same
field format in SQL and hence my problem. I'm guessing the second method is
a better approach, but it appears I need to do some sort of intermediate
cast/conversion. Am I on the right track with that line of thinking?

-Darrel

[Karl Seguin]

command.Paramet ers.Add("@date" , SqlDbType.DateT ime)
if cbx_ccjDateNone .Checked = True then
command.Paramet ers("@date").Va lue = DbNull.Value
else
command.Paramet ers("@date").Va lue =
cdate(ddl_CCJDa teMonth.Selecte dValue.tostring & "/01/" &
ddl_CCJDateYear .SelectedValue. tostring)
end if
hopefully nullable types in 2.0 will make this cleaner..

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"darrel" <no*****@hotmai l.com> wrote in message
news:e3******** *****@TK2MSFTNG P09.phx.gbl...

I have the following right now to enter a date into SQL getting the data
from some pull down menus:

-------------------------------------------------
dim dateCCJApproved Date as DateTime

if cbx_ccjDateNone .Checked = True then
dateCCJApproved Date = ctype("", DateTime)
else
dateCCJApproved Date = ctype(ddl_CCJDa teMonth.Selecte dValue.tostring &
"/01/" & ddl_CCJDateYear .SelectedValue. tostring,
System.Data.Sql Types.SqlDateTi me)
End If
-------------------------------------------------

That works if there is a date to enter. But fails if there isn't, as "" is
a
string and can't be converted to a date/time.

So, I did a bit of googling, and came up with this:

-------------------------------------------------
dim dateCCJApproved Date as System.Data.Sql Types.SqlDateTi me

if cbx_ccjDateNone .Checked = True then
dateCCJApproved Date = System.Data.Sql Types.SqlDateTi me.null
else
dateCCJApproved Date = ctype(ddl_CCJDa teMonth.Selecte dValue.tostring &
"/01/" & ddl_CCJDateYear .SelectedValue. tostring,
System.Data.Sql Types.SqlDateTi me)
End If
-------------------------------------------------

But I have the opposite problem...I can use the null value, but I can't
convert the second set of data to SQLDateTime.

So, I seem to be trying to use/cast two different types of data to the
same
field format in SQL and hence my problem. I'm guessing the second method
is
a better approach, but it appears I need to do some sort of intermediate
cast/conversion. Am I on the right track with that line of thinking?

-Darrel

[darrel]

> command.Paramet ers.Add("@date" , SqlDbType.DateT ime)

if cbx_ccjDateNone .Checked = True then
command.Paramet ers("@date").Va lue = DbNull.Value
else
command.Paramet ers("@date").Va lue =
cdate(ddl_CCJDa teMonth.Selecte dValue.tostring & "/01/" &
ddl_CCJDateYear .SelectedValue. tostring)
end if

So the solution is to use stored procedures?

-Darrel

[Karl Seguin]

Parameters can be used with inline sql as well...there's really no excuse
not to use them (and plenty of reasons to do it)

dim c as new SqlCommand("SEL ECT * FROM Blah WHERE x = @Date")
c.Parameters.Ad d("@Date", SqlDbType.DateT ime).Value = SomeValue

works perfectly.

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"darrel" <no*****@hotmai l.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

command.Paramet ers.Add("@date" , SqlDbType.DateT ime)
if cbx_ccjDateNone .Checked = True then
command.Paramet ers("@date").Va lue = DbNull.Value
else
command.Paramet ers("@date").Va lue =
cdate(ddl_CCJDa teMonth.Selecte dValue.tostring & "/01/" &
ddl_CCJDateYear .SelectedValue. tostring)
end if

So the solution is to use stored procedures?

-Darrel

[John.Net]

You could also use a regular expression validator and not even allow
the call to the sp to happen if the date is not a valid date.

[darrel]

> dim c as new SqlCommand("SEL ECT * FROM Blah WHERE x = @Date")

c.Parameters.Ad d("@Date", SqlDbType.DateT ime).Value = SomeValue

Thanks, Carl. I definitely need to start playing with paramaters.

-Darrel