OleDb Concurrency Errors in ADO.NET [HELP REQUIRED]
Microsoft
I am getting errors while performing delete or update operation through
OleDbDataAdapter using my custom delete or update command objects.
when i try to delete some records the error raises and says .
"OleDbCommand.Prepare method requires parameters of type '14' have an
explicitly set Precision and Scale"
Now i have no idea what DataAdapter is doing behind the scenese. I am also
facing another strange problem about Concurrency Issue when i try to
update or delete records from my database using my custom command objects
assigned to dataadapter.
The concurrency error says:
"Concurrency violation: The Delete Command effected 0 records"
Same is the case with my costome update command.
"Concurrency violation: The Update Command effected 0 records"
Please note that i am not testing my application under multiuser scnerio
where there are chances of real concurrency to occur.
I am using only one form to test the Application. I don't want to use
Commandbuilder to generate commands.
So here is the code i am using to do my job (2 days spent on looking at
various sites about the possible solution but in vain):
============================================================================
public void Save Changes(DataSet dsInquiry)
{
string sql_UpdateIQ = @"Update InquiryQuotes Set
HistoryID=?,NOP=?,AgeGroup=?,Fare=?,Tax=?,Other=?,Total=? Where QuoteID=?";
string sql_DeleteIQ = @"Delete From InquiryQuotes Where QuoteID = ?";
using(OleDbConnection Conn = new OleDbConnection(App.Data.ConnectionString))
{
// Here i have used my own OleDbHelper class to create command objects from
underlying tables. This is modified version of Microsoft Data Access
Application Block.
//It simply gets the schema of underlying table and creates appropriate
commands including required parameters.
OleDbCommand cmd_UpdateCommandIQ =
OleDbHelper.CreateTextCommand(Conn,sql_UpdateIQ,"Update:InquiryQuotes","Quot
eId","HistoryID","NOP","AgeGroup","Fare","Tax","Other","Total");
OleDbCommand cmd_DeleteCommandIQ =
OleDbHelper.CreateTextCommand(Conn,sql_DeleteIQ,"Delete:InquiryQuotes","Quot
eId");
cmd_UpdateCommandIQ.Parameters["QuoteId"].SourceVersion =
DataRowVersion.Original;
cmd_DeleteCommandIQ.Parameters["QuoteId"].SourceVersion =
DataRowVersion.Original;
/*
Update changes back to the database using.
I am performing each update operation saparately because the table has
relationships with other tables so have to take care of data integrity
manually"
*/
UpdateDatatable(cmd_DeleteCommandIQ,dsInquiry,"InquiryQuotes","Delete"); //
Here the error gets raised:
UpdateDatatableAuto(cmd_SelectCommandIQ,dsInquiry,"InquiryQuotes","Update");
dsInquiry.AcceptChanges();
}
}
private void UpdateDatatable(OleDbCommand desiredCommand, DataSet dataSet,
string tableName, string Action) {
if( desiredCommand == null ) throw new ArgumentNullException(
"desiredCommand" );
if( tableName == null || tableName.Length == 0 ) throw new
ArgumentNullException( "tableName" );
DataTable dtActionTable = null;
try {
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter()) {
if (Action.ToUpper().Equals("SELECT")){
dataAdapter.SelectCommand = desiredCommand;
dtActionTable =
dataSet.Tables[tableName].GetChanges(DataRowState.Unchanged);
}else if (Action.ToUpper().Equals("INSERT")){
dataAdapter.InsertCommand = desiredCommand;
dtActionTable =
dataSet.Tables[tableName].GetChanges(DataRowState.Added);
}else if (Action.ToUpper().Equals("UPDATE")){
dataAdapter.UpdateCommand = desiredCommand;
dtActionTable =
dataSet.Tables[tableName].GetChanges(DataRowState.Modified);
}else if (Action.ToUpper().Equals("DELETE")){
dataAdapter.DeleteCommand = desiredCommand;
dtActionTable =
dataSet.Tables[tableName].GetChanges(DataRowState.Deleted);
}
if (dtActionTable != null) {
dataAdapter.Update(dtActionTable);
dtActionTable.Dispose();
}
}catch{throw;}
}
============================================================================
===========
Regards,
Ahsan Umair.