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

1 Nov 2006 11:06 PM

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");
                                }
                        }

2 Nov 2006 12:16 AM

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

Show quote

"sherifffruitfly" <sherifffruit***@gmail.com> wrote in message
news:1162422403.239985.172850@b28g2000cwb.googlegroups.com...
> 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");
>                                }
>                        }
>

2 Nov 2006 12:17 AM

Kerry Moorman

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

Show quote

"sherifffruitfly" wrote:

> 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");
>                                 }
>                         }
>
>

2 Nov 2006 12:39 AM

sherifffruitfly

Kerry Moorman wrote:

> 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