Trouble clearing SqlParameters from my Command object

(note - sorry for the crosspost - I sent to .vb thinking i was sending to
.adonet).

I implemented a very small, basic data access layer for my web application.
It works just fine, except for this one bug.

One of my methods returns an abstracted dataset. To accomodate X number of
input parameters, I created a function signature that accepts a ParamArray
of SqlParameters as well as the name of the stored proc. In the body of the
function I loop through the param array and append each object to the
Parameters collection of my Command object.

The problem comes up when I try to append a SqlParameter object that I have
already used in a previous call, that is also an argument for the next
stored procedure. For example, I have:

dim objParam1 as New SqlParameter("@userid", intUserid)
dim objDS as DataSet
objDS = objDAL.GetDataSet("storedprocname", objParam1)

' do some stuff with the dataset, now try a new query, different proc, same
parameter
objDS.clear()
objDS = objDAL.GetDataSet("anotherprocname", objParam1)

The error returned is something like "There is already a parameter named
objParam1 in the SqlParametersCollcetion.

Prior to adding each parameter, I check its count and, if greater than 0,
remove each item before appending the new params. In debugging the count is
always 0, so how can there be an SqlParameter object in its collection?

' create our command object with the passed in ProcedureName.

' and sqlparameter object.

Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)

Dim sqlParam As SqlParameter

objCommand.CommandType = CommandType.StoredProcedure

Me.LocalCommand = objCommand

If objCommand.Parameters.Count > 0 Then

' in case any parameters are hanging around, clear them out

For Each sqlParam In objCommand.Parameters

objCommand.Parameters.Remove(sqlParam)

Next

End If

objCommand.Parameters.Clear()

' add the parameters passed in from the ParamArray

' into the command's parameters collection.

For Each sqlParam In DataParameters

sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE

Next

What element am I missing please? This has me bending my brain pretty badly.
Thank you in advance.

William
Tue Sep 16 13:34:25 CDT 2003

For Each sqlParam In DataParameters--Try changing this to
objCommand.Parameters. It looks like DataParameters is a different
reference and I don't see any code that clears or removes the params from it

sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
>
> Next

"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> (note - sorry for the crosspost - I sent to .vb thinking i was sending to
> .adonet).
>
> I implemented a very small, basic data access layer for my web
application.
> It works just fine, except for this one bug.
>
> One of my methods returns an abstracted dataset. To accomodate X number of
> input parameters, I created a function signature that accepts a ParamArray
> of SqlParameters as well as the name of the stored proc. In the body of
the
> function I loop through the param array and append each object to the
> Parameters collection of my Command object.
>
> The problem comes up when I try to append a SqlParameter object that I
have
> already used in a previous call, that is also an argument for the next
> stored procedure. For example, I have:
>
> dim objParam1 as New SqlParameter("@userid", intUserid)
> dim objDS as DataSet
> objDS = objDAL.GetDataSet("storedprocname", objParam1)
>
> ' do some stuff with the dataset, now try a new query, different proc,
same
> parameter
> objDS.clear()
> objDS = objDAL.GetDataSet("anotherprocname", objParam1)
>
> The error returned is something like "There is already a parameter named
> objParam1 in the SqlParametersCollcetion.
>
> Prior to adding each parameter, I check its count and, if greater than 0,
> remove each item before appending the new params. In debugging the count
is
> always 0, so how can there be an SqlParameter object in its collection?
>
> ' create our command object with the passed in ProcedureName.
>
> ' and sqlparameter object.
>
> Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
>
> Dim sqlParam As SqlParameter
>
> objCommand.CommandType = CommandType.StoredProcedure
>
> Me.LocalCommand = objCommand
>
> If objCommand.Parameters.Count > 0 Then
>
> ' in case any parameters are hanging around, clear them out
>
> For Each sqlParam In objCommand.Parameters
>
> objCommand.Parameters.Remove(sqlParam)
>
> Next
>
> End If
>
> objCommand.Parameters.Clear()
>
> ' add the parameters passed in from the ParamArray
>
> ' into the command's parameters collection.
>
> For Each sqlParam In DataParameters
>
> sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
>
> Next
>
> What element am I missing please? This has me bending my brain pretty
badly.
> Thank you in advance.
>
>
>
>

