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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
>
>

> 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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>> 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
>
>

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.

> 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