0

I try to use the code sample in DBDataAdapter.Update Method to clear a table in a database.

using (SqlConnection connection = new SqlConnection(connectionString))
{
     SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM WebCam", connection);
     DataTable table = new DataTable();
     adapter.Fill(table);
     table.PrimaryKey = new DataColumn[] { table.Columns["Date"] };
     //table.Rows[0]["Date"] = System.DateTime.Now; //It's OK to modify a row 
     table.Clear(); //But it is not working to clear the table
     SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
     adapter.Update(table);
}

I can add new rows or modify existing rows, and the changes can be committed to the database, but if I try to empty the table, the change to 'table' can not be committed to the database, also, no exception is thrown.

Do I miss something?

kennyzx
  • 12,845
  • 6
  • 39
  • 83
  • 1
    Even though no exceptions seem to be thrown, it is a good programming practice to always surround db operations with Try ... Catch. The Clear method will issue an exception for any errors that result from relationship violations. Otherwise the code looks correct. – ron tornambe Jan 05 '12 at 16:45
  • Turns out I can clear the table by calling DataRow.Delete() on each row. But still have no idea why DataTable.Clear() does not work. – kennyzx Jan 06 '12 at 01:59
  • The MSDN reference isn't terribly clear (no pun intended) about the Clear method. Here's a link that seems to make sense: http://www.canaware.com/default.aspx?g=posts&t=932 – ron tornambe Jan 06 '12 at 03:24
  • Found a similar question on http://social.msdn.microsoft.com/Forums/en-AU/vblanguage/thread/E232039B-7921-4C7A-8D77-CF3C36D61D30 which explains DataTable.Clear() does not mark the rows for delete, should call DataRow.Delete() - I chose to use DataTable.Clear() to avoid iterating through all the rows and Clear() sound like delete all the rows at one time. – kennyzx Jan 06 '12 at 06:11

1 Answers1

0

u should get error on this line:

table.PrimaryKey = new DataColumn[] { table.Columns["Date"] };

this would mean that u have catched the exception somewhere else, maybe from where the function is being called, and not showing the error in a messege box.

Another reason might be because you are using words like Table, adapter etc ... my guess is that you might be overloading some reserved words functionalities.

Naval
  • 344
  • 2
  • 7