DBConcurrency Exception when updating Access Database

S

Sameer Motwani

Hello,
I am reading data from an Access 97 database into a DataSet modifying it and
commiting the change back to the Database.
But when I call the Update method on the DataAdapter it throws a
DBConcurrency Excpetion. The table I am updating has
a Autonumber field which is also the Primary Key of the Table.
Here are methods of the my DataAccess Access Class to Read and Update Data

// For Reading data into DataSet
public DataSet GetAllReplica()
{
// DBProvider is an Abstract Factory that returns the required Databases
objects based on a config file
using(IDbConnection dbConn = DBProvider.CreateConnection())
{
String cmdText = String.Empty;
IDbDataAdapter da = null;
try
{
IDbCommand cmd = DBProvider.CreateCommand();
cmd.Connection = dbConn;

cmd.CommandType = CommandType.Text;
cmdText = "SELECT * FROM Replica ORDER BY KEY";

cmd.CommandText = cmdText;
da = DBProvider.CreateDataAdapter();
da.SelectCommand = cmd;
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;


DataSet ds = new DataSet();


da.Fill(ds);

// Setting Primary Datafield properties
ds.Tables["Table"].Columns["KEY"].AutoIncrementSeed = -1;
ds.Tables["Table"].Columns["KEY"].AutoIncrementStep = -1;

return ds;
}
catch(Exception e)
{
if (dbConn.State.Equals(ConnectionState.Open) ||
dbConn.State.Equals(ConnectionState.Broken))
{
dbConn.Close();
}
String errMsg = "Database Error: In Obtaining All Replica Records";
throw e;
}
}
}

// For Updating DataSet
public bool UpdateReplica(DataSet replicaSet)
{
bool isReplicaUpdated = false;

// DBProvider is an Abstract Factory that returns the required Databases
objects based on a config file
using(IDbConnection dbConn = DBProvider.CreateConnection())
{
String cmdText = String.Empty;
IDbDataAdapter da = null;
try
{

if (!replicaSet.HasChanges()) return isReplicaUpdated;

DataSet updatedSet = replicaSet.GetChanges(DataRowState.Modified);


if (updatedSet == null) return isReplicaUpdated;

IDbCommand cmd = DBProvider.CreateCommand();
cmd.Connection = dbConn;

cmd.CommandType = CommandType.Text;
cmdText = "UPDATE REPLICA SET
[DATABASEPATH]=@DataBasePath,[REPLICATED]=@Replicated,[CREATIONDATE]=@Creati
onDate,[LASTSYNDATE]=@LastSynDate,[FILTER]=@Filter,[PARTIALLYREPLICATED]=@Pa
rtiallyReplicated WHERE [KEY]=@Key";


cmd.CommandText = cmdText;
da = DBProvider.CreateDataAdapter();
da.UpdateCommand = cmd;

IDataParameterCollection paramCollection = da.UpdateCommand.Parameters;
IDbDataParameter param;

param = DBProvider.CreateParameter("@Key",DbType.Int32,"KEY");
param.Direction = ParameterDirection.Input;
param.SourceVersion = DataRowVersion.Original;
paramCollection.Add(param);

param =
DBProvider.CreateParameter("@DataBasePath",DbType.String,255,"DATABASEPATH")
;
param.Direction = ParameterDirection.Input;
paramCollection.Add(param);

param =
DBProvider.CreateParameter("@Replicated",DbType.Boolean,"REPLICATED");
param.Direction = ParameterDirection.Input;
paramCollection.Add(param);

param =
DBProvider.CreateParameter("@CreationDate",DbType.DateTime,"CREATIONDATE");
param.Direction = ParameterDirection.Input;
paramCollection.Add(param);

param =
DBProvider.CreateParameter("@LastSynDate",DbType.DateTime,"LASTSYNDATE");
param.Direction = ParameterDirection.Input;
paramCollection.Add(param);

param = DBProvider.CreateParameter("@Filter",DbType.String,255,"FILTER");
param.Direction = ParameterDirection.Input;
paramCollection.Add(param);

param =
DBProvider.CreateParameter("@PartiallyReplicated",DbType.Boolean,"PARTIALLYR
EPLICATED");
param.Direction = ParameterDirection.Input;
paramCollection.Add(param);

EventInfo evt = da.GetType().GetEvent("RowUpdated");
Delegate updateHandler =
Delegate.CreateDelegate(evt.EventHandlerType,this,"OnRowUpdated");
evt.AddEventHandler(da,updateHandler);

if (da.Update(updatedSet) > 0)
{
isReplicaUpdated = true;
}
else
{
isReplicaUpdated = false;
}


replicaSet.Merge(updatedSet);

return isReplicaUpdated;
}
catch(Exception e)
{
if (dbConn.State.Equals(ConnectionState.Open) ||
dbConn.State.Equals(ConnectionState.Broken))
{
dbConn.Close();
}

throw e;
}

Please let me know if I am doing something wrong.
Any help would be appreciated.

Sameer
 
K

Kevin Yu [MSFT]

Hi Sammer,

Based on MSDN document, DBConcurrencyException is thrown by the DataAdapter
during the update operation if the number of rows affected equals zero. So
I think there might be something wrong with the DataAdapter's
UpdateCommand. If the UpdateCommand affects 0 records, the exception is
thrown. Please try to check the WHERE clause to see if there are records
which meet the condition. You can try to use SELECT statement with the same
WHERE clause to find out this.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
S

Sameer Motwani

Hello,
I found the problem it was the sequence of the parameters in my SQL
statement. I think, that the order in which you add the parameters to the
command object must be the same for the parameters in your SQL statement in
the code.

Thanks
Sameer
 
Top