SqlParameter is already contained by another SqlParameterCollectio

[MCM]

I am getting the following error: "The SqlParameter is already contained by
another SqlParameterCollection."

In the code below, the error occurs during the 2nd iteration on the line
"cmdSQL.Parameters.AddRange(params)".

It seems to me that cmdSQL is not releasing the params even though I do call
Dispose on cmdSQL.


Code:

Public Sub GetStuff(ByRef stuff As StuffCollection)

Dim sp As New
SQLProvider(ConfigurationManager.ConnectionStrings("MainConnectionString").ConnectionString)

Dim params As New List(Of SqlParameter)

Dim paramStuffId As New SqlParameter("@stuffId", Data.SqlDbType.Int)
paramStuffId.Direction = Data.ParameterDirection.Input
params.Add(paramStuffId)
Dim paramThing As New SqlParameter("@thing", Data.SqlDbType.TinyInt)
paramThing.Direction = Data.ParameterDirection.Output
params.Add(paramThing)

For i As Integer = 0 To stuff.Count - 1
paramStuffId.Value = stuff(i).Id
sp.SQLExecuteNonQuery("GetStuff", params.ToArray)
If Not IsDBNull(paramThing.Value) Then
stuff(i).Thing = DirectCast(paramThing.Value, ThingType)
End If
Next

End Sub

Public Class SQLProvider

Private _ConnectionString As String

Public Sub New(ByVal connectionString As String)

_ConnectionString = connectionString

End Sub

Public Sub SQLExecuteNonQuery(ByVal functionName As String, ByRef params
As SqlParameter())

Dim conSQL As SqlConnection
Dim cmdSQL As SqlCommand

conSQL = Nothing
cmdSQL = Nothing

Try
conSQL = New SqlConnection(_ConnectionString)
cmdSQL = New SqlCommand(functionName, conSQL)
cmdSQL.CommandType = Data.CommandType.StoredProcedure
cmdSQL.Parameters.AddRange(params)
conSQL.Open()
cmdSQL.ExecuteNonQuery()
Finally
If Not conSQL Is Nothing Then
conSQL.Close()
conSQL.Dispose()
End If
If Not cmdSQL Is Nothing Then
cmdSQL.Dispose()
End If
End Try

End Sub

End Class

 

[Miha Markic]

Yes, dispose is almost certainly not releasing params as that's not what
dispose is meant to do.
Try removing params manually before you call dispose.

 

[MCM]

Brilliant. Thank you!

Yes, dispose is almost certainly not releasing params as that's not what
dispose is meant to do.
Try removing params manually before you call dispose.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: blog.rthand.com

I am getting the following error: "The SqlParameter is already contained
by
another SqlParameterCollection."

In the code below, the error occurs during the 2nd iteration on the line
"cmdSQL.Parameters.AddRange(params)".

It seems to me that cmdSQL is not releasing the params even though I do
call
Dispose on cmdSQL.


Code:

Public Sub GetStuff(ByRef stuff As StuffCollection)

Dim sp As New
SQLProvider(ConfigurationManager.ConnectionStrings("MainConnectionString").ConnectionString)

Dim params As New List(Of SqlParameter)

Dim paramStuffId As New SqlParameter("@stuffId", Data.SqlDbType.Int)
paramStuffId.Direction = Data.ParameterDirection.Input
params.Add(paramStuffId)
Dim paramThing As New SqlParameter("@thing", Data.SqlDbType.TinyInt)
paramThing.Direction = Data.ParameterDirection.Output
params.Add(paramThing)

For i As Integer = 0 To stuff.Count - 1
paramStuffId.Value = stuff(i).Id
sp.SQLExecuteNonQuery("GetStuff", params.ToArray)
If Not IsDBNull(paramThing.Value) Then
stuff(i).Thing = DirectCast(paramThing.Value, ThingType)
End If
Next

End Sub

Public Class SQLProvider

Private _ConnectionString As String

Public Sub New(ByVal connectionString As String)

_ConnectionString = connectionString

End Sub

Public Sub SQLExecuteNonQuery(ByVal functionName As String, ByRef
params
As SqlParameter())

Dim conSQL As SqlConnection
Dim cmdSQL As SqlCommand

conSQL = Nothing
cmdSQL = Nothing

Try
conSQL = New SqlConnection(_ConnectionString)
cmdSQL = New SqlCommand(functionName, conSQL)
cmdSQL.CommandType = Data.CommandType.StoredProcedure
cmdSQL.Parameters.AddRange(params)
conSQL.Open()
cmdSQL.ExecuteNonQuery()
Finally
If Not conSQL Is Nothing Then
conSQL.Close()
conSQL.Dispose()
End If
If Not cmdSQL Is Nothing Then
cmdSQL.Dispose()
End If
End Try

End Sub

End Class