The SqlParameter with ParameterName 'xxxxx' is already contained by this SqlParameterCollection.

S

Stephan

Hi,

I'm experiencing the problem mentioned in the title above. Briefly, I
build a page in which I include a class. This class contains a
procedure to add a value to an ArrayList. The value is hold by a
structure. So all elements in the arraylist are in fact a structure.
As you can see in the code below. Now what I'm trying to do is for
each value in the Arraylist create and add a parameter. Then I try to
execute a SP. This only works if I use one parameter. If I use
multiple parameters I get the above array. Does anyone see the
problem?

I'm using APS.NET Framework 1.1, VS.NET, SQL 2000

First I put the code of the page and after that the code of the class


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Dim objDataAccess As New
ClassLibrary1.SymaxDataAccess.clsSqlDataAccess()
Dim dummy As String
Dim dbRecordSet As SqlClient.SqlDataReader

objDataAccess.strStoredProcedure = "Select_Test"
objDataAccess.AddParameter("@Error", "", SqlDbType.Int,
ParameterDirection.Output, 4)
objDataAccess.AddParameter("@rowcount", "", SqlDbType.Int,
ParameterDirection.Output, 4)
objDataAccess.AddParameter("@FLD_Parameter", "", SqlDbType.Int,
ParameterDirection.Input, 4)
dbRecordSet = objDataAccess.DbRecordSet()


While (dbRecordSet.Read())
dummy = dummy + "<br>" + CStr(dbRecordSet(0))

End While

objDataAccess = Nothing

If Not (dbRecordSet.IsClosed) Then
dbRecordSet.Close()
End If


myLabel.Text = dummy


End Sub

Codebehind bestand Eind


Gebruikte class:


Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections

Namespace SymaxDataAccess

'*******************************************************
'
' clsSqlDataAccess Class
'
' Business/Data Logic Class that encapsulates all data
' logic necessary to add/login/query customers within
' the [] database.
'
' TO DO: Error handeling
'*******************************************************
Public Class clsSqlDataAccess

'*******************************************************
' Private var's
'*******************************************************
Private _strSP As String = ""
Private _objParamArray As New ArrayList()

Private Shared _strConnectString As String =
ConfigurationSettings.AppSettings("ConnectionString")

Private Structure _ParamList
Dim ParamName As String
Dim ParamValue As Object
Dim ParamType As String
Dim ParamIO As String
Dim ParamSize As Integer
End Structure


Sub New()
MyBase.New()
_strSP = ""
End Sub


#Region "clsDataAccess Properties"
'*******************************************************
' clsDataAccess Properties
'*******************************************************
Public WriteOnly Property strStoredProcedure() As String
Set(ByVal Value As String)
_strSP = Value
End Set
End Property

Public ReadOnly Property DbRecordSet() As SqlDataReader
Get

' Create Instance of Connection and Command Object
Dim objConnection As New SqlConnection()
Dim objCommand As New SqlCommand()
Dim strArrayValue As _ParamList
Dim objParamValue As New SqlParameter()


With objConnection
..ConnectionString = _strConnectString
..Open()
End With

With objCommand
..CommandTimeout = 15
..CommandType = CommandType.StoredProcedure
..Connection = objConnection
..CommandText = _strSP
End With

For Each strArrayValue In _objParamArray
objCommand.CreateParameter()
objParamValue.ParameterName = strArrayValue.ParamName
objParamValue.Size = strArrayValue.ParamSize
objParamValue.Direction = strArrayValue.ParamIO
objParamValue.Precision = strArrayValue.ParamType
objCommand.Parameters.Add(objParamValue).Value =
strArrayValue.ParamValue
Next


Try

Return objCommand.ExecuteReader

Catch sqlException As Exception

objCommand.Parameters.Clear()
objCommand.Dispose()

Finally

If (objConnection.State <> ConnectionState.Closed) Then
objConnection.Close()
End If

End Try
End Get
End Property


#End Region

#Region "clsDataAccess Methods"

'*******************************************************
' clsDataAccess Methods
'*******************************************************

