Delete all access database table data
<p>I have an access database that I am manipulating with C#.</p>

<p>I have connected to it, retrieved a data-set from it and can add rows to a table. Now I am trying to clear a table and I am unable to get it to work.</p>

<p>I have tried <code>TRUNCATE TABLE table_name</code> but that throws an exception saying that I must use either <code>DELETE, INSERT, PROCEDURE, SELECT or UPDATE</code> and I have tried <code>Delete FROM table_name</code> However that throws an <code>DBConcurrenceyException</code>.</p>

<p>Here is what I have to tried to clear the table:</p>

<pre><code>private void ClearBut_Click(object sender, EventArgs e)
{
    OleDbDataAdapter dtaAdpTestTableClear = new OleDbDataAdapter();
    OleDbCommand command;

    command = new OleDbCommand("DELETE FROM TestTable", con);

    dtaAdpTestTableClear.DeleteCommand = command;

    foreach (DataRow row in dsWCSDHDB.Tables["TestTable"].Rows)
    {
        row.Delete();
    }

    dtaAdpTestTableClear.Update(dsWCSDHDB.Tables["TestTable"]);
}
</code></pre>

<p>My other add method</p>

<pre><code>private void Add_Click(object sender, EventArgs e)
{
    OleDbDataAdapter dtaAdpTestTableInsertNewRow = new OleDbDataAdapter();
    OleDbCommand command;

    // Create the InsertCommand.
    // This is needed as DataAdaptor.InsertCommand() is called during the update to insert the row into the database. It requires an insert query
    command = new OleDbCommand("INSERT INTO TestTable (id, someData) " +"VALUES (?, ?)", con); //We create a dbcommand the command is, Querytype, what we are doing with it, what table, (columns we are using), concat, Values we will be adding(as ? for now as we will pass this data in latter), connection to the database 

    command.Parameters.Add("id", OleDbType.Char, 5, "id"); //this is where we add a parameter to the command function. we add one per column in the row (columns we are using name, value type, column length, source column, these parameters will replace the ? in the query above 
    command.Parameters.Add("someData", OleDbType.VarChar, 40, "someData");

    dtaAdpTestTableInsertNewRow.InsertCommand = command;// we attach this command to the Insert command function of the adapter that we are using

    //Create the new row
    DataRow  row = dsWCSDHDB.Tables["TestTable"].NewRow(); //Create a new empty row that is formated for the TestTable table
    row["someData"] = AddValueTextBox.Text.ToString();// add in the values 

    //Add the new row to the dataset table
    dsWCSDHDB.Tables["TestTable"].Rows.Add(row); //adds this new row to the clients dataset

    //Updates the database table with the values of the clients dataset Table
    //For this to work you need to build a proper data adapter that is using a query taylered for the table you are using. 
    //Unfortunately although it would be nice to be able to add and use tables to the database with out changing the code you cant build a generic one that works for all tables in the database. 
    //this is because different tables can have different fields and column lengths .
    //there is a example of how to build one below 

    //Update the database table with the values of the clients dataset Table
    dtaAdpTestTableInsertNewRow.Update(dsWCSDHDB.Tables["TestTable"]);   // using the adapter that we created above we update the database with the clients dataset.
}
</code></pre>