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 dateCCJApprovedDate as DateTime

if cbx_ccjDateNone.Checked = True then
dateCCJApprovedDate = ctype("", DateTime)
else
dateCCJApprovedDate = ctype(ddl_CCJDateMonth.SelectedValue.tostring &
"/01/" & ddl_CCJDateYear.SelectedValue.tostring,
System.Data.SqlTypes.SqlDateTime)
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 dateCCJApprovedDate as System.Data.SqlTypes.SqlDateTime

if cbx_ccjDateNone.Checked = True then
dateCCJApprovedDate = System.Data.SqlTypes.SqlDateTime.null
else
dateCCJApprovedDate = ctype(ddl_CCJDateMonth.SelectedValue.tostring &
"/01/" & ddl_CCJDateYear.SelectedValue.tostring,
System.Data.SqlTypes.SqlDateTime)
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.Parameters.Add("@date", SqlDbType.DateTime)
if cbx_ccjDateNone.Checked = True then
command.Parameters("@date").Value = DbNull.Value
else
command.Parameters("@date").Value =
cdate(ddl_CCJDateMonth.SelectedValue.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]

command.Parameters.Add("@date", SqlDbType.DateTime)

if cbx_ccjDateNone.Checked = True then
command.Parameters("@date").Value = DbNull.Value
else
command.Parameters("@date").Value =
cdate(ddl_CCJDateMonth.SelectedValue.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("SELECT * FROM Blah WHERE x = @Date")
c.Parameters.Add("@Date", SqlDbType.DateTime).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!)

 

[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("SELECT * FROM Blah WHERE x = @Date")

c.Parameters.Add("@Date", SqlDbType.DateTime).Value = SomeValue

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

-Darrel