Public Sub AddParameter(ByVal ParamName, ByVal ParamValue, ByVal
ParamType, ByVal ParamIO, ByVal ParamSize)

Dim objParamList As _ParamList

objParamList.ParamName = ParamName
objParamList.ParamValue = ParamValue
objParamList.ParamType = ParamType
objParamList.ParamIO = ParamIO
objParamList.ParamSize = ParamSize

_objParamArray.Add(objParamList)

objParamList = Nothing

End Sub

#End Region

End Class

End Namespace

Error:

System.ArgumentException: The SqlParameter with ParameterName 'xxxxx'
is already contained by this SqlParameterCollection

Thx for the help
 
J

Jason Coyne Gaijin42

Does your stored procedure accept a variable number of parameters? I
have never heard of that type of functonality in SQL server or Oracle.
Most likely you will either have to call your SP multiple times, or
the SP wants an array values.

If your SP does accept a variable number of parameters, you need to
give each parameter a different name. you could append a number to
each parameter so it is incremented each time. However most DBs want
the SP parameter names to match the name of the param as defined in
the SP

Hi,

I'm experiencing the problem mentioned in the title above. Briefly, I
build a page in which I include a class. This class contains a
procedure to add a value to an ArrayList. The value is hold by a
structure. So all elements in the arraylist are in fact a structure.
As you can see in the code below. Now what I'm trying to do is for
each value in the Arraylist create and add a parameter. Then I try to
execute a SP. This only works if I use one parameter. If I use
multiple parameters I get the above array. Does anyone see the
problem?

I'm using APS.NET Framework 1.1, VS.NET, SQL 2000

First I put the code of the page and after that the code of the class


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Dim objDataAccess As New
ClassLibrary1.SymaxDataAccess.clsSqlDataAccess()
Dim dummy As String
Dim dbRecordSet As SqlClient.SqlDataReader

objDataAccess.strStoredProcedure = "Select_Test"
objDataAccess.AddParameter("@Error", "", SqlDbType.Int,
ParameterDirection.Output, 4)
objDataAccess.AddParameter("@rowcount", "", SqlDbType.Int,
ParameterDirection.Output, 4)
objDataAccess.AddParameter("@FLD_Parameter", "", SqlDbType.Int,
ParameterDirection.Input, 4)
dbRecordSet = objDataAccess.DbRecordSet()


While (dbRecordSet.Read())
dummy = dummy + "<br>" + CStr(dbRecordSet(0))

End While

objDataAccess = Nothing

If Not (dbRecordSet.IsClosed) Then
dbRecordSet.Close()
End If


myLabel.Text = dummy


End Sub

Codebehind bestand Eind


Gebruikte class:


Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections

Namespace SymaxDataAccess

'*******************************************************
'
' clsSqlDataAccess Class
'
' Business/Data Logic Class that encapsulates all data
' logic necessary to add/login/query customers within
' the [] database.
'
' TO DO: Error handeling
'*******************************************************
Public Class clsSqlDataAccess

'*******************************************************
' Private var's
'*******************************************************
Private _strSP As String = ""
Private _objParamArray As New ArrayList()

Private Shared _strConnectString As String =
ConfigurationSettings.AppSettings("ConnectionString")

Private Structure _ParamList
Dim ParamName As String
Dim ParamValue As Object
Dim ParamType As String
Dim ParamIO As String
Dim ParamSize As Integer
End Structure


Sub New()
MyBase.New()
_strSP = ""
End Sub


#Region "clsDataAccess Properties"
'*******************************************************
' clsDataAccess Properties
'*******************************************************
Public WriteOnly Property strStoredProcedure() As String
Set(ByVal Value As String)
_strSP = Value
End Set
End Property

Public ReadOnly Property DbRecordSet() As SqlDataReader
Get

' Create Instance of Connection and Command Object
Dim objConnection As New SqlConnection()
Dim objCommand As New SqlCommand()
Dim strArrayValue As _ParamList
Dim objParamValue As New SqlParameter()


With objConnection
.ConnectionString = _strConnectString
.Open()
End With

