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 ContractNo as varchar
dim colParms as collection

' 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

[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()

"Brad Pears" <br***@truenort hloghomes.comwr ote in message
news:e%******** ********@TK2MSF TNGP06.phx.gbl. ..

>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 ContractNo as varchar
dim colParms as collection

' 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