Elliot
Tue Sep 16 13:51:19 CDT 2003

William:

Thank you for your reply.

I dont follow what you mean. Upon each call the value of DataParameters
should change. If I change the line to what you indicated, how will my
function know which parameter object to add to it?

"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:Ovy4SDIfDHA.1872@TK2MSFTNGP09.phx.gbl...
> For Each sqlParam In DataParameters--Try changing this to
> objCommand.Parameters. It looks like DataParameters is a different
> reference and I don't see any code that clears or removes the params from
it
>
> sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> >
> > Next
>
> "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
> news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> > (note - sorry for the crosspost - I sent to .vb thinking i was sending
to
> > .adonet).
> >
> > I implemented a very small, basic data access layer for my web
> application.
> > It works just fine, except for this one bug.
> >
> > One of my methods returns an abstracted dataset. To accomodate X number
of
> > input parameters, I created a function signature that accepts a
ParamArray
> > of SqlParameters as well as the name of the stored proc. In the body of
> the
> > function I loop through the param array and append each object to the
> > Parameters collection of my Command object.
> >
> > The problem comes up when I try to append a SqlParameter object that I
> have
> > already used in a previous call, that is also an argument for the next
> > stored procedure. For example, I have:
> >
> > dim objParam1 as New SqlParameter("@userid", intUserid)
> > dim objDS as DataSet
> > objDS = objDAL.GetDataSet("storedprocname", objParam1)
> >
> > ' do some stuff with the dataset, now try a new query, different proc,
> same
> > parameter
> > objDS.clear()
> > objDS = objDAL.GetDataSet("anotherprocname", objParam1)
> >
> > The error returned is something like "There is already a parameter named
> > objParam1 in the SqlParametersCollcetion.
> >
> > Prior to adding each parameter, I check its count and, if greater than
0,
> > remove each item before appending the new params. In debugging the count
> is
> > always 0, so how can there be an SqlParameter object in its collection?
> >
> > ' create our command object with the passed in ProcedureName.
> >
> > ' and sqlparameter object.
> >
> > Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
> >
> > Dim sqlParam As SqlParameter
> >
> > objCommand.CommandType = CommandType.StoredProcedure
> >
> > Me.LocalCommand = objCommand
> >
> > If objCommand.Parameters.Count > 0 Then
> >
> > ' in case any parameters are hanging around, clear them out
> >
> > For Each sqlParam In objCommand.Parameters
> >
> > objCommand.Parameters.Remove(sqlParam)
> >
> > Next
> >
> > End If
> >
> > objCommand.Parameters.Clear()
> >
> > ' add the parameters passed in from the ParamArray
> >
> > ' into the command's parameters collection.
> >
> > For Each sqlParam In DataParameters
> >
> > sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> >
> > Next
> >
> > What element am I missing please? This has me bending my brain pretty
> badly.
> > Thank you in advance.
> >
> >
> >
> >
>
>

David
Tue Sep 16 13:54:37 CDT 2003


"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> (note - sorry for the crosspost - I sent to .vb thinking i was sending to
> .adonet).
>
> I implemented a very small, basic data access layer for my web
application.
> It works just fine, except for this one bug.
>
> One of my methods returns an abstracted dataset. To accomodate X number of
> input parameters, I created a function signature that accepts a ParamArray
> of SqlParameters as well as the name of the stored proc. In the body of
the
> function I loop through the param array and append each object to the
> Parameters collection of my Command object.
>
> The problem comes up when I try to append a SqlParameter object that I
have
> already used in a previous call, that is also an argument for the next
> stored procedure. For example, I have:
>

Why are you trying to reuse SqlParameter's.

SqlCommand, and SqlParameter are lightweight objects which should be used
once and then discarded. If you need a similar parameter later, just create
a new one.

David

Elliot
Tue Sep 16 14:04:01 CDT 2003

David:

Thanks for your reply.
Its a clarity issue more than anything. And besides, if I can reuse it, why
not?

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:ezcexPIfDHA.2188@TK2MSFTNGP10.phx.gbl...
>
> "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
> news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> > (note - sorry for the crosspost - I sent to .vb thinking i was sending
to
> > .adonet).
> >
> > I implemented a very small, basic data access layer for my web
> application.
> > It works just fine, except for this one bug.
> >
> > One of my methods returns an abstracted dataset. To accomodate X number
of
> > input parameters, I created a function signature that accepts a
ParamArray
> > of SqlParameters as well as the name of the stored proc. In the body of
> the
> > function I loop through the param array and append each object to the
> > Parameters collection of my Command object.
> >
> > The problem comes up when I try to append a SqlParameter object that I
> have
> > already used in a previous call, that is also an argument for the next
> > stored procedure. For example, I have:
> >
>
> Why are you trying to reuse SqlParameter's.
>
> SqlCommand, and SqlParameter are lightweight objects which should be used
> once and then discarded. If you need a similar parameter later, just
create
> a new one.
>
> David
>
>

William
Tue Sep 16 14:55:15 CDT 2003

I misread the code...sorry about that.. Let me look at it for a minute. I
think though that instead of a Param Array, you could add your parameters to
an ArrayList and iteratively walk through it adding your params and passing
in the arraylist to the handler function. Let me look at it for a few
mintues...I'll be right back.;

Bill
"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:OR4$KOIfDHA.3464@TK2MSFTNGP11.phx.gbl...
> William:
>
> Thank you for your reply.
>
> I dont follow what you mean. Upon each call the value of DataParameters
> should change. If I change the line to what you indicated, how will my
> function know which parameter object to add to it?
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> news:Ovy4SDIfDHA.1872@TK2MSFTNGP09.phx.gbl...
> > For Each sqlParam In DataParameters--Try changing this to
> > objCommand.Parameters. It looks like DataParameters is a different
> > reference and I don't see any code that clears or removes the params
from
> it
> >
> > sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> > >
> > > Next
> >
> > "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in
message
> > news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> > > (note - sorry for the crosspost - I sent to .vb thinking i was sending
> to
> > > .adonet).
> > >
> > > I implemented a very small, basic data access layer for my web
> > application.
> > > It works just fine, except for this one bug.
> > >
> > > One of my methods returns an abstracted dataset. To accomodate X
number
> of
> > > input parameters, I created a function signature that accepts a
> ParamArray
> > > of SqlParameters as well as the name of the stored proc. In the body
of
> > the
> > > function I loop through the param array and append each object to the
> > > Parameters collection of my Command object.
> > >
> > > The problem comes up when I try to append a SqlParameter object that I
> > have
> > > already used in a previous call, that is also an argument for the next
> > > stored procedure. For example, I have:
> > >
> > > dim objParam1 as New SqlParameter("@userid", intUserid)
> > > dim objDS as DataSet
> > > objDS = objDAL.GetDataSet("storedprocname", objParam1)
> > >
> > > ' do some stuff with the dataset, now try a new query, different proc,
> > same
> > > parameter
> > > objDS.clear()
> > > objDS = objDAL.GetDataSet("anotherprocname", objParam1)
> > >
> > > The error returned is something like "There is already a parameter
named
> > > objParam1 in the SqlParametersCollcetion.
> > >
> > > Prior to adding each parameter, I check its count and, if greater than
> 0,
> > > remove each item before appending the new params. In debugging the
count
> > is
> > > always 0, so how can there be an SqlParameter object in its
collection?
> > >
> > > ' create our command object with the passed in ProcedureName.
> > >
> > > ' and sqlparameter object.
> > >
> > > Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
> > >
> > > Dim sqlParam As SqlParameter
> > >
> > > objCommand.CommandType = CommandType.StoredProcedure
> > >
> > > Me.LocalCommand = objCommand
> > >
> > > If objCommand.Parameters.Count > 0 Then
> > >
> > > ' in case any parameters are hanging around, clear them out
> > >
> > > For Each sqlParam In objCommand.Parameters
> > >
> > > objCommand.Parameters.Remove(sqlParam)
> > >
> > > Next
> > >
> > > End If
> > >
> > > objCommand.Parameters.Clear()
> > >
> > > ' add the parameters passed in from the ParamArray
> > >
> > > ' into the command's parameters collection.
> > >
> > > For Each sqlParam In DataParameters
> > >
> > > sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> > >
> > > Next
> > >
> > > What element am I missing please? This has me bending my brain pretty
> > badly.
> > > Thank you in advance.
> > >
> > >
> > >
> > >
> >
> >
>
>