With objCommand
.CommandTimeout = 15
.CommandType = CommandType.StoredProcedure
.Connection = objConnection
.CommandText = _strSP
End With

For Each strArrayValue In _objParamArray
objCommand.CreateParameter()
objParamValue.ParameterName = strArrayValue.ParamName
objParamValue.Size = strArrayValue.ParamSize
objParamValue.Direction = strArrayValue.ParamIO
objParamValue.Precision = strArrayValue.ParamType
objCommand.Parameters.Add(objParamValue).Value =
strArrayValue.ParamValue
Next


Try

Return objCommand.ExecuteReader

Catch sqlException As Exception

objCommand.Parameters.Clear()
objCommand.Dispose()

Finally

If (objConnection.State <> ConnectionState.Closed) Then
objConnection.Close()
End If

End Try
End Get
End Property


#End Region

#Region "clsDataAccess Methods"

'*******************************************************
' clsDataAccess Methods
'*******************************************************

Public Sub AddParameter(ByVal ParamName, ByVal ParamValue, ByVal
ParamType, ByVal ParamIO, ByVal ParamSize)

Dim objParamList As _ParamList

objParamList.ParamName = ParamName
objParamList.ParamValue = ParamValue
objParamList.ParamType = ParamType
objParamList.ParamIO = ParamIO
objParamList.ParamSize = ParamSize

_objParamArray.Add(objParamList)

objParamList = Nothing

End Sub

#End Region

End Class

End Namespace

Error:

System.ArgumentException: The SqlParameter with ParameterName 'xxxxx'
is already contained by this SqlParameterCollection

Thx for the help
Click to expand...
 
S

Stephan

In the meantime I solved the problem by replacing


For Each strArrayValue In _objParamArray
objCommand.CreateParameter()
objParamValue.ParameterName = strArrayValue.ParamName
objParamValue.Size = strArrayValue.ParamSize
objParamValue.Direction = strArrayValue.ParamIO
objParamValue.Precision = strArrayValue.ParamType
objCommand.Parameters.Add(objParamValue).Value =
strArrayValue.ParamValue
Next

With

For Each strArrayValue In _objParamArray

Dim objParamValue As New System.Data.SqlClient.SqlParameter()
objParamValue.ParameterName = strArrayValue.ParamName
objParamValue.Size = strArrayValue.ParamSize
objParamValue.Direction = strArrayValue.ParamIO
objParamValue.Precision = strArrayValue.ParamType
objCommand.Parameters.Add(objParamValue).Value =
strArrayValue.ParamValue

If Not IsNothing(objParamValue) Then
objParamValue = Nothing
End If
Next

So for every parameter a new instance is created and destroyed. But
now I'm wondering what the createparameter-method does. Should I use
it, or shouldn't I user it, that's the question. The above code works
fine, but I'm always looking for the most effectief en efficient way
to program, just to save resources. So if anyone has suggestions,
pleas let me know.

Thx.


Does your stored procedure accept a variable number of parameters? I
have never heard of that type of functonality in SQL server or Oracle.
Most likely you will either have to call your SP multiple times, or
the SP wants an array values.

If your SP does accept a variable number of parameters, you need to
give each parameter a different name. you could append a number to
each parameter so it is incremented each time. However most DBs want
the SP parameter names to match the name of the param as defined in
the SP

Hi,

I'm experiencing the problem mentioned in the title above. Briefly, I
build a page in which I include a class. This class contains a
procedure to add a value to an ArrayList. The value is hold by a
structure. So all elements in the arraylist are in fact a structure.
As you can see in the code below. Now what I'm trying to do is for
each value in the Arraylist create and add a parameter. Then I try to
execute a SP. This only works if I use one parameter. If I use
multiple parameters I get the above array. Does anyone see the
problem?

I'm using APS.NET Framework 1.1, VS.NET, SQL 2000

First I put the code of the page and after that the code of the class


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Dim objDataAccess As New
ClassLibrary1.SymaxDataAccess.clsSqlDataAccess()
Dim dummy As String
Dim dbRecordSet As SqlClient.SqlDataReader

