Re: SmallDateTime

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@xxxxxxxxxxxxxxxxxx> wrote in
message news:%23PZ7B6dbIHA.4196@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxx> wrote in message
news:esOGGsYbIHA.5164@xxxxxxxxxxxxxxxxxxxxxxx
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!







.

Relevant Pages

  • Re: SmallDateTime
    ... Actually I am using Add and setting the small date time type:P and it is ... Here is your corrected string: ... The second example will strongly type for SQL Server and will definitely ... convert from datetime to smalldatetime... ...
    (microsoft.public.dotnet.framework.adonet)
  • SQL Server Job step failing.
    ... but fails as part of the Job in '2005 (on the SQL Server 2005 d/b). ... smalldatetime data type resulted in an out-of-range smalldatetime value. ... Any thoughts as to why the '2005 Job will not execute this step, ...
    (microsoft.public.sqlserver.programming)
  • Re: Data Driven Query - Lookup where Clause
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... > im trying to write a lookup query for a Data driven Query. ... > im trying to compare a string value from a text file to a smalldatetime value. ... > Ive tried converting the textfile value to '27/02/2004' but am getting nowhere ...
    (microsoft.public.sqlserver.dts)
  • Re: "Type Mismatch" calling GetExecutionErrorInfo Method from VBscript
    ... >I already feared that this and tried to 'initialize' the variables by ... >assigning them a value (of the proper type) on beforehand. ... out properly since they are still not the correct type underneath, ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: Cross Tab Query Help!
    ... Have you tried a SQL Server news group? ... This is my first query: ... FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum ... WHERE (DateCode BETWEEN CAST('20060713' AS smalldatetime) ...
    (microsoft.public.access.queries)
Quantcast