William
Tue Sep 16 15:01:30 CDT 2003

Where are you passing in the Command Object. It looks like GetDataSet has a
local command object in it. Is that right? I can't see the call, but from
the function calls, it looks like it probably does. That collection, inside
the class objDAL has a command object and in the first pass, you give it a
parameter named @Userid. Then, in the same instance, you call another
function passing in @UserID.

So in GetDataSet....make sure the paraters are clear out there...not outside
of the instance. If there isn't a command object in the class, then I way
off base, but I suspect there is and this is the problem..


Let me know.

Bill
"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> (note - sorry for the crosspost - I sent to .vb thinking i was sending to
> .adonet).
>
> I implemented a very small, basic data access layer for my web
application.
> It works just fine, except for this one bug.
>
> One of my methods returns an abstracted dataset. To accomodate X number of
> input parameters, I created a function signature that accepts a ParamArray
> of SqlParameters as well as the name of the stored proc. In the body of
the
> function I loop through the param array and append each object to the
> Parameters collection of my Command object.
>
> The problem comes up when I try to append a SqlParameter object that I
have
> already used in a previous call, that is also an argument for the next
> stored procedure. For example, I have:
>
> dim objParam1 as New SqlParameter("@userid", intUserid)
> dim objDS as DataSet
> objDS = objDAL.GetDataSet("storedprocname", objParam1)
>
> ' do some stuff with the dataset, now try a new query, different proc,
same
> parameter
> objDS.clear()
> objDS = objDAL.GetDataSet("anotherprocname", objParam1)
>
> The error returned is something like "There is already a parameter named
> objParam1 in the SqlParametersCollcetion.
>
> Prior to adding each parameter, I check its count and, if greater than 0,
> remove each item before appending the new params. In debugging the count
is
> always 0, so how can there be an SqlParameter object in its collection?
>
> ' create our command object with the passed in ProcedureName.
>
> ' and sqlparameter object.
>
> Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
>
> Dim sqlParam As SqlParameter
>
> objCommand.CommandType = CommandType.StoredProcedure
>
> Me.LocalCommand = objCommand
>
> If objCommand.Parameters.Count > 0 Then
>
> ' in case any parameters are hanging around, clear them out
>
> For Each sqlParam In objCommand.Parameters
>
> objCommand.Parameters.Remove(sqlParam)
>
> Next
>
> End If
>
> objCommand.Parameters.Clear()
>
> ' add the parameters passed in from the ParamArray
>
> ' into the command's parameters collection.
>
> For Each sqlParam In DataParameters
>
> sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
>
> Next
>
> What element am I missing please? This has me bending my brain pretty
badly.
> Thank you in advance.
>
>
>
>

David
Tue Sep 16 15:04:04 CDT 2003


"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:uNizQVIfDHA.1820@TK2MSFTNGP10.phx.gbl...
> David:
>
> Thanks for your reply.
> Its a clarity issue more than anything. And besides, if I can reuse it,
why
> not?
>

It's just that if you do you're on your own for figuring out the
dependencies.
For instance the Command has a reference to the connection, and the
parameter has a reference to the ParameterCollection, which is a field on
the Command. So you have to figure out the correct sequence to detach these
objects and reuse them.

David

Elliot
Tue Sep 16 15:29:16 CDT 2003

william:

I noticed that after I stared at your post for a while. But then I got an
unusual error after clearing the collection:
For now I've decided instead to use the other parameters at David's
suggestion, and that is working. It takes away from my encapsulation of my
DAL, but I need to get at least a prototype going on my project.

Thanks guys

"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:uaJm9zIfDHA.2484@TK2MSFTNGP09.phx.gbl...
> Where are you passing in the Command Object. It looks like GetDataSet has
a
> local command object in it. Is that right? I can't see the call, but
from
> the function calls, it looks like it probably does. That collection,
inside
> the class objDAL has a command object and in the first pass, you give it a
> parameter named @Userid. Then, in the same instance, you call another
> function passing in @UserID.
>
> So in GetDataSet....make sure the paraters are clear out there...not
outside
> of the instance. If there isn't a command object in the class, then I way
> off base, but I suspect there is and this is the problem..
>
>
> Let me know.
>
> Bill
> "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
> news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> > (note - sorry for the crosspost - I sent to .vb thinking i was sending
to
> > .adonet).
> >
> > I implemented a very small, basic data access layer for my web
> application.
> > It works just fine, except for this one bug.
> >
> > One of my methods returns an abstracted dataset. To accomodate X number
of
> > input parameters, I created a function signature that accepts a
ParamArray
> > of SqlParameters as well as the name of the stored proc. In the body of
> the
> > function I loop through the param array and append each object to the
> > Parameters collection of my Command object.
> >
> > The problem comes up when I try to append a SqlParameter object that I
> have
> > already used in a previous call, that is also an argument for the next
> > stored procedure. For example, I have:
> >
> > dim objParam1 as New SqlParameter("@userid", intUserid)
> > dim objDS as DataSet
> > objDS = objDAL.GetDataSet("storedprocname", objParam1)
> >
> > ' do some stuff with the dataset, now try a new query, different proc,
> same
> > parameter
> > objDS.clear()
> > objDS = objDAL.GetDataSet("anotherprocname", objParam1)
> >
> > The error returned is something like "There is already a parameter named
> > objParam1 in the SqlParametersCollcetion.
> >
> > Prior to adding each parameter, I check its count and, if greater than
0,
> > remove each item before appending the new params. In debugging the count
> is
> > always 0, so how can there be an SqlParameter object in its collection?
> >
> > ' create our command object with the passed in ProcedureName.
> >
> > ' and sqlparameter object.
> >
> > Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
> >
> > Dim sqlParam As SqlParameter
> >
> > objCommand.CommandType = CommandType.StoredProcedure
> >
> > Me.LocalCommand = objCommand
> >
> > If objCommand.Parameters.Count > 0 Then
> >
> > ' in case any parameters are hanging around, clear them out
> >
> > For Each sqlParam In objCommand.Parameters
> >
> > objCommand.Parameters.Remove(sqlParam)
> >
> > Next
> >
> > End If
> >
> > objCommand.Parameters.Clear()
> >
> > ' add the parameters passed in from the ParamArray
> >
> > ' into the command's parameters collection.
> >
> > For Each sqlParam In DataParameters
> >
> > sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> >
> > Next
> >
> > What element am I missing please? This has me bending my brain pretty
> badly.
> > Thank you in advance.
> >
> >
> >
> >
>
>

William
Tue Sep 16 15:57:42 CDT 2003