objDataAccess.strStoredProcedure = "Select_Test"
objDataAccess.AddParameter("@Error", "", SqlDbType.Int,
ParameterDirection.Output, 4)
objDataAccess.AddParameter("@rowcount", "", SqlDbType.Int,
ParameterDirection.Output, 4)
objDataAccess.AddParameter("@FLD_Parameter", "", SqlDbType.Int,
ParameterDirection.Input, 4)
dbRecordSet = objDataAccess.DbRecordSet()


While (dbRecordSet.Read())
dummy = dummy + "<br>" + CStr(dbRecordSet(0))

End While

objDataAccess = Nothing

If Not (dbRecordSet.IsClosed) Then
dbRecordSet.Close()
End If


myLabel.Text = dummy


End Sub

Codebehind bestand Eind


Gebruikte class:


Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections

Namespace SymaxDataAccess

'*******************************************************
'
' clsSqlDataAccess Class
'
' Business/Data Logic Class that encapsulates all data
' logic necessary to add/login/query customers within
' the [] database.
'
' TO DO: Error handeling
'*******************************************************
Public Class clsSqlDataAccess

'*******************************************************
' Private var's
'*******************************************************
Private _strSP As String = ""
Private _objParamArray As New ArrayList()

Private Shared _strConnectString As String =
ConfigurationSettings.AppSettings("ConnectionString")

Private Structure _ParamList
Dim ParamName As String
Dim ParamValue As Object
Dim ParamType As String
Dim ParamIO As String
Dim ParamSize As Integer
End Structure


Sub New()
MyBase.New()
_strSP = ""
End Sub


#Region "clsDataAccess Properties"
'*******************************************************
' clsDataAccess Properties
'*******************************************************
Public WriteOnly Property strStoredProcedure() As String
Set(ByVal Value As String)
_strSP = Value
End Set
End Property

Public ReadOnly Property DbRecordSet() As SqlDataReader
Get

' Create Instance of Connection and Command Object
Dim objConnection As New SqlConnection()
Dim objCommand As New SqlCommand()
Dim strArrayValue As _ParamList
Dim objParamValue As New SqlParameter()


With objConnection
.ConnectionString = _strConnectString
.Open()
End With

With objCommand
.CommandTimeout = 15
.CommandType = CommandType.StoredProcedure
.Connection = objConnection
.CommandText = _strSP
End With

For Each strArrayValue In _objParamArray
objCommand.CreateParameter()
objParamValue.ParameterName = strArrayValue.ParamName
objParamValue.Size = strArrayValue.ParamSize
objParamValue.Direction = strArrayValue.ParamIO
objParamValue.Precision = strArrayValue.ParamType
objCommand.Parameters.Add(objParamValue).Value =
strArrayValue.ParamValue
Next


Try

Return objCommand.ExecuteReader

Catch sqlException As Exception

objCommand.Parameters.Clear()
objCommand.Dispose()

Finally

If (objConnection.State <> ConnectionState.Closed) Then
objConnection.Close()
End If

End Try
End Get
End Property


#End Region

#Region "clsDataAccess Methods"

'*******************************************************
' clsDataAccess Methods
'*******************************************************

Public Sub AddParameter(ByVal ParamName, ByVal ParamValue, ByVal
ParamType, ByVal ParamIO, ByVal ParamSize)

Dim objParamList As _ParamList

objParamList.ParamName = ParamName
objParamList.ParamValue = ParamValue
objParamList.ParamType = ParamType
objParamList.ParamIO = ParamIO
objParamList.ParamSize = ParamSize

_objParamArray.Add(objParamList)

objParamList = Nothing

End Sub

#End Region

End Class

End Namespace

Error:

System.ArgumentException: The SqlParameter with ParameterName 'xxxxx'
is already contained by this SqlParameterCollection

Thx for the help
Click to expand...
Click to expand...
 

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

Similar Threads

Populating a gridview (.net 2.0) using objectdatasource 3
SqlParameter is already contained by another SqlParameterCollectio 2
No parameterless constructor error for dataview 8
Why is this keeping the old values? 3
The connection is already Open (state=Open). 1

Top