CODEWITHSUNDEEP
c#databasems-access
skyzzle
skyzzle

Reputation: 187

Delete all access database table data

I have an access database that I am manipulating with C#.

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.

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

Here is what I have to tried to clear the table:

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

My other add method

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

Upvotes: 0

Views: 5156

Answers (1)

Mostafiz
Mostafiz

Reputation: 7352

You will just need to call ExecuteNonQuery

private void ClearBut_Click(object sender, EventArgs e)
{
    string comand = "DELETE FROM TestTable";
    OleDbCommand cmd = new OleDbCommand(comand, con);
    cmd.ExecuteNonQuery();
 }

Upvotes: 3

Related Questions