Problems with data type conversions...

  • Thread starter Thread starter Brad Pears
  • Start date Start date
B

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(CreateSQLParam("@vcContractNo", ContractNo, SqlDbType.VarChar,
ParameterDirection.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.SqlDbType, ByVal varDir As ParameterDirection) As
SqlClient.SqlParameter

Dim objParam As SqlClient.SqlParameter
objParam = New SqlClient.SqlParameter()
objParam.ParameterName = sName

If IsNothing(sValue) Then sValue = System.DBNull.Value

objParam.Value = sValue
objParam.SqlDbType = varType
objParam.Direction = 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
 
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 said:
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...
Click to expand...

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

Andrew
 
Brad said:
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(CreateSQLParam("@vcContractNo", ContractNo, SqlDbType.VarChar,
ParameterDirection.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.SqlDbType, ByVal varDir As ParameterDirection) As
SqlClient.SqlParameter

Dim objParam As SqlClient.SqlParameter
objParam = New SqlClient.SqlParameter()
objParam.ParameterName = sName

If IsNothing(sValue) Then sValue = System.DBNull.Value

objParam.Value = sValue
objParam.SqlDbType = varType
objParam.Direction = 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
Click to expand...

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.
 
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
 
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
 
Brad Pears said:
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...
Click to expand...

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...
Click to expand...

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
 
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.SqlParamter(<any number of parameters !>) )
see this for the possible combinations you can use this link...
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sqlparameter.aspx

Also, in .Net 2.0 there's a SqlParameterCollection so that they don't need
to be stored in a generic collection.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.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 ContractNoObject as Object
dim ContractNo as String
dim colParms as collection

' Add a paramter to the collection
colParms.Add(CreateSQLParam("@vcContractNo", ContractNoObject ,
SqlDbType.VarChar, ParameterDirection.Input)

ContractNo = ContractNoObject.ToString()
 
Brad,

In addition to the as forever very good explanation from Armin.

If you want to pass an object, you have to pass an object, not creating an
object.
The sense is not much even if you could do it. You would always have to find
the type of the object at the receiving side and use that; at least with
casting or converting.

In other words, an object is not a subtitute for the old value type VAR.

Cor
 
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
 
Had to be

:-)

Cor


Cor Ligthert said:
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
Click to expand...
 
Cor said:
Goran,

I agree with you almost everything, however is it not that you are talking
about C#.
Click to expand...

No, obviously it's not, as there are no ByRef and ByVal keywords in C#.
I tried it and it does not work.
Click to expand...

Exactly what did not work?
 
Cor Ligthert said:
Brad,

In addition to the as forever very good explanation from Armin.
Click to expand...

I see it coming: One day, we will have a Skype call. :-) (..why not?)


Armin
 
Back
Top