Sequential read and update of table

Using OleDb, is it possible to sequentially read a (Access) table and update some records in it?

The read (ExecuteReader) works fine, but when I attempt to update, I get a message telling me I have to close the reader connection first before updating, which defeats the purpose of the reader.

The update examples in the documentation use select then update. In my application the records must be read in chronological order.

Thanks

re: Sequential read and update of table

Could you post the problematic code for us to have a look at?

re: Sequential read and update of table

private void btnResetAll_Click(object sender, System.EventArgs e)
{
selectQuery = "SELECT ";
selectQuery += "* ";
selectQuery += "FROM ";
selectQuery += "ScheduleR";
sqlCommand = new OleDbCommand(selectQuery, MyConn);
OleDbDataReader myReader;
myReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow );
readCount = 0;
updateCount = 0;
tableName = "ScheduleR";
while (myReader.Read())
{
schSeq = myReader.GetInt16(0);
schLine = myReader.GetInt16(1);
schSeg = myReader.GetString(2);
schDir = myReader.GetInt16(3);
schRun = myReader.GetInt16(4);
schCar = myReader.GetString(5);
schStnStart = myReader.GetString(6);
schTimeStart = myReader.GetDateTime(7);
schTimeEnd = myReader.GetDateTime(8);
schStnEnd = myReader.GetString(9);
schAssignment = myReader.GetInt16(10);
schPart = myReader.GetString(11);
schService = myReader.GetString(12);
schTrip = myReader.GetInt16(13);
ProcessData();
Console.WriteLine(schLine.ToString() + schSeg + schDir.ToString());
readCount += 1;
}
listBoxOpsLog.Items.Add ("Records Read =" + readCount.ToString());
listBoxOpsLog.Items.Add ("Records Updated =" + updateCount.ToString());
myReader.Close();
MyConn.Close();
}

private void ProcessData()
{
if (schAssignment != 0)
{
myDA = new OleDbDataAdapter();
updateCommand = new OleDbCommand();
updateCommand.CommandText = "UPDATE ScheduleR SET Assignment=0";
updateCommand.Connection = MyConn;
myDA.UpdateCommand = updateCommand;
myDA.UpdateCommand.ExecuteNonQuery();
updateCount += 1;
}
}


The objective to to sequentially read the table and set the field "Assignment" to zero.
The error happens on ExecuteNonQuery();
The text of the error is:
"There is already an open DataReader associated with this Connection which must be closed first."

I am new to C# and OleDb.

Thanks

re: Sequential read and update of table

Hmmm, I would approach this slightly differently.

You can simply select all the rows from the appropriate table:

You can update all the values with one SQL command since you are setting everything that is not 0 equal to 0.

PS Please use code tags (# symbol in toolbar) when posting code, this makes code easier to read.

re: Sequential read and update of table

This program is merely a shell for the final version, just to see if it is possible to sequentially read a table and update some of the records.

In the final version, complex logic must be used to check dates, times, and references to data in other tables in order to determine which records to update, and the update will be setting the assignment number to a non-zero number.

The logic will be more complex than can be handled by a SELECT.

The table must be read in sequential order since the data refers to events in chronological order.

Thanks

re: Sequential read and update of table

Uh, ok. Is it not possible for you to use an 'order by' statement in the query to get the data in a specific order?

If you are going to be doing data inspection and manipulation I suggest you define a data structure (a List<> of some sort of custom object for example) to hold the data in memory. It will be easier to manipulate this structure and save the results back to the database again instead of doing it one record at a time.

So basically: Load everything you will need into memory using a select statement, update data which must be changed and finally persist those changes back to the database.