Good deal. I agree with his point too. Just out of curiousity, what
exception was being thrown?
"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:OewB6EJfDHA.2236@TK2MSFTNGP12.phx.gbl...
> william:
>
> I noticed that after I stared at your post for a while. But then I got an
> unusual error after clearing the collection:
> For now I've decided instead to use the other parameters at David's
> suggestion, and that is working. It takes away from my encapsulation of my
> DAL, but I need to get at least a prototype going on my project.
>
> Thanks guys
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> news:uaJm9zIfDHA.2484@TK2MSFTNGP09.phx.gbl...
> > Where are you passing in the Command Object. It looks like GetDataSet
has
> a
> > local command object in it. Is that right? I can't see the call, but
> from
> > the function calls, it looks like it probably does. That collection,
> inside
> > the class objDAL has a command object and in the first pass, you give it
a
> > parameter named @Userid. Then, in the same instance, you call another
> > function passing in @UserID.
> >
> > So in GetDataSet....make sure the paraters are clear out there...not
> outside
> > of the instance. If there isn't a command object in the class, then I
way
> > off base, but I suspect there is and this is the problem..
> >
> >
> > Let me know.
> >
> > Bill
> > "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in
message
> > news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> > > (note - sorry for the crosspost - I sent to .vb thinking i was sending
> to
> > > .adonet).
> > >
> > > I implemented a very small, basic data access layer for my web
> > application.
> > > It works just fine, except for this one bug.
> > >
> > > One of my methods returns an abstracted dataset. To accomodate X
number
> of
> > > input parameters, I created a function signature that accepts a
> ParamArray
> > > of SqlParameters as well as the name of the stored proc. In the body
of
> > the
> > > function I loop through the param array and append each object to the
> > > Parameters collection of my Command object.
> > >
> > > The problem comes up when I try to append a SqlParameter object that I
> > have
> > > already used in a previous call, that is also an argument for the next
> > > stored procedure. For example, I have:
> > >
> > > dim objParam1 as New SqlParameter("@userid", intUserid)
> > > dim objDS as DataSet
> > > objDS = objDAL.GetDataSet("storedprocname", objParam1)
> > >
> > > ' do some stuff with the dataset, now try a new query, different proc,
> > same
> > > parameter
> > > objDS.clear()
> > > objDS = objDAL.GetDataSet("anotherprocname", objParam1)
> > >
> > > The error returned is something like "There is already a parameter
named
> > > objParam1 in the SqlParametersCollcetion.
> > >
> > > Prior to adding each parameter, I check its count and, if greater than
> 0,
> > > remove each item before appending the new params. In debugging the
count
> > is
> > > always 0, so how can there be an SqlParameter object in its
collection?
> > >
> > > ' create our command object with the passed in ProcedureName.
> > >
> > > ' and sqlparameter object.
> > >
> > > Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
> > >
> > > Dim sqlParam As SqlParameter
> > >
> > > objCommand.CommandType = CommandType.StoredProcedure
> > >
> > > Me.LocalCommand = objCommand
> > >
> > > If objCommand.Parameters.Count > 0 Then
> > >
> > > ' in case any parameters are hanging around, clear them out
> > >
> > > For Each sqlParam In objCommand.Parameters
> > >
> > > objCommand.Parameters.Remove(sqlParam)
> > >
> > > Next
> > >
> > > End If
> > >
> > > objCommand.Parameters.Clear()
> > >
> > > ' add the parameters passed in from the ParamArray
> > >
> > > ' into the command's parameters collection.
> > >
> > > For Each sqlParam In DataParameters
> > >
> > > sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> > >
> > > Next
> > >
> > > What element am I missing please? This has me bending my brain pretty
> > badly.
> > > Thank you in advance.
> > >
> > >
> > >
> > >
> >
> >
>
>

Elliot
Wed Sep 17 06:27:59 CDT 2003

Oops. I though I posted it. Off the top of my head, it was something like
"collection may be modified". It was thrown as soon as I attempted to call
the remove method as I looped through the parameter collection.

for each objParam in Me.LocalCommand.Parameters
Me.LocalCommand.Parameters.Remove(objParam) 'thrown here
next objParam


