What's causing ADO error: "Concurrency violation: the DeleteCommand affected 0 records"

[sherifffruitfly]

Hi,

I'm deleting a bunch of records from my DB, and they actually DO get
deleted (as I verify by looking afterwards). But then I get the
exception in the subject line. Isn't it weird that all of the correct
records would get deleted, and only *after* that, an error gets thrown?

Here's the code I'm using - thanks for any ideas!

And yes, I know the method used to avoid the concurrency exception is
ghetto in the extreme. That's why I'm asking for help

cdj

try
{
string oracleTable = "myTable";
string oracleSelectString = "select *
from " + oracleTable + "
where as_of_date between '" + oracleStart + "' and '" + oracleEnd + "'"
;
OracleDataAdapter daAdapter = new
OracleDataAdapter(oracleSelectString, dc_UAT);
OracleCommandBuilder cbCommandBldr =
new
OracleCommandBuilder(daAdapter);

OracleCommand oc = new OracleCommand();
oc.CommandText = "delete from " +
oracleTable + " where as_of_date
between '" + oracleStart + "' and '" + oracleEnd + "'" ;
oc.Connection = dc_UAT;
daAdapter.DeleteCommand = oc;

DataSet data = new DataSet();
data.Tables.Add("tbl");

daAdapter.Fill(data, "tbl");
int numRows =
data.Tables["tbl"].Rows.Count;

//Clear the rows
foreach (DataRow dr in
data.Tables["tbl"].Rows)
{
dr.Delete();
}

//Send back to db
daAdapter.Update(data, "tbl");
MessageBox.Show(numRows.ToString() + "
records successfully
deleted.", "Rates Aggregator");
}
catch (Exception ex)
{
if (ex.Message.StartsWith("Conc"))
{
MessageBox.Show("Records
successfully deleted.", "Rates
Aggregator");
}
else
{
MessageBox.Show("Error deleting
from Rates_test:\n" + ex.Message,
"Delete Error");
}
}

 

[William \(Bill\) Vaughn]

If you're depending on the DataAdapter.Delete method it expects a return
count from the operation to indicate one and only one row was deleted. You
either have to ignore the error or handle the delete operation via a
ExecuteNonQuery Command...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Guest]

cdj,

Is there a reason why you don't just execute the delete command against the
database instead of retrieving the rows into a datatable, etc?

For example:

OracleCommand oc = new OracleCommand();
oc.CommandText = "delete from " +
oracleTable + " where as_of_date
between '" + oracleStart + "' and '" + oracleEnd + "'" ;
oc.Connection = dc_UAT;

int rowsAffected;
rowsAffected = oc.ExecuteNonQuery;

MessageBox.Show(rowsAffected.ToString() + "
records successfully deleted.", "Rates Aggregator");

Kerry Moorman

 

[sherifffruitfly]

cdj,

Is there a reason why you don't just execute the delete command against the
database instead of retrieving the rows into a datatable, etc?

For example:

(excelllent example snipped)

Yup. Ignorance. I only knew how to delete anything from a
dataset/table, and then call dataadapter.update(). Thanks for the
nonquery call example!

cdj