Executing SP while data read is open?

  • Thread starter Thread starter Brett
  • Start date Start date
B

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()
 
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.
 
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
 
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.
 
Back
Top