Reusing parameters

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

tshad

I am trying to reuse my parameters to re-call my stored procedures but get
the error:

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

My code is essentially:
***************************************************************
Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.BigInt) }

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

for i as integer = 0 to 1
dbReader = myDbObject.RunProcedure("GetProfileList", parameters)
StoredProfiles.DataSource=dbReader
StoredProfiles.DataValueField="ApplicantProfileID"
StoredProfiles.DataTextField= "ProfileName"
StoredProfiles.databind()
StoredProfiles.Items.Insert(0, new ListItem("Select Stored Search",""))
if not (StoredProfiles.Items.FindByText("Default")) is nothing then
StoredProfiles.Items.FindByText("Default").Selected = true
SelectProfile(StoredProfiles.SelectedValue)
exit for
else
Call AddNewProfileDefault()
parameters(0).value = new SqlParameter("@UserID",SqldbType.Int)
parameters(0).value = session("UserID")
DBReader.Close()
end if
next
***************************************************************

What the above code does is look for a list of profiles. If there is no
"Default" profile, it creates one and then goes back does the
"GetProfileList" Stored Procedure again (2nd loop). But the 2nd time gets
the error.

I thought that if I created another SqlParameter (new SqlParameter) that it
would be pointing another SqlParameter but apparently not. I also tried
closing the DBReader to see if this would help but it didn't.

How can I make this work?

Thanks,

Tom
 
¤ I am trying to reuse my parameters to re-call my stored procedures but get
¤ the error:
¤
¤ The SqlParameter with ParameterName '@UserID' is already contained by
¤ another SqlParameterCollection.
¤
¤ My code is essentially:
¤ ***************************************************************
¤ Dim parameters As SqlParameter () = { _
¤ New SqlParameter("@UserID",SqldbType.BigInt) }
¤
¤ parameters(0).value = session("UserID")
¤
¤ for i as integer = 0 to 1
¤ dbReader = myDbObject.RunProcedure("GetProfileList", parameters)
¤ StoredProfiles.DataSource=dbReader
¤ StoredProfiles.DataValueField="ApplicantProfileID"
¤ StoredProfiles.DataTextField= "ProfileName"
¤ StoredProfiles.databind()
¤ StoredProfiles.Items.Insert(0, new ListItem("Select Stored Search",""))
¤ if not (StoredProfiles.Items.FindByText("Default")) is nothing then
¤ StoredProfiles.Items.FindByText("Default").Selected = true
¤ SelectProfile(StoredProfiles.SelectedValue)
¤ exit for
¤ else
¤ Call AddNewProfileDefault()
¤ parameters(0).value = new SqlParameter("@UserID",SqldbType.Int)
¤ parameters(0).value = session("UserID")
¤ DBReader.Close()
¤ end if
¤ next
¤ ***************************************************************
¤
¤ What the above code does is look for a list of profiles. If there is no
¤ "Default" profile, it creates one and then goes back does the
¤ "GetProfileList" Stored Procedure again (2nd loop). But the 2nd time gets
¤ the error.
¤
¤ I thought that if I created another SqlParameter (new SqlParameter) that it
¤ would be pointing another SqlParameter but apparently not. I also tried
¤ closing the DBReader to see if this would help but it didn't.
¤

The problem is that you've already added the parameter @UserID to the collection. If you want to
reuse the parameter then just change the value instead of adding a new SQLParameter object.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul Clement said:
¤ I am trying to reuse my parameters to re-call my stored procedures but
get
¤ the error:
¤
¤ The SqlParameter with ParameterName '@UserID' is already contained by
¤ another SqlParameterCollection.
¤
¤ My code is essentially:
¤ ***************************************************************
¤ Dim parameters As SqlParameter () = { _
¤ New SqlParameter("@UserID",SqldbType.BigInt) }
¤
¤ parameters(0).value = session("UserID")
¤
¤ for i as integer = 0 to 1
¤ dbReader = myDbObject.RunProcedure("GetProfileList", parameters)
¤ StoredProfiles.DataSource=dbReader
¤ StoredProfiles.DataValueField="ApplicantProfileID"
¤ StoredProfiles.DataTextField= "ProfileName"
¤ StoredProfiles.databind()
¤ StoredProfiles.Items.Insert(0, new ListItem("Select Stored
Search",""))
¤ if not (StoredProfiles.Items.FindByText("Default")) is nothing then
¤ StoredProfiles.Items.FindByText("Default").Selected = true
¤ SelectProfile(StoredProfiles.SelectedValue)
¤ exit for
¤ else
¤ Call AddNewProfileDefault()
¤ parameters(0).value = new SqlParameter("@UserID",SqldbType.Int)
¤ parameters(0).value = session("UserID")
¤ DBReader.Close()
¤ end if
¤ next
¤ ***************************************************************
¤
¤ What the above code does is look for a list of profiles. If there is no
¤ "Default" profile, it creates one and then goes back does the
¤ "GetProfileList" Stored Procedure again (2nd loop). But the 2nd time
gets
¤ the error.
¤
¤ I thought that if I created another SqlParameter (new SqlParameter) that
it
¤ would be pointing another SqlParameter but apparently not. I also tried
¤ closing the DBReader to see if this would help but it didn't.
¤

The problem is that you've already added the parameter @UserID to the
collection. If you want to
reuse the parameter then just change the value instead of adding a new
SQLParameter object.
Click to expand...

Actually, I just found the problem. It was my error.

It was not that I was using @UserID, but that I was applying it to
parameters(0).value instead of parameters(0) - (without the .value).

After I did that, it worked fine.

Thanks,

Tom
 
Back
Top