SqlParameter error

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

I keep getting this error when I am executing a Stored Procedure multiple
times.

The SqlParameter with ParameterName '@ApplicantID' is already contained by
another SqlParameterCollection.

Anyone know what causes this and how to fix it?

Thanks,

Tom
 
Sushil Chordia said:
Are you using the same parameter object in two different
ParameterCollection:?

Actually, this is contained in an object. I am calling the same Object
twice, but I am recreating the SqlCommand object (I think).

Here is a page that calls the MyDbObject twice with the same parameters.

***************************************************************
sub Resume_Click(sender as Object, e as eventArgs)
Dim TemplateID as Integer
Dim Title as String

Dim myDbObject as new DbObject()
Dim dbReader As SqlDataReader

Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.VarChar,20) }

parameters(0).value = session("UserID")

dbReader = myDbObject.RunProcedure("GetDefaultTemplate", parameters)

if dbReader.Read then
Session("TemplateID") = dbReader("ResumeTemplateID")
response.redirect("DisplayTemplate.aspx?s=1")
else

dbReader = myDbObject.RunProcedure("GetTemplate",parameters)

if dbReader.Read then
response.redirect("Template.aspx")
end if
end if
end sub
********************************************************************

The Object is:

**********************************************************************
Imports System
Imports System.Data
Imports System.Data.SqlClient

Namespace MyFunctions

Public Class DbObject


Protected myConnection As SqlConnection
Private myConnectionString As String

Public Sub New()
myConnectionString =
System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING
_ft")
myConnection = New SqlConnection(myConnectionString)
End Sub


Public Overloads Function RunProcedure( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlDataReader

Dim returnReader As SqlDataReader

if myConnection.State = ConnectionState.Open then
myConnection.Close()
end if
myConnection.Open()
Dim command As SqlCommand = _
BuildQueryCommand(storedProcName, parameters)
command.CommandType = CommandType.StoredProcedure

returnReader = command.ExecuteReader( _
CommandBehavior.CloseConnection)
' Connection will be closed automatically

Return returnReader

End Function

' Properties
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

' Protected property that exposes the connection string to inheriting
' classes. Read-Only.
' ---
Protected ReadOnly Property ConnectionString() As String
Get
Return myConnectionString
End Get
End Property


' Builds a SqlCommand designed to return a SqlDataReader,
' and not an actual integer value.
' ----
Private Function BuildQueryCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter()) _
As SqlCommand

Dim command As New SqlCommand(storedProcName, myConnection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter
For Each parameter In parameters
command.Parameters.Add(parameter)
Next

Return command

End Function


End Class

End Namespace
**********************************************************************

The BuildQueryCommand does a:

Dim command As New SqlCommand(storedProcName, myConnection)

each time I call call MyDbObject.RunProcedure().

What do I need to do to prevent this error, if I call the object multiple
times?

Thanks,

Tom
 
The error is exactly what Sushil told you. The same object instance causes
the same SqlParameter instance to be recycled - that is not allowed.

The "parameters" array (the very same object) is being Reused in two
commands - GetDefaultTempalte and GetTemplate - You can't do that. Create
new instances of Parameters, or copy them around into new instances.

If I misinterpreted your code below, it's probably because there is tonnes
of stuff on my plate, but I am quite certain the problem is because "you are
trying to reuse the same parameter instance in two parametercollections" ---
Just don't do that.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
 
Sahil Malik said:
The error is exactly what Sushil told you. The same object instance causes
the same SqlParameter instance to be recycled - that is not allowed.

You were right (as was Sushil).

I am still confused as to why this caused the error.

I did as you suggested by making another parameters array and it worked
fine.

***************************************************************
sub Resume_Click(sender as Object, e as eventArgs)
Dim TemplateID as Integer
Dim Title as String

Dim myDbObject as new DbObject()
Dim dbReader As SqlDataReader

Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.VarChar,20) }

parameters(0).value = session("UserID")

dbReader = myDbObject.RunProcedure("GetDefaultTemplate", parameters)

if dbReader.Read then
Session("TemplateID") = dbReader("ResumeTemplateID")
response.redirect("DisplayTemplate.aspx?s=1")
else

Dim parameters2 As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.VarChar,20) }

parameters2(0).value = session("UserID")
dbReader = myDbObject.RunProcedure("GetTemplate",parameters2)

if dbReader.Read then
response.redirect("Template.aspx")
end if
end if
end sub
********************************************************************

But I am copying it into another collection in my Class.

Since I am actually not using the parameters array for anything more than to
copy it into the SqlCommand parameters, why is it causing the problem?

Also, is there a better way to handle the array than to just create another
array?

Can't I re-use the same array.

In a SqlCommand array, I would just use a Clear command.

Thanks,

Tom
The "parameters" array (the very same object) is being Reused in two
commands - GetDefaultTempalte and GetTemplate - You can't do that. Create
 
Hi Tom,
But I am copying it into another collection in my Class.

These lines of code as shown below ---

Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.VarChar,20) }

parameters(0).value = session("UserID")

dbReader = myDbObject.RunProcedure("GetDefaultTemplate", parameters)


They don't actually copy the parameter array in entirety. What they do is,
they copy around a reference of the object (SqlParameter), so essentially in
multiple method calls, you are reusing the same actual object instance.

HTH :-)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Sahil Malik said:
Hi Tom,


These lines of code as shown below ---

Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.VarChar,20) }

parameters(0).value = session("UserID")

dbReader = myDbObject.RunProcedure("GetDefaultTemplate", parameters)

So let me see if I have this right.

This line:

Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.VarChar,20) }

Sets the array and assigns the object to the array.

This line:

parameters(0).value = session("UserID")

Just sets a value to the object (not the array).

And if I did:

parameters(0).value = something else

I would still be setting the value of the same object, which would be fine
until it was executed once. So once I do:

returnReader = command.ExecuteReader()

I can't use that object again. Everything would be fine until I did another
Execute.Reader (which is what would give me an error).

Can I just do a redim after the Execute.Reader():

Redim parameters(1)
parameters(0).value = session("UserID")

and then re-execute the Execute.Reader().

Or could I just do:

parameters(0) = New SqlParameter("@UserID",SqldbType.VarChar,20)
parameters(0).value = session("UserID") or whatever.

I assume that this would work as we would now be pointing at another object
(unless I am mistaken).

Thanks,

Tom
 
Back
Top