Problems with running cursor and insert

[Lars_V_J]

Hi,

I have a cursor running (a reader), and then I want to try to insert things that I find into another table.

I then get
There is already an open DataReader associated with this Connection which must be closed first.

Why can't I have a reader running on one table while inserting into another?
I run MSSQL 2000.

Also, I've heard that I can't have two active readers running on a MSSQL 2000 at the same time. I sometimes have nested cursor loops, so I hope there's a way around this.

Thanks in advance

[Alsvha]

If you use a "reader" (datareader) it is is most languages a read only - forward only - structure.
So you can't insert into that reader for those reasons.
Furthermore that reader "claims" a connection to the database, so you can't reuse the connection until that connection is closed (closing the reader), which is why you get the message that you get.

Depending on what you need, you need to use a different architecture or methodology.
You might just need to create another connection, you can use for updating the other table.
It is a "problem" with readers, that you need to be careful with the connections and closing the reader after use, because otherwise you'll run out of possible connections to the database. Readers are fast, but they have their restrictions and should be used with care.

[Lars_V_J]

I now push things into a DataSet. But I think that the reader is nearly useless, then. It is understandable that it can't allow writing to the table you read from. But it most certainly ought to be possible to change another table.
If one have to make another connection, then transactions will become useless.

But the DataSet works, fortunately :-)

Thank you for your time :-)

[Alsvha]

A reader is far from useless, for example you use a reader to populate the dataset
If you need things done in a transaction, then several methods exists for this as well, right from a transaction scope in .NET down to the transactions in the database.

The issue with a reader is it is a structure made for fast reading and nothing else, and to facilitate that, it needs/consumes a connection until closed. (A common cause of leaks in many solutions)
When you need more complex/other things done, other tools exists and must(can) be used.