Paradox Inserting data Operation must use an updateable query

Question

I am attempting to write to a Paradox database file using c# and oledb in Windows 8. I am able to drop the table, create the table and write the first line before I get the error "operation must use an updateable query".

I have gone through the following to try and work it out: 1) Running as administrator 2) Updating the permissions within the application to make sure I don't have readonly or archive properties on the file 3) Adding the running user permissions to read / write / modify directories where the db files sit 4) Changing the query multiple times in case I am doing something weird with the query

If I was unable to write or insert at all then the above steps would have made sense but the initial insert works and any additional inserts fail.

The code below shows the current way I am trying to perform the actions and I have left in the commented out sections so that you can see what else I have tried.

public void OverwriteData(string fileName, DataTable dataToWrite)
{
    //split up the filename
    string path = Path.GetDirectoryName(fileName);
    string file = Path.GetFileName(fileName);

    //create the string for creating the table
    string strTempCreate = "";
    //string strTempInsert = "";
    foreach (DataColumn column in dataToWrite.Columns)
    {
        if (strTempCreate != "")
        {
            strTempCreate = strTempCreate + ", ";
        }
        strTempCreate = strTempCreate + "[" + column.ColumnName + "]" + " char(30)";

        /*if (strTempInsert != "")
        {
            strTempInsert = strTempInsert + ", ";
        }
        strTempInsert = strTempInsert + column.ColumnName;*/
    }
    string createTableStr = "CREATE TABLE " + file + " (" + strTempCreate + ")";
    string dropTableStr = "DROP TABLE " + file;
    //build the sql insert command
    //string insertSql = "insert into " + file + " values ";
    /*foreach (DataRow row in dataToWrite.Rows)
    {
        insertSql = insertSql + row.Field<string>;
    }*/


    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @";Extended Properties=Paradox 5.x;";
    //DataTable results = new DataTable();
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbCommand dbCommand = new OleDbCommand();
        dbCommand.Connection = conn;
        dbCommand.CommandText = dropTableStr;
        try
        {
            dbCommand.ExecuteNonQuery();
        }
        catch { }

        dbCommand.CommandText = createTableStr;
        dbCommand.ExecuteNonQuery();

        //try to do the insert
        StringBuilder sb = new StringBuilder();

        //make sure that the database is not readonly
        FileAttributes attributes = File.GetAttributes(fileName);
        if ((attributes & FileAttributes.Archive) == FileAttributes.Archive)
        {
            attributes = attributes & ~FileAttributes.Archive;
            File.SetAttributes(fileName, attributes);
        }

        //then we want to try and connect to this database to put data into it
        string selectSQL = "Select * from " + file;
        using (var adapter = new OleDbDataAdapter(selectSQL, conn))
        {
            using (var builder = new OleDbCommandBuilder(adapter))
            {
                var destinationTable = new DataTable();
                adapter.Fill(destinationTable);
                destinationTable.Merge(dataToWrite,true,MissingSchemaAction.Ignore);
                destinationTable.AcceptChanges();
                foreach (DataRow row in destinationTable.Rows)
                {
                    row.SetAdded();
                }
                builder.QuotePrefix = "[";
                builder.QuoteSuffix = "]";
                builder.GetInsertCommand();
                adapter.Update(destinationTable);
            }
        }

       /*foreach (DataRow row in dataToWrite.Rows)
        {
            sb.Clear();
            sb.Append("insert into " + file + " values ('");
            IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
            sb.Append(string.Join("','", fields));
            sb.Append("')");
            dbCommand.CommandText = sb.ToString();
            dbCommand.ExecuteNonQuery();
        }*/

        /*sb.Clear();
        sb.Append("insert into " + file);
        foreach (DataRow row in dataToWrite.Rows)
        {
            sb.Append(" values ('");
            IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
            sb.Append(string.Join("','", fields));
            sb.Append("'),");
        }
        sb.Remove(sb.Length - 1, 1);
        sb.Append(";");
        dbCommand.CommandText = sb.ToString();
        dbCommand.ExecuteNonQuery();
        */

    }
}

Answer 1

This article solved my problem: https://msdn.microsoft.com/en-us/library/ms715421(v=vs.85).aspx

According to Microsoft: A table is not updatable by the Paradox driver under two conditions:

1. When a unique index is not defined on the table. This is not true for an empty table, which can be updated with a single row even if a unique index is not defined on the table. If a single row is inserted in an empty table that does not have a unique index, an application cannot create a unique index or insert additional data after the single row has been inserted.
2. If the Borland Database Engine is not implemented.