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("ConnectionStrin g")
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
