Problem with the SQLParameterCollection object

[Brad Pears]

I have the following code that adds SQLParameter objects to an
SQLParameterCol lection object (well at least that's what I am trying to
do!!)

When the line of code runs that adds the parameter (colParams.Add) , the
actual function call (CreateSQLParam ) executes with no errors but the actual
".add" fails with an "Object reference not set to an instance of an object"
error. At first I thought it was maybe because I did not use the "New"
keyword when dimming colParams but there is no constructor for an
SQLParameterCol lection object so that was not the problem. The funny thing
was, this code worked EXACTLY as it is listed below EXCEPT I was using a
standard collection object previously instead of the SQLParameterCol lection
object. As soon as I converted it to the SQLParamaterCol lection object, I
started getting this error. I can not see where I am going awry but it must
be something quite simple!!!

Here is the code...

Dim colParams As SqlClient.SqlPa rameterCollecti on

' Set up the parameters

colParams.Add(C reateSQLParam(" @vcContractNo", ContractNo, SqlDbType.VarCh ar,
ParameterDirect ion.Input))



Function CreateSQLParam( ByVal sName As String, ByVal 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

[tommaso.gastaldi@uniroma1.it]

Re: Problem with the SQLParameterCol lection object

On 13 Lug, 21:21, "Brad Pears" <br...@truenort hloghomes.comwr ote:

I have the following code that adds SQLParameter objects to an
SQLParameterCol lection object (well at least that's what I am trying to
do!!)
>
When the line of code runs that adds the parameter (colParams.Add) , the
actual function call (CreateSQLParam ) executes with no errors but the actual
".add" fails with an "Object reference not set to an instance of an object"
error. At first I thought it was maybe because I did not use the "New"
keyword when dimming colParams but there is no constructor for an
SQLParameterCol lection object so that was not the problem. The funny thing
was, this code worked EXACTLY as it is listed below EXCEPT I was using a
standard collection object previously instead of the SQLParameterCol lection
object. As soon as I converted it to the SQLParamaterCol lection object, I
started getting this error. I can not see where I am going awry but it must
be something quite simple!!!
>
Here is the code...
>
Dim colParams As SqlClient.SqlPa rameterCollecti on
>
' Set up the parameters
>
colParams.Add(C reateSQLParam(" @vcContractNo", ContractNo, SqlDbType.VarCh ar,
ParameterDirect ion.Input))
>
Function CreateSQLParam( ByVal sName As String, ByVal 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

Dim SqlDataAdapter As New SqlClient.SqlDa taAdapter 'Use yours

'Dim colParams As New SqlClient.SqlPa rameterCollecti on

' Set up the parameters

SqlDataAdapter. SelectCommand.P arameters.Add(C reateSQLParam(" @vcContractNo",
ContractNo, SqlDbType.VarCh ar, ParameterDirect ion.Input))


see:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.aspx

Tommaso

[Jack Jackson]

Re: Problem with the SQLParameterCol lection object

On Fri, 13 Jul 2007 15:21:17 -0400, "Brad Pears"
<bradp@truenort hloghomes.comwr ote:

>I have the following code that adds SQLParameter objects to an
>SQLParameterCo llection object (well at least that's what I am trying to
>do!!)
>
>When the line of code runs that adds the parameter (colParams.Add) , the
>actual function call (CreateSQLParam ) executes with no errors but the actual
>".add" fails with an "Object reference not set to an instance of an object"
>error. At first I thought it was maybe because I did not use the "New"
>keyword when dimming colParams but there is no constructor for an
>SQLParameterCo llection object so that was not the problem. The funny thing
>was, this code worked EXACTLY as it is listed below EXCEPT I was using a
>standard collection object previously instead of the SQLParameterCol lection
>object. As soon as I converted it to the SQLParamaterCol lection object, I
>started getting this error. I can not see where I am going awry but it must
>be something quite simple!!!
>
>Here is the code...
>
>Dim colParams As SqlClient.SqlPa rameterCollecti on
>
>' Set up the parameters
>
>colParams.Add( CreateSQLParam( "@vcContractNo" , ContractNo, SqlDbType.VarCh ar,
>ParameterDirec tion.Input))

You declared colParams but never set it to anything, so it has the
value Nothing. If you stepped through this with the debugger you
would see that.

Normally you would fix this by:

Dim colParams As SqlClient.SqlPa rameterCollecti on = New
SqlClient.SqlPa rameterCollecti on()

However, SqlParameterCol lection doesn't have an externally visible
constructor, so you can't create a stand-alone collection. Instead
create a SqlCommand and call the Add method of its Parameters property
passing the return value from CreateSqlParm:

Dim sqlCmd as SqlCommand = New SqlCommand()

sqlCmd.Paramete rs.Add(CreateSq lParm("@vcContr actNo", ...))