Cowboy
2/14/2008 9:59:00 PM
I have had that experience before.
Had a client that set unknown expiration dates to 9999. Fun stuff.
--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA
*************************************************
| Think outside the box!
|
*************************************************
"Smokey Grindel" <nospam@nospam.com> wrote in message
news:%235sVtMnbIHA.4140@TK2MSFTNGP04.phx.gbl...
> Should add I found the problem too turns out our client sends us data and
> one of the rows just happened to have a year of 9999.... of course that
> errors out on small date time... so my bug turned out to be bad data from
> them......
>
> "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in
> message news:%23PZ7B6dbIHA.4196@TK2MSFTNGP04.phx.gbl...
>> Yes, I know how.
>>
>> The problem is you are not using Add(), you are using AddWithValue(). The
>> signature here is:
>>
>> AddWithValue(name As String, value As Object)
>>
>> Underneath the hood, it creates a SqlParameter of the proper type, so you
>> do not have to set it. There are overloads you never see that create the
>> parameter of the correct SqlDbType for you based on what you send.
>>
>> Here is your corrected string:
>>
>> CmdAddRecord.Parameters.AddWithValue("@BillDate", _
>> now.Date)
>>
>> This one may actually narrow, as well, causing issue. If so, you will not
>> find it until runtime, as you have completely obfuscated the creation of
>> the parameter from your own eyes and left it up to Microsoft. The
>> correct, if so, is the following:
>>
>> Dim param As New SqlParameter("@BillDate2", _
>> SqlDbType.SmallDateTime)
>> param.Value = Now.Date
>> cmd.Parameters.Add(param)
>>
>> The second example will strongly type for SQL Server and will definitely
>> work. I have attached the entire code sample as a Test Project.
>>
>> --
>> Gregory A. Beamer
>> MVP, MCP: +I, SE, SD, DBA
>>
>> *************************************************
>> | Think outside the box! |
>> *************************************************
>> "Smokey Grindel" <nospam@nospam.com> wrote in message
>> news:esOGGsYbIHA.5164@TK2MSFTNGP03.phx.gbl...
>>>I need to insert a date into sql server via a sproc the table has a
>>> smalldatetime field... because we dont care about things before 1900 or
>>> after 2079.... or the time for that matter... but when I instert a date
>>> into
>>> the parameter like this
>>> CmdAddRecord.Parameters.AddWithValue("@BillDate",
>>> SqlDbType.SmallDateTime).Value = now.date
>>>
>>> it throws a narrowing error basically on the server... saying it cant
>>> convert from datetime to smalldatetime... how do we go about doing this?
>>> thanks!
>>>
>>>
>>
>>
>>
>
>