Problems with data type conversions...

[Brad Pears]

I am using a function called "CreateSQLParam " which adds SQL parameters to a
collection.

The function is shown below... I add a parameter to a collection using the
following line code...

------------------------------------------------------------------------------------
dim vcContractNo as varchar
dim colParms as collection
vcContractNo = "07-00001"

' Add a paramter to the collection
colParms.Add(Cr eateSQLParam("@ vcContractNo", ContractNo, SqlDbType.VarCh ar,
ParameterDirect ion.Input)
------------------------------------------------------------------------------------
I am getting an error on the "ContractNo " field in the above line that says
"option strict on
disallows narrowing from type 'object' to type 'string' in copying the
value of ByRef parameter "sValue" back to the matching argument"

Here is the function...
-----------------------------------------------------------------------------------------
Function CreateSQLParam( ByVal sName As String, ByRef sValue As Object, ByVal
varType As System.Data.Sql DbType, ByVal varDir As ParameterDirect ion) As
SqlClient.SqlPa rameter

Dim objParam As SqlClient.SqlPa rameter
objParam = New SqlClient.SqlPa rameter()
objParam.Parame terName = sName

If IsNothing(sValu e) Then sValue = System.DBNull.V alue

objParam.Value = sValue
objParam.SqlDbT ype = varType
objParam.Direct ion = varDir
CreateSQLParam = objParam

End Function
-------------------------------------------------------------------------------------------

So in looking at the function, I am passing a varchar value (ContractNo) to
sValue which has been defined as an object and hence the error message.
Short of turning "option strict OFF", what is the best way to keep my
generic function so that I can pass whatever data type is required to the
functions sValue parameter? It must somehow mean I need to explicitely
define the type of variable coming in isntead of using object but how and
where would I do this?

Help!!

Thanks, Brad

[Cor Ligthert [MVP]]

Brad,

First as you get no answers, you can be sure nobody knows the answer, so
please don't repeat.

Two questions to you, why you are using by ref, I have the idea that you are
interpreting that wrong secondly why do you think that when you tell that
you want to pass an object that it will pass everything else too as by
instance values?

Cor
"Brad Pears" <br***@truenort hloghomes.comsc hreef in bericht
news:%2******** **********@TK2M SFTNGP03.phx.gb l...

>I am using a function called "CreateSQLParam " which adds SQL parameters to
a collection.

The function is shown below... I add a parameter to a collection using the
following line code...

------------------------------------------------------------------------------------
dim vcContractNo as varchar
dim colParms as collection
vcContractNo = "07-00001"

' Add a paramter to the collection
colParms.Add(Cr eateSQLParam("@ vcContractNo", ContractNo,
SqlDbType.VarCh ar, ParameterDirect ion.Input)
------------------------------------------------------------------------------------
I am getting an error on the "ContractNo " field in the above line that
says "option strict on
disallows narrowing from type 'object' to type 'string' in copying the
value of ByRef parameter "sValue" back to the matching argument"

Here is the function...
-----------------------------------------------------------------------------------------
Function CreateSQLParam( ByVal sName As String, ByRef sValue As Object,
ByVal varType As System.Data.Sql DbType, ByVal varDir As
ParameterDirect ion) As SqlClient.SqlPa rameter

Dim objParam As SqlClient.SqlPa rameter
objParam = New SqlClient.SqlPa rameter()
objParam.Parame terName = sName

If IsNothing(sValu e) Then sValue = System.DBNull.V alue

objParam.Value = sValue
objParam.SqlDbT ype = varType
objParam.Direct ion = varDir
CreateSQLParam = objParam

End Function
-------------------------------------------------------------------------------------------

So in looking at the function, I am passing a varchar value (ContractNo)
to sValue which has been defined as an object and hence the error message.
Short of turning "option strict OFF", what is the best way to keep my
generic function so that I can pass whatever data type is required to the
functions sValue parameter? It must somehow mean I need to explicitely
define the type of variable coming in isntead of using object but how and
where would I do this?

Help!!

Thanks, Brad

[Andrew Morton]

Brad Pears wrote:

I am using a function called "CreateSQLParam " which adds SQL
parameters to a collection.

The function is shown below... I add a parameter to a collection
using the following line code...

------------------------------------------------------------------------------------
dim vcContractNo as varchar
dim colParms as collection
vcContractNo = "07-00001"

But it's "String" in VB.NET, not "varchar".. .

Andrew

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Brad Pears wrote:

I am using a function called "CreateSQLParam " which adds SQL parameters to a
collection.

The function is shown below... I add a parameter to a collection using the
following line code...

------------------------------------------------------------------------------------
dim vcContractNo as varchar
dim colParms as collection
vcContractNo = "07-00001"

' Add a paramter to the collection
colParms.Add(Cr eateSQLParam("@ vcContractNo", ContractNo, SqlDbType.VarCh ar,
ParameterDirect ion.Input)
------------------------------------------------------------------------------------
I am getting an error on the "ContractNo " field in the above line that says
"option strict on
disallows narrowing from type 'object' to type 'string' in copying the
value of ByRef parameter "sValue" back to the matching argument"

Here is the function...
-----------------------------------------------------------------------------------------
Function CreateSQLParam( ByVal sName As String, ByRef sValue As Object, ByVal
varType As System.Data.Sql DbType, ByVal varDir As ParameterDirect ion) As
SqlClient.SqlPa rameter

Dim objParam As SqlClient.SqlPa rameter
objParam = New SqlClient.SqlPa rameter()
objParam.Parame terName = sName

If IsNothing(sValu e) Then sValue = System.DBNull.V alue

objParam.Value = sValue
objParam.SqlDbT ype = varType
objParam.Direct ion = varDir
CreateSQLParam = objParam

End Function
-------------------------------------------------------------------------------------------

So in looking at the function, I am passing a varchar value (ContractNo) to
sValue which has been defined as an object and hence the error message.
Short of turning "option strict OFF", what is the best way to keep my
generic function so that I can pass whatever data type is required to the
functions sValue parameter? It must somehow mean I need to explicitely
define the type of variable coming in isntead of using object but how and
where would I do this?

Help!!

Thanks, Brad

As you are passing the reference by reference, the data type of the
reference has to match exactly. However, if you don't plan to change the
reference from within the method, there is no reason to pass the
reference by reference. Just remove the ByRef keyword.

You can remove the ByVal keywords also, that is the default way that
parameters are passed. Stick with the default, unless you actually need
to pass a parameter by reference.

--
Göran Andersson
_____
http://www.guffa.com

[Brad Pears]

Sorry about that. I just wanted to rephrase part of my question so I deleted
the original, changed the content and reposted...

I am using by ref because "sValue" could be any type of datatype - and that
is why this function is using type "object"... The passed value could be of
decimal type, string type, boolean type, datetime type etc.. etc..

Otherwise I would have to check what the datatype of the object is before
and then I would have to call a fucntion that was customized for that
particular data type. You can see why I would not want to do that. I want
to use just this one function as a generic function to do the work
regardless of what type is passed in...

When you pass an object it does pass everything about that object - that is
the whole idea of being able to pass an object. For isntance, if you pass a
"collection " object, you are passing all the items in the collection as
well... So whatever function or procedure is then passed collection will be
able to use items in that collection...

Brad

"Cor Ligthert [MVP]" <no************ @planet.nlwrote in message
news:e4******** ******@TK2MSFTN GP04.phx.gbl...

Brad,

First as you get no answers, you can be sure nobody knows the answer, so
please don't repeat.

Two questions to you, why you are using by ref, I have the idea that you
are interpreting that wrong secondly why do you think that when you tell
that you want to pass an object that it will pass everything else too as
by instance values?

Cor
"Brad Pears" <br***@truenort hloghomes.comsc hreef in bericht
news:%2******** **********@TK2M SFTNGP03.phx.gb l...

>>I am using a function called "CreateSQLParam " which adds SQL parameters to
a collection.

The function is shown below... I add a parameter to a collection using
the following line code...

------------------------------------------------------------------------------------
dim vcContractNo as varchar
dim colParms as collection
vcContractNo = "07-00001"

' Add a paramter to the collection
colParms.Add(C reateSQLParam(" @vcContractNo", ContractNo,
SqlDbType.VarC har, ParameterDirect ion.Input)
------------------------------------------------------------------------------------
I am getting an error on the "ContractNo " field in the above line that
says "option strict on
disallows narrowing from type 'object' to type 'string' in copying the
value of ByRef parameter "sValue" back to the matching argument"

Here is the function...
-----------------------------------------------------------------------------------------
Function CreateSQLParam( ByVal sName As String, ByRef sValue As Object,
ByVal varType As System.Data.Sql DbType, ByVal varDir As
ParameterDirec tion) As SqlClient.SqlPa rameter

Dim objParam As SqlClient.SqlPa rameter
objParam = New SqlClient.SqlPa rameter()
objParam.Param eterName = sName

If IsNothing(sValu e) Then sValue = System.DBNull.V alue

objParam.Val ue = sValue
objParam.SqlDb Type = varType
objParam.Direc tion = varDir
CreateSQLPar am = objParam

End Function
-------------------------------------------------------------------------------------------

So in looking at the function, I am passing a varchar value (ContractNo)
to sValue which has been defined as an object and hence the error
message. Short of turning "option strict OFF", what is the best way to
keep my generic function so that I can pass whatever data type is
required to the functions sValue parameter? It must somehow mean I need
to explicitely define the type of variable coming in isntead of using
object but how and where would I do this?

Help!!

Thanks, Brad

[Brad Pears]

Sorry Andrew, that was a typo... I didn;t copy/paste this code - I just
entered it... Yes, it is defined as string in VB. I have been working with
SQL server so much that I accidentally tryped in varchar there instead...

Brad
"Andrew Morton" <ak*@in-press.co.uk.inv alidwrote in message
news:u9******** ******@TK2MSFTN GP03.phx.gbl...

Brad Pears wrote:

>I am using a function called "CreateSQLParam " which adds SQL
parameters to a collection.

The function is shown below... I add a parameter to a collection
using the following line code...

------------------------------------------------------------------------------------
dim vcContractNo as varchar
dim colParms as collection
vcContractNo = "07-00001"

But it's "String" in VB.NET, not "varchar".. .

Andrew

[Armin Zingler]

"Brad Pears" <br***@truenort hloghomes.comsc hrieb

Sorry about that. I just wanted to rephrase part of my question so I
deleted the original, changed the content and reposted...

I am using by ref because "sValue" could be any type of datatype -
and that is why this function is using type "object"... The passed
value could be of decimal type, string type, boolean type, datetime
type etc.. etc..

Otherwise I would have to check what the datatype of the object is
before and then I would have to call a fucntion that was customized
for that particular data type. You can see why I would not want to
do that. I want to use just this one function as a generic function
to do the work regardless of what type is passed in...

You don't have to use ByRef to be able to declare it As Object.

Use ByRef /only/ if you want to pass something back to the calling
procedure.

When you pass an object it does pass everything about that object -
that is the whole idea of being able to pass an object. For
isntance, if you pass a "collection " object, you are passing all the
items in the collection as well... So whatever function or procedure
is then passed collection will be able to use items in that
collection...

We have to distinguish between reference types and value types:

If you pass a value type ByVal, a complete copy of the object, that means a
copy of all the fields in the object is put on the stack.

If you pass a reference type ByVal, a copy of the reference to the object is
put on the stack. The object is not copied. In the called procedure you have
a reference to the same object as the caller has.

If you pass a value type ByRef, a reference to the object is passed.

If you pass a reference type ByRef, a reference to the passed variable
(holding the reference to the object) is passed.
In addition, there are boxed value types: If you assign a value type to an
"As Object" variable, the variable will contain a reference to the boxed
object (which is still a value type). Boxing is done implicitly.
Armin

[Daniel Bass]

Brad,

Firstly, I'm not sure why you're trying to do this since the contstructor
for SqlParameter is overloaded, allowing you to do something like this:
colParms.Add ( New SqlClient.SqlPa ramter(<any number of parameters !>) )
see this for the possible combinations you can use this link...
http://msdn2.microsoft.com/en-us/lib...parameter.aspx

Also, in .Net 2.0 there's a SqlParameterCol lection so that they don't need
to be stored in a generic collection.
http://msdn2.microsoft.com/en-us/lib...ction.add.aspx

Secondly, you're parameter in your function is not only an Object, but it's
also a reference type, (ByRef). This means that it's going to try to
implicitly copy the details from an Object to a String, but the compiler
doesn't know whether this is possible.
Either change the function definition so that the parameter so that it's
"ByVal sValue As Object" (not ByRef), or change do the following:

dim ContractNoObjec t as Object
dim ContractNo as String
dim colParms as collection

' Add a paramter to the collection
colParms.Add(Cr eateSQLParam("@ vcContractNo", ContractNoObjec t ,
SqlDbType.VarCh ar, ParameterDirect ion.Input)

ContractNo = ContractNoObjec t.ToString()

[Cor Ligthert [MVP]]

Goran,

I agree with you almost everything, however is it not that you are talking
about C#. I tried it and it does not work. Although my IDE makes from it
nicely the correct VB code even in VBNet 2003.

(-:

Cor

>
You can remove the ByVal keywords also, that is the default way that
parameters are passed. Stick with the default, unless you actually need to
pass a parameter by reference.

--

[Cor Ligthert [MVP]]

Had to be

:-)

Cor
"Cor Ligthert [MVP]" <no************ @planet.nlschre ef in bericht
news:Ow******** ******@TK2MSFTN GP04.phx.gbl...

Goran,

I agree with you almost everything, however is it not that you are talking
about C#. I tried it and it does not work. Although my IDE makes from it
nicely the correct VB code even in VBNet 2003.

(-:

Cor

>>
You can remove the ByVal keywords also, that is the default way that
parameters are passed. Stick with the default, unless you actually need
to pass a parameter by reference.

--