By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,578 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,578 IT Pros & Developers. It's quick & easy.

Trouble Removing SqlParameter objects from Command object

P: n/a
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.
Nov 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.