OleDb update query only updating first records in MS Access table

[Adinb007]

Team,

I ran into a situation where only first record is getting updated in Ms access database table however there is no error thrown and affected row count is also correct.

public int TextToColumn(string DB, string table)
{
string _conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DB + ";Jet OLEDBatabase Password=";

OleDbConnection _connection = new OleDbConnection(_conn);
// OleDbConnection _connection2 = new OleDbConnection(_conn);
System.Data.DataTable DT = new System.Data.DataTable();

OleDbCommand _command = new OleDbCommand();

string _status = string.Empty;//arr[0];
string _Date = string.Empty;//arr[1];
string _comments = string.Empty;//arr[2];
string _processor = string.Empty;//arr[3];
int result = 0;

try
{
_command.CommandText = "SELECT [Document Number],[Text] From [" + table + "] WHERE [Text] Like '%-%'";
_command.Connection = _connection;
_connection.Open();

OleDbDataAdapter dtr = new OleDbDataAdapter(_command);
dtr.Fill(DT);
_connection.Close();
dtr.Dispose();

foreach (System.Data.DataRow dr in DT.Rows)
{
string value = dr["Text"].ToString();
string Doc = dr["Document Number"].ToString();

_status = "";
_comments = "";
_processor = "";
_Date = "";

string[] arr = value.ToString().Split('-');
try
{
_status = arr[0].Trim();
_Date = arr[1].Trim();
_comments = arr[2].Trim();
_processor = arr[3].Trim();
}
catch
{
}

_command.CommandText = @"UPDATE [" + table + "] SET [Status]=?,[Date_1]=?,[Comment]=?,[Processor]=? WHERE [Document Number]=?";
_command.Connection = _connection;
_command.Parameters.AddWithValue("[Status]", _status);
_command.Parameters.AddWithValue("[Date_1]", _Date);
_command.Parameters.AddWithValue("[Comment]", _comments);
_command.Parameters.AddWithValue("[Processor]", _processor);
_command.Parameters.AddWithValue("[Document Number]",Doc);

_connection.Open();
result += _command.ExecuteNonQuery();
_connection.Close();
}

return result;
}
catch (Exception ex)
{
return -1;
}
}

Continue reading...