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

 

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