Executing SP while data read is open?

[Brett]

I execute an SQL stored procedure that returns 5 records
("SqlCmd_SpamUpdate_spfBlackUnprocessed"). I then open a data reader, loop
through those five records while calling stored procedures that does
updates. The second SP needs information from the 5 records being returned.
The problem is that when I try to execute the stored procedure inside of the
data reader loop, I get an error that a data reader is already open and
should be closed before executing the stored procedure. Any ideas how I can
fix this? Thanks, Brett.


cn_mydatabase.Open()

Dim drspfblacklist As SqlDataReader
drspfblacklist =
SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader()

While drspfblacklist.Read

-- do something ---

With SqlCmd_BlacklistUpdateStatus
.CommandType = System.Data.CommandType.StoredProcedure
.Parameters("@messageid").Value =
drspfblacklist.Item("message_id")
.Parameters("@subpart").Value = BlackListStatus
.ExecuteNonQuery()
End With

End While

drspfblacklist.Close()
SqlCmd_SpamUpdate_spfBlackUnprocessed.Dispose()
cn_mydatabase.Close()

 

[W.G. Ryan eMVP]

Open another connection for the second command - or wait until ADO.NET 2.0
;-)

 

[Brett]

This means I'll need to create another connection? Or can it be cloned?

Thanks,
Brett

 

[Brett]

I actually copied the current connection and gave it a different name.
Thanks.

Brett

 

[Dave Fancher]

You could also use a DataAdapter to fill a DataTable (replacing the reader),
then iterate through the rows collection of your table, calling the SP as
necessary during your loop.

 

[Brett]

I see.

I did copy connection1 and use it for the data reader. This does work.
How is it different than creating another data adapter?

Thanks,
Brett

 

[Dave Fancher]

Quite simply, you now have two connections to your database.

A DataAdapter is an object used to manage a group of commands for filling a
DataTable and mapping how any changes to the DataTable should be mapped back
to the DataSource. Quite often, only the SelectCommand property of a
DataAdapter is used.

When the Fill method of the DataAdapter is called, the command defined by
the SelectCommand property is executed (along with a few other methods for
retrieving the schema, etc...) and a "snapshot" of the result set is stored
in memory. The DataReader used for populating the DataTable is disposed
before the Fill method exits.

This method allows you to use a single connection rather than having to
manage two.