Problem with the SQLParameterCollection object

[Brad Pears]

I have the following code that adds SQLParameter objects to an
SQLParameterCollection 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
SQLParameterCollection 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 SQLParameterCollection
object. As soon as I converted it to the SQLParamaterCollection 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.SqlParameterCollection

' Set up the parameters

colParams.Add(CreateSQLParam("@vcContractNo", ContractNo, SqlDbType.VarChar,
ParameterDirection.Input))



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

 

[tommaso.gastaldi]

I have the following code that adds SQLParameter objects to an
SQLParameterCollection 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
SQLParameterCollection 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 SQLParameterCollection
object. As soon as I converted it to the SQLParamaterCollection 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.SqlParameterCollection

' Set up the parameters

colParams.Add(CreateSQLParam("@vcContractNo", ContractNo, SqlDbType.VarChar,
ParameterDirection.Input))

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

Dim SqlDataAdapter As New SqlClient.SqlDataAdapter 'Use yours

'Dim colParams As New SqlClient.SqlParameterCollection

' Set up the parameters

SqlDataAdapter.SelectCommand.Parameters.Add(CreateSQLParam("@vcContractNo",
ContractNo, SqlDbType.VarChar, ParameterDirection.Input))


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



Tommaso

 

[Jack Jackson]

I have the following code that adds SQLParameter objects to an
SQLParameterCollection 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
SQLParameterCollection 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 SQLParameterCollection
object. As soon as I converted it to the SQLParamaterCollection 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.SqlParameterCollection

' Set up the parameters

colParams.Add(CreateSQLParam("@vcContractNo", ContractNo, SqlDbType.VarChar,
ParameterDirection.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.SqlParameterCollection = New
SqlClient.SqlParameterCollection()

However, SqlParameterCollection 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.Parameters.Add(CreateSqlParm("@vcContractNo", ...))