Why is it neccesary to include SqlDbType to the SqlParameter?

[Kenneth]

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.Small DateTime)
param.Value = textboxDOB

2. param = New SqlParameter("@ DOB", SqlDbType.DateT ime)
param.Value = textboxDOB

3. param = New SqlParameter()
param.Parameter Name = "@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?

[Greg Burns]

> 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 DeriveParameter s 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.Small DateTime 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.********@get 2net.dk> wrote in message
news:13******** *************** **@posting.goog le.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.Small DateTime)
param.Value = textboxDOB

2. param = New SqlParameter("@ DOB", SqlDbType.DateT ime)
param.Value = textboxDOB

3. param = New SqlParameter()
param.Parameter Name = "@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?

[Greg Burns]

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@DON T_SPAM_ME_hotma il.com> wrote in message
news:OW******** ******@TK2MSFTN GP12.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 DeriveParameter s 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.Small DateTime 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.********@get 2net.dk> wrote in message
news:13******** *************** **@posting.goog le.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.Small DateTime)
param.Value = textboxDOB

2. param = New SqlParameter("@ DOB", SqlDbType.DateT ime)
param.Value = textboxDOB

3. param = New SqlParameter()
param.Parameter Name = "@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?

[Patrice]

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@DON T_SPAM_ME_hotma il.com> a écrit dans le message
de news:Oj******** ******@TK2MSFTN GP09.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@DON T_SPAM_ME_hotma il.com> wrote in message
news:OW******** ******@TK2MSFTN GP12.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 DeriveParameter s 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.Small DateTime 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.********@get 2net.dk> wrote in message
news:13******** *************** **@posting.goog le.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.Small DateTime)
param.Value = textboxDOB

2. param = New SqlParameter("@ DOB", SqlDbType.DateT ime)
param.Value = textboxDOB

3. param = New SqlParameter()
param.Parameter Name = "@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?

[Greg Burns]

> 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.Sq lClient.SqlExce ption: 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=.;initia l
catalog=northwi nd;integrated security=SSPI;p ersist security
info=False;pack et size=4096;")

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

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

Try
cn.Open()
cmd.ExecuteNonQ uery()
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_InsertShipp er
@company_name nvarchar(40),
@phone nvarchar(25)

AS

SET NOCOUNT ON

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

Greg
"Patrice" <no****@nowhere .com> wrote in message
news:eX******** ******@TK2MSFTN GP09.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@DON T_SPAM_ME_hotma il.com> a écrit dans le message
de news:Oj******** ******@TK2MSFTN GP09.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@DON T_SPAM_ME_hotma il.com> wrote in message
news:OW******** ******@TK2MSFTN GP12.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 DeriveParameter s 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.Small DateTime 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.********@get 2net.dk> wrote in message
> news:13******** *************** **@posting.goog le.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.Small DateTime)
>> param.Value = textboxDOB
>>
>> 2. param = New SqlParameter("@ DOB", SqlDbType.DateT ime)
>> param.Value = textboxDOB
>>
>> 3. param = New SqlParameter()
>> param.Parameter Name = "@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?
>
>

[Patrice]

Thanks, interesting.

The length is used client side to truncate the string. If the length is not
known the string is sent entirely to the server that will eventuallty raise
the usual error when data doesn't fit the alloted space...

Patrice

--

"Greg Burns" <greg_burns@DON T_SPAM_ME_hotma il.com> a écrit dans le message
de news:OX******** ******@TK2MSFTN GP10.phx.gbl...

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.Sq lClient.SqlExce ption: 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=.;initia l
catalog=northwi nd;integrated security=SSPI;p ersist security
info=False;pack et size=4096;")

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

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

Try
cn.Open()
cmd.ExecuteNonQ uery()
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_InsertShipp er
@company_name nvarchar(40),
@phone nvarchar(25)

AS

SET NOCOUNT ON

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

Greg
"Patrice" <no****@nowhere .com> wrote in message
news:eX******** ******@TK2MSFTN GP09.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@DON T_SPAM_ME_hotma il.com> a écrit dans le message de news:Oj******** ******@TK2MSFTN GP09.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@DON T_SPAM_ME_hotma il.com> wrote in message
news:OW******** ******@TK2MSFTN GP12.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 DeriveParameter s 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.Small DateTime 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.********@get 2net.dk> wrote in message
> news:13******** *************** **@posting.goog le.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.Small DateTime)
>> param.Value = textboxDOB
>>
>> 2. param = New SqlParameter("@ DOB", SqlDbType.DateT ime)
>> param.Value = textboxDOB
>>
>> 3. param = New SqlParameter()
>> param.Parameter Name = "@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?
>
>