Sequential read and update of table

[harmikm]

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

[cloud255]

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

[harmikm]

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

[cloud255]

Hmmm, I would approach this slightly differently.

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

private void btnResetAll_Click(object sender, System.EventArgs e)
{
string selectQuery = "SELECT * FROM ScheduleR";
var sqlCommand = new OleDbCommand(selectQuery, MyConn);
OleDbDataReader myReader;
myReader = sqlCommand.ExecuteReader(); //this will get all the rows
 
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);
  Console.WriteLine(schLine.ToString() + schSeg + schDir.ToString());
  readCount += 1
}
  myReader.Close();
  MyConn.Close();
 
  ProcessData(); //only called once
 
  listBoxOpsLog.Items.Add ("Records Read =" + readCount.ToString());
  istBoxOpsLog.Items.Add ("Records Updated =" + updateCount.ToString());
}
 
 

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

private void ProcessData()
{
  //There is no need to check if the schAssignment != 0 as you are setting all rows schAssignment value to 0;
  myDA = new OleDbDataAdapter();
  updateCommand = new OleDbCommand();
  updateCommand.CommandText = "UPDATE ScheduleR SET Assignment=0";
 
  MyConn.Open();
 
  updateCommand.Connection = MyConn;
  myDA.UpdateCommand = updateCommand;
  updateCount = myDA.UpdateCommand.ExecuteNonQuery();
}

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

[harmikm]

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

[cloud255]

@harmikm
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.