Re: Why is it neccesary to include SqlDbType to the SqlParameter?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Greg Burns (greg_burns_at_DONT_SPAM_ME_hotmail.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 10:39:06 -0500

> Try to not specify the size and insert a long string in the DB. Is it
> truncated ?

No.

I you try and insert a string longer than the field length without
specifying the field length in the parameter it throws an exception!
"System.Data.SqlClient.SqlException: String or binary data would be
truncated."

It does NOT throw the exception when you do specify the field length in the
parameter, it simpy truncates.

Here is the code I tested with:

        Dim cn As New SqlConnection("data source=.;initial
catalog=northwind;integrated security=SSPI;persist security
info=False;packet size=4096;")

        Dim cmd As New SqlCommand("INSERT shippers (CompanyName, Phone)
VALUES (@company_name, @phone)", cn)

        cmd.Parameters.Add("@company_name", SqlDbType.NVarChar).Value = New
String("X"c, 41) ' allows 40
        cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = New
String("Z"c, 25) ' allows 24

        Try
            cn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
        Finally
            If Not cn Is Nothing AndAlso cn.State = ConnectionState.Open
Then cn.Close()
        End Try

Out of curiosity I tried with a sproc also with the same results:

CREATE PROCEDURE usp_InsertShipper
 @company_name nvarchar(40),
 @phone nvarchar(25)

AS

SET NOCOUNT ON

INSERT shippers (CompanyName, Phone)
VALUES (@company_name, @phone)

Greg

"Patrice" <nobody@nowhere.com> wrote in message
news:eXA5lQC9EHA.3416@TK2MSFTNGP09.phx.gbl...
> Try to not specify the size and insert a long string in the DB. Is it
> truncated ?
>
> AFAIK it is valid to not specify a size for varchar when using
> Transact-SQL.
> In this case the default length is 30. It makes me think that the .NET
> provider could do something similar...
>
> Thanks for letting us know what you find...
>
> Patrice
>
> --
>
> "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> a écrit dans le message
> de news:OjgrJs28EHA.3012@TK2MSFTNGP09.phx.gbl...
>> Replying to myself. :)
>>
>> > One of the overloads for creating a parameter is just the name and
>> > type,
>> > ommitting the size. I am not aware of any negative side affects of
>> > this
>> > for varchar (etc.) parameters. Anybody know?
>>
>> Just saw this in documentation:
>>
>> "The Size is inferred from the value of the dbType parameter if it is not
>> explicitly set in the size parameter."
>>
>> So what does this mean for a varchar?
>>
>> Greg
>>
>>
>> "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
>> news:OWcTgo28EHA.3908@TK2MSFTNGP12.phx.gbl...
>> >> It also seem annoying that every time I make a change in the database
>> >> (eg varchar(50) to varchar(100)), I have to check all my
>> >> Sqlparameters. Is there a way to read these definitions from the
>> >> stored procedure into the Sqlparameters?
>> >
>> > CommandBuilder has a DeriveParameters method (akin to the old ADO
> refresh
>> > method), but it is not recommended and will create an extra round trip
> to
>> > the database.
>> >
>> > One of the overloads for creating a parameter is just the name and
>> > type,
>> > ommitting the size. I am not aware of any negative side affects of
>> > this
>> > for varchar (etc.) parameters. Anybody know?
>> >
>> > As for me, I have the same problem. I've been including the size, and
>> > then when it changes in the sproc, I gotta go change it in the code
>> > too.
>> > ;(
>> >
>> > As for your example; if you pass your DOB variable using the
>> > SqlDbType.SmallDateTime you're not going to see a difference unless the
>> > precision of your DOB variable is to the second! Check out the SQL
>> > datetime and smalldatetime datatypes in BOL for more info.
>> >
>> > Greg
>> >
>> > "Kenneth" <k.agerskov@get2net.dk> wrote in message
>> > news:13664bff.0501050853.ff03210@posting.google.com...
>> >> Can anyone explain me why it is neccesary to include SqlDbType to the
>> >> SqlParameter. In every example I see, it is done, but no one explaines
>> >> why.
>> >>
>> >> I have for example a date I want to save into my Sql Server database
>> >> through a stored procedure-call. In the database it is defined as a
>> >> SmallDateTime. Every 3 methods in the client-code below gives the same
>> >> (and correct) result. So why is it that important?
>> >>
>> >> dim param As SqlParameter
>> >>
>> >> 1. param = New SqlParameter("@DOB", SqlDbType.SmallDateTime)
>> >> param.Value = textboxDOB
>> >>
>> >> 2. param = New SqlParameter("@DOB", SqlDbType.DateTime)
>> >> param.Value = textboxDOB
>> >>
>> >> 3. param = New SqlParameter()
>> >> param.ParameterName = "@DOB"
>> >> param.Value = textboxDOB
>> >>
>> >> It also seem annoying that every time I make a change in the database
>> >> (eg varchar(50) to varchar(100)), I have to check all my
>> >> Sqlparameters. Is there a way to read these definitions from the
>> >> stored procedure into the Sqlparameters?
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: sed issue
    ... the log file, it truncates the file after the first line, and I have been ... unable to get it to read past the last character of the first line. ... This seems to be the only instance of the problematic string, but it is in every one of the expect log output files. ... You can also try to specify ...
    (comp.unix.shell)
  • Re: store chinese again
    ... > i cannot store unicode in thie way, ... You've specified that it's a VarChar, which means you should specify a ... string, not a byte array. ...
    (microsoft.public.dotnet.general)
  • Re: Web DAV only works when authenticated to OWA
    ... > Dim objRequest 'As MSXML.XMLHTTPRequest ... > Dim strXMLNSInfo 'As String ... > ' Specify the URL of the new object to be created. ... > ' Put it all together in an HTTP request. ...
    (microsoft.public.exchange.development)
  • Re: Grabbing string values from the end of a string of an unknown
    ... having to specify the parameter values/never run macros unless excel has just ... Not if the string is produced by a formula. ... shown below in this recorded macro are restored. ... The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each ...
    (microsoft.public.excel.programming)
  • Web DAV only works when authenticated to OWA
    ... Dim objRequest 'As MSXML.XMLHTTPRequest ... Dim strXMLNSInfo 'As String ... ' Specify the URL of the new object to be created. ... ' Specify the Business Address Information. ...
    (microsoft.public.exchange.development)