SqlDataReader & ExecuteNonQuery

[JamesNZ]

I'm looping through a reader checking various data and am wanting to update it with an ExecuteNonQuery.

The problem I'm facing is that when I try to run the ExecuteNonQuery within the reader loop I get the following error - "There is already an open Datareader associated with this connection which must be closed first".

I know I can't run an ExecuteNonQuery isnide a datareader but what are my alternatives? I'm just wanting to run a very simple update query half way through each while loop pass.

[Aaron Young]

Without digging in too deep, I'd say you could either pull down the data in a DataSet and enumerate that instead of using a DataReader which holds a connection open, then you can execute your ExecuteNonQuery without issue.

Or, you could create a 2nd connection to the Database for the purposes of Executing the Query.

[JamesNZ]

Thanks for that Aaron.

I'm having trouble looping through the dataset correctly. Am using:

For Each dr In dt.Rows

and am getting myself confused. How can I successfully loop through every row in the dataset?

[schenz]

I use the following:

For x as Integer = 0 to DataSet.Tables(0).Rows.Count - 1
   Messagebox.Show(Convert.ToString(DataSet.Tables(0).Rows(x).Items(0)))
Next x

That is a basic example from my memory, so it may require a little more work.