SqlParameter is already contained by another SqlParameterCollectio


M

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
 
Ad

Advertisements

M

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.
 
Ad

Advertisements

M

MCM

Brilliant. Thank you!


Miha Markic said:
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

MCM said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top