"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:e1kHXTJfDHA.3024@tk2msftngp13.phx.gbl...
> Good deal. I agree with his point too. Just out of curiousity, what
> exception was being thrown?
> "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
> news:OewB6EJfDHA.2236@TK2MSFTNGP12.phx.gbl...
> > william:
> >
> > I noticed that after I stared at your post for a while. But then I got
an
> > unusual error after clearing the collection:
> > For now I've decided instead to use the other parameters at David's
> > suggestion, and that is working. It takes away from my encapsulation of
my
> > DAL, but I need to get at least a prototype going on my project.
> >
> > Thanks guys
> >
> > "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> > news:uaJm9zIfDHA.2484@TK2MSFTNGP09.phx.gbl...
> > > Where are you passing in the Command Object. It looks like GetDataSet
> has
> > a
> > > local command object in it. Is that right? I can't see the call, but
> > from
> > > the function calls, it looks like it probably does. That collection,
> > inside
> > > the class objDAL has a command object and in the first pass, you give
it
> a
> > > parameter named @Userid. Then, in the same instance, you call another
> > > function passing in @UserID.
> > >
> > > So in GetDataSet....make sure the paraters are clear out there...not
> > outside
> > > of the instance. If there isn't a command object in the class, then I
> way
> > > off base, but I suspect there is and this is the problem..
> > >
> > >
> > > Let me know.
> > >
> > > Bill
> > > "Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in
> message
> > > news:OYEwl4HfDHA.2248@TK2MSFTNGP09.phx.gbl...
> > > > (note - sorry for the crosspost - I sent to .vb thinking i was
sending
> > to
> > > > .adonet).
> > > >
> > > > I implemented a very small, basic data access layer for my web
> > > application.
> > > > It works just fine, except for this one bug.
> > > >
> > > > One of my methods returns an abstracted dataset. To accomodate X
> number
> > of
> > > > input parameters, I created a function signature that accepts a
> > ParamArray
> > > > of SqlParameters as well as the name of the stored proc. In the body
> of
> > > the
> > > > function I loop through the param array and append each object to
the
> > > > Parameters collection of my Command object.
> > > >
> > > > The problem comes up when I try to append a SqlParameter object that
I
> > > have
> > > > already used in a previous call, that is also an argument for the
next
> > > > stored procedure. For example, I have:
> > > >
> > > > dim objParam1 as New SqlParameter("@userid", intUserid)
> > > > dim objDS as DataSet
> > > > objDS = objDAL.GetDataSet("storedprocname", objParam1)
> > > >
> > > > ' do some stuff with the dataset, now try a new query, different
proc,
> > > same
> > > > parameter
> > > > objDS.clear()
> > > > objDS = objDAL.GetDataSet("anotherprocname", objParam1)
> > > >
> > > > The error returned is something like "There is already a parameter
> named
> > > > objParam1 in the SqlParametersCollcetion.
> > > >
> > > > Prior to adding each parameter, I check its count and, if greater
than
> > 0,
> > > > remove each item before appending the new params. In debugging the
> count
> > > is
> > > > always 0, so how can there be an SqlParameter object in its
> collection?
> > > >
> > > > ' create our command object with the passed in ProcedureName.
> > > >
> > > > ' and sqlparameter object.
> > > >
> > > > Dim objCommand As New SqlCommand(ProcedureName, Me.Connection)
> > > >
> > > > Dim sqlParam As SqlParameter
> > > >
> > > > objCommand.CommandType = CommandType.StoredProcedure
> > > >
> > > > Me.LocalCommand = objCommand
> > > >
> > > > If objCommand.Parameters.Count > 0 Then
> > > >
> > > > ' in case any parameters are hanging around, clear them out
> > > >
> > > > For Each sqlParam In objCommand.Parameters
> > > >
> > > > objCommand.Parameters.Remove(sqlParam)
> > > >
> > > > Next
> > > >
> > > > End If
> > > >
> > > > objCommand.Parameters.Clear()
> > > >
> > > > ' add the parameters passed in from the ParamArray
> > > >
> > > > ' into the command's parameters collection.
> > > >
> > > > For Each sqlParam In DataParameters
> > > >
> > > > sqlParam = objCommand.Parameters.Add(sqlParam) ' ERROR THROWN HERE
> > > >
> > > > Next
> > > >
> > > > What element am I missing please? This has me bending my brain
pretty
> > > badly.
> > > > Thank you in advance.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

David
Wed Sep 17 09:32:51 CDT 2003


"Elliot M. Rodriguez" <elliotmrodriguez@hotmail.spam.com> wrote in message
news:O0pWG7QfDHA.2364@TK2MSFTNGP09.phx.gbl...
> Oops. I though I posted it. Off the top of my head, it was something like
> "collection may be modified". It was thrown as soon as I attempted to call
> the remove method as I looped through the parameter collection.
>
> for each objParam in Me.LocalCommand.Parameters
> Me.LocalCommand.Parameters.Remove(objParam) 'thrown here
> next objParam
>

In general you cannot modify a collection while enumerating its members.
Do something like:

do until Me.LocalCommand.Parameters.Count = 0
Me.LocalCommand.Parameters.Remove(0)
loop

David