Dave
Thu Jan 27 06:45:08 CST 2005
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.
--
Dave Fancher
http://davefancher.blogspot.com
"Brett" <no@spam.net> wrote in message
news:eaLf3rGBFHA.2792@TK2MSFTNGP15.phx.gbl...
>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" <eijitek@comcast.net> wrote in message
> news:q8GdnamERPVW8WXcRVn-rA@comcast.com...
>> 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()
>>>
>>
>>
>
>