Problems with data type conversions...
Brad Pears
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
Cor Ligthert [MVP]
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...@truenorthloghomes.com> schreef in bericht
news:%23cfY%239ywH...@TK2MSFTNGP03.phx.gbl...
Andrew Morton
> 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
Göran Andersson
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
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]" <notmyfi...@planet.nl> wrote in message
news:e4kugT3w...@TK2MSFTNGP04.phx.gbl...
Brad Pears
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" <a...@in-press.co.uk.invalid> wrote in message
news:u95iV14w...@TK2MSFTNGP03.phx.gbl...
Armin Zingler
> 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
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()
Cor Ligthert [MVP]
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
"Brad Pears" <br...@truenorthloghomes.com> schreef in bericht
news:es43bq8w...@TK2MSFTNGP04.phx.gbl...
Cor Ligthert [MVP]
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
Cor Ligthert [MVP]
:-)
Cor
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> schreef in bericht
news:Ow7NZjQx...@TK2MSFTNGP04.phx.gbl...
Göran Andersson
> Goran,
>
> I agree with you almost everything, however is it not that you are talking
> about C#.
No, obviously it's not, as there are no ByRef and ByVal keywords in C#.
> I tried it and it does not work.
Exactly what did 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.
>>
>> --
>
>
Armin Zingler
> Brad,
>
> In addition to the as forever very good explanation from Armin.
I see it coming: One day, we will have a Skype call. :-) (..why not?)
Armin