locked
Issue in truncating access database RRS feed

  • Question

  • User275991403 posted

    Hi,


           I am developing one application in windows C#.NET.The application contain one database with more number of records.Each time i run the application it takes backup of current database.and remove all values after backup.This is the process.


    How to  use truncate query after taking backup?


    If i  use truncate query i got the following error:

    System.Data.OleDb.OleDbException: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at contentbackup.Form1.btnBackUp_Click(Object sender, EventArgs e) in D:\contentbackup\contentbackup\Form1.cs:line


    My code is here:

    string sourcePath = txtFileName.Text;
                    string Apppath = Application.StartupPath;
                    files = Directory.GetFiles(Apppath + "/backup/");
                    numFiles = files.Length;
                    int flname = numFiles + 1;
                    string DestinationPath = Apppath + "/backup/" + "Backup_" + flname.ToString() + ".mdb";
                    System.IO.File.Copy(sourcePath, DestinationPath);
                    con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileName.Text);
                    con.Open();
                    for (int i = 0; i < listBox1.Items.Count; i++)
                    {
                        string str = listBox1.Items[i].ToString();
                        string query = "truncate table tblAdminlogin";
                        cmd = new OleDbCommand("query", con);
                        cmd.ExecuteNonQuery();
                    }


    Anyone can tell me solution for this error.I am waiting for your reply.Advance thanks for reply.


    -Saravanan

    Monday, May 17, 2010 7:13 AM

Answers

  • User-837620913 posted

    Access doesn't have a "TRUNCATE TABLE" statement.  You just use a delete statement without a where clause.  After doing that, you might want to reset the autonumber to 1.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 17, 2010 7:57 AM

All replies

  • User-837620913 posted

    Access doesn't have a "TRUNCATE TABLE" statement.  You just use a delete statement without a where clause.  After doing that, you might want to reset the autonumber to 1.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 17, 2010 7:57 AM
  • User1591219901 posted

    Go through this link below for more information about Truncate & Delete:

    http://msnettech.blogspot.com/p/sql.html

    Wednesday, November 3, 2010 2:55 AM