Reusing parameters

[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")

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)
else
Call AddNewProfileDefault()
parameters(0).value = new SqlParameter("@UserID",SqldbType.Int)
parameters(0).value = session("UserID")
DBReader.Close()
dbReader = myDbObject.RunProcedure("GetProfileList", parameters)
StoredProfiles.DataSource=dbReader
StoredProfiles.DataValueField="ApplicantProfileID"
StoredProfiles.DataTextField= "ProfileName"
StoredProfiles.databind()
end if
***************************************************************

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. But the 2nd time gets
the error.

I tried to redim the paramaters array:
redim parameters(1)
parameters(0).value = new SqlParameter("@UserID",SqldbType.Int)
parameters(0).value = session("UserID")

But go an error at the "new SqlParameter" line:

Object reference not set to an instance of an object.

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

 

[Aytaç ÖZAY]

Hi Tom,

I think that your code has mistakes.

1) Dim parameters As SqlParameter () = { _
New SqlParameter("@UserID",SqldbType.BigInt) }
..
..
..
2) parameters(0).value = new SqlParameter("@UserID",SqldbType.Int)
..
..
..

In your code you added @UserID parameter in parameters collection twice, so
you can get error in run time. You remove the second insertion line from
your code, you won't get an error about @UserID parameter.

Have a nice day,

Aytaç ÖZAY
MSc.in CSc., MCP, Dynamics AX Technical Consultant
Microsoft Academic Software Developer Group Member

 

[tshad]

Hi Tom,

I think that your code has mistakes.

You're right. I just saw the mistake. The problem isn't @UserID, but
parameters(0).value. It needs to be parameters(0). Once I fixed that, it
worked fine.

Thanks,

Tom