469,604 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,604 developers. It's quick & easy.

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

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

Nov 19 '05 #1
5 10404

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" <no*****@hotmail.com> wrote in message
news:e3*************@TK2MSFTNGP09.phx.gbl...
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

Nov 19 '05 #2
> 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
Nov 19 '05 #3
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!)
"darrel" <no*****@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

Nov 19 '05 #4
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.

Nov 19 '05 #5
> 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
Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Lauren Quantrell | last post: by
9 posts views Thread by cavassinif | last post: by
10 posts views Thread by satishrajana | last post: by
7 posts views Thread by elliotng.ee | last post: by
5 posts views Thread by =?Utf-8?B?bXBhaW5l?= | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.