Re: Executing SP while data read is open?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dave Fancher (eijitek_at_comcast.net)
Date: 01/27/05 

Date: Wed, 26 Jan 2005 23:23:38 -0500

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. 

-- 
Dave Fancher
http://davefancher.blogspot.com
"Brett" <no@spam.net> wrote in message 
news:eXMVuIBBFHA.3368@TK2MSFTNGP10.phx.gbl...
>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()
>

Relevant Pages