converting/casting before submitting

[djc]

I have typically always used one of the VB CType functions (not CType itself
but CInt, CString, etc...) to cast/convert an input value to its proper type
before passing it as a parameter to a SQL stored procedure. I recently came
accross one I did not know how to handle though... TinyInt. I have an SQL
field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
and tried to submit the value as is to the stored procedure to see if there
would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting time
and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was
declared within it?

any info would be greatly appreciated. Thanks.

[Karl Seguin]

SqlParameter.Value is of type Object, which suggest that everything is taken
care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code
for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn
calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];
Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"djc" <no***@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

I have typically always used one of the VB CType functions (not CType itself but CInt, CString, etc...) to cast/convert an input value to its proper type before passing it as a parameter to a SQL stored procedure. I recently came accross one I did not know how to handle though... TinyInt. I have an SQL
field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
and tried to submit the value as is to the stored procedure to see if there would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting time and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was
declared within it?

any info would be greatly appreciated. Thanks.

[bruce barker]

the sql sp declares the datatype. when you call the client libary, you pass
an object and tell the client lib the actual datatype of your data. the
client lib will then convert the datatype to the matching sqldatatype, and
pass sql datatype to the sqlserver. the sqlserver than converts the passed
sql datatype to the sp dataype if it can. see the sql Convert function doc's
to see the supported conversions.

-- bruce (sqlwork.com)
"djc" <no***@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...
| I have typically always used one of the VB CType functions (not CType
itself
| but CInt, CString, etc...) to cast/convert an input value to its proper
type
| before passing it as a parameter to a SQL stored procedure. I recently
came
| accross one I did not know how to handle though... TinyInt. I have an SQL
| field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
| vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
| and tried to submit the value as is to the stored procedure to see if
there
| would be a problem. The value is a string (from a
| dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
| took the value fine. The SQL stored procedure was expecting a TinyInt as
| declared in the stored procedure. ??
|
| 1) Is there some type of auto-conversion in place? have I been wasting
time
| and/or slowing performance converting/casting myself all this time?
|
| 2) does the SQL stored procedure convert it itself since that is what was
| declared within it?
|
| any info would be greatly appreciated. Thanks.
|
|

[Karl Seguin]

My newsreader shows my reply to this as deleted...so I'll try again...sorry
for the possible double post:

SqlParameter.Value is of type Object, which suggest that everything is taken
care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code
for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn
calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];
Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.
--
MY ASP.Net tutorials
http://www.openmymind.net/
"djc" <no***@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

I have typically always used one of the VB CType functions (not CType itself but CInt, CString, etc...) to cast/convert an input value to its proper type before passing it as a parameter to a SQL stored procedure. I recently came accross one I did not know how to handle though... TinyInt. I have an SQL
field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
and tried to submit the value as is to the stored procedure to see if there would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting time and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was
declared within it?

any info would be greatly appreciated. Thanks.

[djc]

Thank you for the excellently explained answer. I appreciate it greatly.

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:OM**************@TK2MSFTNGP10.phx.gbl...

SqlParameter.Value is of type Object, which suggest that everything is taken care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];
Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"djc" <no***@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

I have typically always used one of the VB CType functions (not CType

itself

but CInt, CString, etc...) to cast/convert an input value to its proper

type

before passing it as a parameter to a SQL stored procedure. I recently

came

accross one I did not know how to handle though... TinyInt. I have an SQL field of type TinyInt which is an 8bit integer. I came accross a ToInt16() vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead and tried to submit the value as is to the stored procedure to see if

there

would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting

time

and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was declared within it?

any info would be greatly appreciated. Thanks.

[djc]

My newsreader shows both posts... but since your original is showing deleted
to you I am thanking you in both places to make sure you get the proper
thanks. Thanks!

Thank you for the excellently explained answer. I appreciate it greatly.

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:O%***************@TK2MSFTNGP11.phx.gbl...

My newsreader shows my reply to this as deleted...so I'll try again...sorry for the possible double post:

SqlParameter.Value is of type Object, which suggest that everything is taken care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];
Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.
--
MY ASP.Net tutorials
http://www.openmymind.net/
"djc" <no***@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

I have typically always used one of the VB CType functions (not CType

itself

but CInt, CString, etc...) to cast/convert an input value to its proper

type

before passing it as a parameter to a SQL stored procedure. I recently

came

accross one I did not know how to handle though... TinyInt. I have an SQL field of type TinyInt which is an 8bit integer. I came accross a ToInt16() vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead and tried to submit the value as is to the stored procedure to see if

there

would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting

time

and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was declared within it?

any info would be greatly appreciated. Thanks.

[djc]

great! thank you!

"bruce barker" <no***********@safeco.com> wrote in message
news:ub**************@TK2MSFTNGP09.phx.gbl...

the sql sp declares the datatype. when you call the client libary, you pass an object and tell the client lib the actual datatype of your data. the
client lib will then convert the datatype to the matching sqldatatype, and
pass sql datatype to the sqlserver. the sqlserver than converts the passed
sql datatype to the sp dataype if it can. see the sql Convert function doc's to see the supported conversions.

-- bruce (sqlwork.com)
"djc" <no***@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...
| I have typically always used one of the VB CType functions (not CType
itself
| but CInt, CString, etc...) to cast/convert an input value to its proper
type
| before passing it as a parameter to a SQL stored procedure. I recently
came
| accross one I did not know how to handle though... TinyInt. I have an SQL | field of type TinyInt which is an 8bit integer. I came accross a ToInt16() | vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead | and tried to submit the value as is to the stored procedure to see if
there
| would be a problem. The value is a string (from a
| dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
| took the value fine. The SQL stored procedure was expecting a TinyInt as
| declared in the stored procedure. ??
|
| 1) Is there some type of auto-conversion in place? have I been wasting
time
| and/or slowing performance converting/casting myself all this time?
|
| 2) does the SQL stored procedure convert it itself since that is what was | declared within it?
|
| any info would be greatly appreciated. Thanks.
|
|

[nedbalzer]

I know this is an old and dormant thread, but can anyone tell me where the equivalences between SQL data types (int, datetime, varchar, nvarchar, text, etc.) and the ASP.NET parameter types (int32, String, etc.) are documented?

TIA.

-- Ned