How to use the DELETE method in a DataSet

thomaz · June 11th, 2003, 07:26 AM

I want to use the code below to delete rows in a table using the DATASET in conjunction with a DataAdapter.

I do not know what this wrong one, the code compels error-free and when this is executed it not eliminating the lines of my table.

The code below:

thisAdapter = new OleDbDataAdapter ("SELECT * FROM lig_det ORDER BY registro", dbBanco.thisConnection);

thisBuilder = new OleDbCommandBuilder (thisAdapter);

thisDataSet = new DataSet ();

thisAdapter.Fill (thisDataSet, "lig_det");

//percorrendo toda a tabela LIG_DET para achar os registros que devem
//ser eliminados
for (int i = 0; i < thisDataSet.Tables ["lig_det"].Rows.Count; i++)
{
    if (registroSgc == thisDataSet.Tables ["lig_det"].Rows [i]["registro"].ToString ())
    {
    thisDataSet.Tables ["lig_det"].Rows [i].Delete ();

    MessageBox.Show ("Estado do registro na tabela " +thisDataSet.Tables ["lig_det"].Rows [i].RowState.ToString ());

    }//final do if
}//final do FOR

thisDataSet.Tables ["lig_det"].AcceptChanges ();

thisAdapter.Update (thisDataSet, "lig_det");

Ankur_Verma · June 13th, 2003, 07:19 AM

Hi Thomaz

I have noticed there hasnât been any reply posted
to this very elaborate query of yours. I didnât
try to temper with your code 'cause altering any
body else's code is something that I always avoid
but I wrote some myself and it is working.

Code:

string mySelectText = "SELECT * FROM lig_det ORDER BY registro";
string myConnString = "Provider=SQLOLEDB;Data Source=localhost;" +
    "Integrated Security=SSPI;Initial Catalog=tempdb";

OleDbConnection thisConn = new OleDbConnection(myConnString);
OleDbDataAdapter thisAdapter = new OleDbDataAdapter(mySelectText, thisConn);
OleDbCommandBuilder thisB = new OleDbCommandBuilder(thisAdapter);

DataSet thisDataSet = new DataSet ();

//thisDataSet.Tables.Add("lig_det");
try
{
    thisAdapter.Fill (thisDataSet,"lig_det");
}
catch(Exception ex)
{
    MessageBox.Show(ex.Message);
}

DataTable mtable = thisDataSet.Tables["lig_det"];

//percorrendo toda a tabela LIG_DET para achar os registros que devem 
//ser eliminados
string registroSgc = "234";
for (int i = 0; i < mtable.Rows.Count; i++)
{
    if (registroSgc == mtable.Rows[ i]["registro"].ToString ())
    {
        mtable.Rows [ i].Delete ();
        MessageBox.Show (
                  "Estado do registro na tabela " + 
                  mtable.Rows [ i].RowState.ToString());
    }//final do if 
}//final do FOR 


thisAdapter.Update(thisDataSet,"lig_det");
thisConn.Close();

this code some how required your table to have a primary
key. Iâm working on to get to the grips with the underlying
science behind this peculiar requirement.

Anyways, welcome to the forum and do write back if
there is anything else you wanna ask.

Quote:

quote:Originally posted by thomaz

I want to use the code below to delete rows in a table using the DATASET in conjunction with a DataAdapter.

I do not know what this wrong one, the code compels error-free and when this is executed it not eliminating the lines of my table.

The code below:

thisAdapter = new OleDbDataAdapter ("SELECT * FROM lig_det ORDER BY registro", dbBanco.thisConnection);

thisBuilder = new OleDbCommandBuilder (thisAdapter);

thisDataSet = new DataSet ();

thisAdapter.Fill (thisDataSet, "lig_det");

//percorrendo toda a tabela LIG_DET para achar os registros que devem
//ser eliminados
for (int i = 0; i < thisDataSet.Tables ["lig_det"].Rows.Count; i++)
{
    if (registroSgc == thisDataSet.Tables ["lig_det"].Rows [ i]["registro"].ToString ())
    {
    thisDataSet.Tables ["lig_det"].Rows [ i].Delete ();

    MessageBox.Show ("Estado do registro na tabela " +thisDataSet.Tables ["lig_det"].Rows [ i].RowState.ToString ());

    }//final do if
}//final do FOR

thisDataSet.Tables ["lig_det"].AcceptChanges ();

thisAdapter.Update (thisDataSet, "lig_det");

Ankur Verma
.Net and C++ Specialist
Wiley Tech Support

PeacefulPete · January 10th, 2004, 02:28 PM

The problem does not relate to a primary key but the order in which Update and AcceptChanges are called.
Reverse the last two lines of code to read:

thisAdapter.Update (thisDataSet, "lig_det");
thisDataSet.Tables ["lig_det"].AcceptChanges ();

When you call AcceptChanges on your DataTable object the DataRowState of the rows in your table are updated. All Added and Modified rows become Unchanged and Deleted rows are removed.
If you subsequently call the Update method on your DataAdapter object when it looks at the DataRowState of the rows in your "lig_det" table it will find that all the rows are marked as Unchanged and it will therefore believe that there is nothing to update:)

I believe that this should work