How to free resources and reuse objects?

[Michael Hart]

I am trying to get a C_Value for three different providers from one table
using parameter queries to specify the providers. Instead of making
multiple data adapters for this I am trying to resue the adapters,
datasources, etc.

It successfully reads and return the correct C_Value for the Provider1 but
throws an exception for the next one. I get an exception stating that "The
OledbParameter with ParameterName "?" is already contained by another
OledbParameterCollection"

I thought I was clearing everyhting I needed to and using the same
OledbParameter??? Anyone got any clues?

Private Sub GetC()
Dim sqlStr As String
Dim C As Double
Dim con As New OleDb.OleDbConnection()
Dim cmd As New OleDb.OleDbCommand()
Dim da As New OleDb.OleDbDataAdapter()
Dim ds As New DataSet()
Dim parm As New OleDb.OleDbParameter()

C = 0
Try
sqlStr = "SELECT [C_Value], [ID] FROM

WHERE ([ID]= ? )"

con.ConnectionString = ADB_strCon
cmd.Connection = con
cmd.CommandText = sqlStr
parm.Direction = ParameterDirection.Input
parm.DbType = DbType.String
parm.ParameterName = "?"
parm.Value = txtProvider1.Text
parm.Size = Len(txtProvider1.Text)
cmd.Parameters.Add(parm)

da.SelectCommand = cmd
da.Fill(ds, "C_Value")
C = C + ds.Tables("C_Value").Rows(0)(0)

ds.Clear()
ds.Dispose()
da.Dispose()
cmd.Dispose()

cmd.Connection = con
cmd.CommandText = sqlStr
parm.Value = txtProvider2.Text
parm.Size = Len(txtProvider2.Text)
cmd.Parameters.Add(parm) <<-------------------- exception
occures here

da.SelectCommand = cmd
da.Fill(ds, "C_Value")
C = C + ds.Tables("C_Value").Rows(0)(0)

ds.Clear()
ds.Dispose()
da.Dispose()
cmd.Dispose()

cmd.Connection = con
cmd.CommandText = sqlStr
parm.Value = txtProvider3.Text
parm.Size = Len(txtProvider3.Text)
cmd.Parameters.Add(parm)

da.SelectCommand = cmd
da.Fill(ds, "C_Value")
C = C + ds.Tables("C_Value").Rows(0)(0)

ds.Clear()
ds.Dispose()
da.Dispose()
cmd.Dispose()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub

 

[Michael Hart]

Found the issue. .Dispose() must not really remove all the resources used
by the object - or at least it doesn't remove those allocated for
parameters.

Excecuting the .Parameters.Clear() method removed the parameters.

I am trying to get a C_Value for three different providers from one table
using parameter queries to specify the providers. Instead of making
multiple data adapters for this I am trying to resue the adapters,
datasources, etc. [snip]

ds.Clear()
ds.Dispose()
da.Dispose()

cmd.Parameters.Clear()

cmd.Dispose()

[snip]

Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub