How do I structure an OleDbCommand Query so that I can take Tables from one .MDB, and replace them in another .MDB

Asked 6/2, 2009 at 14:38 Answered 6/2, 2009 at 16:37

Solved c#sql datatable dataset oledbcommand

I am trying to take tables from one Access Database File, add them to another Access Database file with the exact same structure but with different information. I need to overwrite any existing tables. I am almost done with my project this is last my brick wall.

I am using a separate class file named DatabaseHandling.cs to work with the Access Database files.

Here is my ENTIRE current DatabaseHandling.cs code. This is kept up to date for now on.

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace LCR_ShepherdStaffupdater_1._0
{
    public class DatabaseHandling
    {
        static DataTable datatableB = new DataTable();
        static DataTable datatableA = new DataTable();
        public static DataSet datasetA = new DataSet();
        public static DataSet datasetB = new DataSet();
        static OleDbDataAdapter adapterA = new OleDbDataAdapter();
        static OleDbDataAdapter adapterB = new OleDbDataAdapter();
        static string connectionstringA = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationA();
        static string connectionstringB = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationB();
        static OleDbConnection dataconnectionB = new OleDbConnection(connectionstringB);
        static OleDbConnection dataconnectionA = new OleDbConnection(connectionstringA);
        static DataTable tableListA;
        static DataTable tableListB;

        static public void addTableA(string table, bool addtoDataSet)
        {
            dataconnectionA.Open();
            datatableA = new DataTable(table);
            try
            {
                OleDbCommand commandselectA = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionA);
                adapterA.SelectCommand = commandselectA;
                adapterA.Fill(datatableA);
            }
            catch
            {
                Logging.updateLog("Error: Tried to get " + table + " from DataSetA. Table doesn't exist!", true, false, false);
            }

            if (addtoDataSet == true)
            {
                datasetA.Tables.Add(datatableA);
                Logging.updateLog("Added DataTableA: " + datatableA.TableName.ToString() + " Successfully!", false, false, false);
            }

            dataconnectionA.Close();
        }

        static public void addTableB(string table, bool addtoDataSet)
        {
            dataconnectionB.Open();
            datatableB = new DataTable(table);

            try
            {
                OleDbCommand commandselectB = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionB);
                adapterB.SelectCommand = commandselectB;
                adapterB.Fill(datatableB);
            }
            catch
            {
                Logging.updateLog("Error: Tried to get " + table + " from DataSetB. Table doesn't exist!", true, false, false);
            }



            if (addtoDataSet == true)
            {
                datasetB.Tables.Add(datatableB);
                Logging.updateLog("Added DataTableB: " + datatableB.TableName.ToString() + " Successfully!", false, false, false);
            }

            dataconnectionB.Close();
        }

        static public string[] getTablesA(string connectionString)
        {
            dataconnectionA.Open();
            tableListA = dataconnectionA.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
            string[] stringTableListA = new string[tableListA.Rows.Count];

            for (int i = 0; i < tableListA.Rows.Count; i++)
            {
                stringTableListA[i] = tableListA.Rows[i].ItemArray[2].ToString();
            }
            dataconnectionA.Close();
            return stringTableListA;
        }

        static public string[] getTablesB(string connectionString)
        {
            dataconnectionB.Open();
            tableListB = dataconnectionB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
            string[] stringTableListB = new string[tableListB.Rows.Count];

            for (int i = 0; i < tableListB.Rows.Count; i++)
            {
                stringTableListB[i] = tableListB.Rows[i].ItemArray[2].ToString();
            }
            dataconnectionB.Close();
            return stringTableListB;
        }

        static public void createDataSet()
        {

            string[] tempA = getTablesA(connectionstringA);
            string[] tempB = getTablesB(connectionstringB);
            int percentage = 0;
            int maximum = (tempA.Length + tempB.Length);

            Logging.updateNotice("Loading Tables...");
            Logging.updateLog("Started Loading File A", false, true, false);
            for (int i = 0; i < tempA.Length ; i++)
            {
                if (!datasetA.Tables.Contains(tempA[i]))
                {
                    addTableA(tempA[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
                else
                {
                    datasetA.Tables.Remove(tempA[i]);
                    addTableA(tempA[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
            }
            Logging.updateLog("Finished loading File A", false, true, false);
            Logging.updateLog("Started loading File B", false, true, false);
            for (int i = 0; i < tempB.Length ; i++)
            {
                if (!datasetB.Tables.Contains(tempB[i]))
                {
                    addTableB(tempB[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
                else
                {
                    datasetB.Tables.Remove(tempB[i]);
                    addTableB(tempB[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
            }
            Logging.updateLog("Finished loading File B", false, true, false);


        }

        static public DataTable getDataTableA()
        {
            datatableA = datasetA.Tables[Settings.textA];

            return datatableA;
        }
        static public DataTable getDataTableB()
        {
            datatableB = datasetB.Tables[Settings.textB];
            return datatableB;
        }

        static public DataSet getDataSetA()
        {
            return datasetA;
        }

        static public DataSet getDataSetB()
        {
            return datasetB;
        }

        static public void InitiateCopyProcessA()
        {
            DataSet tablesA;
            tablesA = DatabaseHandling.getDataSetA();

                foreach (DataTable table in tablesA.Tables)
                {
                    OverwriteTable(table, table.TableName);
                    Logging.updateLog("Copied " + table.TableName + " successfully.", false, true, false);
                }

        }

        static void OverwriteTable(DataTable sourceTable, string tableName)
        {
            using (var destConn = new OleDbConnection(connectionstringA))
            using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
            using (var destDA = new OleDbDataAdapter(destCmd))
            {
                // Since we're using a single table, we can have the CommandBuilder
                // generate the appropriate INSERT and DELETE SQL statements
                using (var destCmdB = new OleDbCommandBuilder(destDA))
                {
                    destCmdB.QuotePrefix = "["; // quote reserved column names
                    destCmdB.QuotePrefix = "]";
                    destDA.DeleteCommand = destCmdB.GetDeleteCommand();
                    destDA.InsertCommand = destCmdB.GetInsertCommand();

                    // Get rows from destination, and delete them
                    var destTable = new DataTable();
                    destDA.Fill(destTable);
                    foreach (DataRow dr in destTable.Rows)
                    {
                        dr.Delete();
                    }
                    destDA.Update(destTable);

                    // Set rows from source as Added, so the DataAdapter will insert them
                    foreach (DataRow dr in sourceTable.Rows)
                    {
                        dr.SetAdded();
                    }
                    destDA.Update(sourceTable);
                }
            }
        }



        }          
    }

I simply want to take a Datatable that is in memory and write it to a .MDB file. I have been attempting to do this for over 30 hours.

LATEST EDIT:

Okay, added new code. I get a new run-time error: Syntax error in FROM clause.

Code:

static public void InitiateCopyProcessA()
{
    DataSet tablesA;
    tablesA = DatabaseHandling.getDataSetA();

        foreach (DataTable table in tablesA.Tables)
        {
            OverwriteTable(table, table.TableName);
            Logging.updateLog("Copied " + table.TableName + " successfully.", false, true, false);
        }

}

static void OverwriteTable(DataTable sourceTable, string tableName)
{
    using (var destConn = new OleDbConnection(connectionstringA))
    using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
    using (var destDA = new OleDbDataAdapter(destCmd))
    {
        // Since we're using a single table, we can have the CommandBuilder
        // generate the appropriate INSERT and DELETE SQL statements
        using (var destCmdB = new OleDbCommandBuilder(destDA))
        {
            destCmdB.QuotePrefix = "["; // quote reserved column names
            destCmdB.QuotePrefix = "]";
            destDA.DeleteCommand = destCmdB.GetDeleteCommand();
            destDA.InsertCommand = destCmdB.GetInsertCommand();

            // Get rows from destination, and delete them
            var destTable = new DataTable();
            destDA.Fill(destTable);
            foreach (DataRow dr in destTable.Rows)
            {
                dr.Delete();
            }
            destDA.Update(destTable);

            // Set rows from source as Added, so the DataAdapter will insert them
            foreach (DataRow dr in sourceTable.Rows)
            {
                dr.SetAdded();
            }
            destDA.Update(sourceTable); // !!! Run-time error: Syntax error in FROM clause. !!!
        }
    }
}

Once again, it does not work. Let me know if you need additional information.

Thermolysis answered 6/2, 2009 at 14:38 Comment(3)

Your column names are probably reserved. Check my update to add quotes around the column names. And please...can we clean up the post to make it less a thread, and more the current question? I'm having trouble following this.... – Oni 6/2, 2009 at 21:43

Sorry bro, I didn't realize it was stacking up. I will keep it current. My deepest apologies... – Thermolysis 6/2, 2009 at 22:4

I assume the exception is throwing on the destDA.Update(destTable) line - is that right? Can you check what the value of destDA.DeleteCommand.CommandText is? I don't know Access SQL that well, but MSDN tells me it's pretty ANSI compliant. What version are you using? – Oni 6/2, 2009 at 23:29

Try replacing

using (var destCmdB = new OleDbCommandBuilder(destDA)) 
{            
    destDA.DeleteCommand = destCmdB.GetDeleteCommand();            
    destDA.InsertCommand = destCmdB.GetInsertCommand();        
}

with

destDA.InsertCommand = new OleDbCommand("INSERT INTO `AdminUsers` (`UserName`, `Password`) VALUES (?, ?)");
destDA.DeleteCommand = new OleDbCommand("DELETE FROM `AdminUsers` WHERE (`ID` = ?)");
destDA.UpdateCommand = new OldDbCommand("UPDATE `AdminUsers` SET `UserName` = ?, `Password` = ? WHERE (`ID` = ?)");

Where the queries are valid to your table structure.

Escalera answered 6/2, 2009 at 15:50 Comment(0)

I get the feeling you're not really grokking the whole DataTable/DataRow thing. You see, in a database, you don't really work with tables - but with rows. If you want to "overwrite" TableB with TableA's rows, you'd first delete all of the rows in TableB and then insert copies of all the rows from TableA.

Assuming the destination table already exists, you can do the insert by filling from 1 source, and then setting the rows to Added. The DataAdapter will then run a SQL insert command for each added row.

static void CopyTable(string sourceConnectionString, string destinationConnectionString, string tableName) {
    // Get rows from source
    var sourceTable = new DataTable();
    using (var sourceConn = new OleDbConnection(sourceConnectionString))
    using (var sourceCmd = new OleDbCommand(tableName, sourceConn) { CommandType = CommandType.TableDirect })
    using (var sourceDA = new OleDbDataAdapter(sourceCmd)) {
        sourceDA.Fill(sourceTable);
    }

    OverwriteTable(sourceTable, destinationConnectionString, tableName);
}

static void OverwriteTable(DataTable sourceTable, string destinationConnectionString, string tableName) {
    using (var destConn = new OleDbConnection(destinationConnectionString))
    using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
    using (var destDA = new OleDbDataAdapter(destCmd)) {
        // Since we're using a single table, we can have the CommandBuilder
        // generate the appropriate INSERT and DELETE SQL statements
        using (var destCmdB = new OleDbCommandBuilder(destDA)) {
            destCmdB.QuotePrefix = "["; // quote reserved column names
            destCmdB.QuoteSuffix = "]";
            destDA.DeleteCommand = destCmdB.GetDeleteCommand();
            destDA.InsertCommand = destCmdB.GetInsertCommand();

            // Get rows from destination, and delete them
            var destTable = new DataTable();
            destDA.Fill(destTable);
            foreach (DataRow dr in destTable.Rows) {
                dr.Delete();
            }
            destDA.Update(destTable);

            // Set rows from source as Added, so the DataAdapter will insert them
            foreach (DataRow dr in sourceTable.Rows) {
               dr.SetAdded(); 
            }
            destDA.Update(sourceTable);
        }
    }    
}

EDIT: Split the OverwriteTable to a different method to accomodate your in memory data table. Also added quotes around generated Sql statements for your reserved Year and Month column names. Moved dispose of CommandBuilder as fixed by bendewey.

Oni answered 6/2, 2009 at 15:16 Comment(6)

Thank you for your response. I did NOT know that I had to do it row by row. I copied and pasted your code and ran it to see if it worked and I got a run-time error: "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows." What am I doing wrong? – Thermolysis 6/2, 2009 at 15:32

I need to know how to take a DataTable I already have in memory and THEN overwrite the Access Database file table. Not take a table from directly from a .MDB file and adding it directly to another .MDB file. Do you see what i'm saying? How do I do that? – Thermolysis 6/2, 2009 at 15:35

Just don't do the fetch from source...I split the code to 2 methods for you. – Oni 6/2, 2009 at 21:42

Thanks -- ill see if it works. If it does, my god I wont know how to thank you or the other guy helping me. Quick question...the Compiler can't find this destDA.QuotePrefix or destDA.QuoteSuffix. Are you sure that is what you mean, cause that isn't working? – Thermolysis 6/2, 2009 at 21:54

Nope...I misttyped (and didn't test). It's supposed to be destCmdB.Quote*. Fixed. – Oni 6/2, 2009 at 21:55

Hey, check my post. I cleaned it up and added a new EDIT to it. Im getting a run-time error with your code – Thermolysis 6/2, 2009 at 22:5

Try replacing

using (var destCmdB = new OleDbCommandBuilder(destDA)) 
{            
    destDA.DeleteCommand = destCmdB.GetDeleteCommand();            
    destDA.InsertCommand = destCmdB.GetInsertCommand();        
}

with

destDA.InsertCommand = new OleDbCommand("INSERT INTO `AdminUsers` (`UserName`, `Password`) VALUES (?, ?)");
destDA.DeleteCommand = new OleDbCommand("DELETE FROM `AdminUsers` WHERE (`ID` = ?)");
destDA.UpdateCommand = new OldDbCommand("UPDATE `AdminUsers` SET `UserName` = ?, `Password` = ? WHERE (`ID` = ?)");

Where the queries are valid to your table structure.

Escalera answered 6/2, 2009 at 15:50 Comment(0)

@Mark Brackett had it really close the reason your getting the no DeleteCommand is because the OleDbCommandBuilder is disposing so move that bracket and you should be good.

static void CopyTable(string sourceConnectionString, string destinationConnectionString, string tableName)
{
// Get rows from source    
var sourceTable = new DataTable();
using (var sourceConn = new OleDbConnection(sourceConnectionString))
using (var sourceCmd = new OleDbCommand(tableName, sourceConn) {CommandType = CommandType.TableDirect})
using (var sourceDA = new OleDbDataAdapter(sourceCmd))
{
    sourceDA.Fill(sourceTable);
}
using (var destConn = new OleDbConnection(destinationConnectionString))
using (var destCmd = new OleDbCommand(tableName, destConn) {CommandType = CommandType.TableDirect})
using (var destDA = new OleDbDataAdapter(destCmd))
{
    // Since we're using a single table, we can have the CommandBuilder        
    // generate the appropriate INSERT and DELETE SQL statements        
    using (var destCmdB = new OleDbCommandBuilder(destDA))
    {
        destDA.DeleteCommand = destCmdB.GetDeleteCommand();
        destDA.InsertCommand = destCmdB.GetInsertCommand();

        // Get rows from destination, and delete them        
        var destTable = new DataTable();
        destDA.Fill(destTable);
        foreach (DataRow dr in destTable.Rows)
        {
            dr.Delete();
        }
        destDA.Update(destTable);
        // Set rows from source as Added, so the DataAdapter will insert them        
        foreach (DataRow dr in sourceTable.Rows)
        {
            dr.SetAdded();
        }
        destDA.Update(sourceTable);
    }
}

Update

Try this exception code

static public void InitiateCopyProcessA()
{
    DataSet tablesA;
    tablesA = DatabaseHandling.getDataSetA();
    int i = 0;
    string tableName = "";
    try
    {
        foreach (DataTable table in tablesA.Tables)
        {
            tableName = table.TableName;  // for debugging the exception
            CopyTable(connectionstringA, connectionstringB, table.TableName);
        }
    }
    catch(Exception ex)
    {
        throw new Exception("Error updating " + tableName, ex);
    }
}

update

try changing

// Set rows from source as Added, so the DataAdapter will insert them                
foreach (DataRow dr in sourceTable.Rows)        
{            
    dr.SetAdded();        
}

// only add the first row.
sourceTable.Rows[0].SetAdded()

I'm tempted to know if its just one row that;s throwing the bug or if its the query. My thought is that one of the rows has a funky value

Escalera answered 6/2, 2009 at 16:37 Comment(16)

Tried that, almost worked then I get a run-time error: Syntax error in INSERT INTO statement. – Thermolysis 6/2, 2009 at 16:46

How many rows are you testing? try testing only 1 row. Also set a break point and hover over the destDA.InsertCommand and see what its CommandText is. post that in your question. – Escalera 6/2, 2009 at 17:15

Okay I did what you said. I wasn't to sure about what you meant on testing only one row but I think I did what you asked. Let me know what you think. And thank you again for this I am so very appreciative. – Thermolysis 6/2, 2009 at 17:47

You're going to have to put some sort of logic in there to determine which table is crapping out on you. See my updated sample – Escalera 6/2, 2009 at 17:54

GREAT Idea. The Table that was crapping out on me was " Error updating AccountBudget" What the heck is wrong with AccountBudget??? – Thermolysis 6/2, 2009 at 17:58

Do you have any ideas on what to do about it? – Thermolysis 6/2, 2009 at 17:59

Whats you inner exception say? does it still say syntax error? – Escalera 6/2, 2009 at 18:30

Hey, sorry went to eat. Yes, my inner exception still says " {"Syntax error in INSERT INTO statement."} " – Thermolysis 6/2, 2009 at 19:45

can you update your post with the insertcommand for the AccountBudget table – Escalera 6/2, 2009 at 19:58

Happy to! Its updated. I thank you again for this, from the bottom of my heart lol – Thermolysis 6/2, 2009 at 20:8

I am comparing each INSERT INTO statement. They are doing the same thing. Listening the names, and for the values how many value columns there are. Account is successfully copied, but then when it gets to AccountBudget it gives me an error. TOTALLY puzzling. – Thermolysis 6/2, 2009 at 20:14

I've been researching it. I have no clue what the problem is. Want to start a new question on just this specific issue? – Thermolysis 6/2, 2009 at 21:21

This guy below me posted code, and says it is a fix. Im just about to test it. – Thermolysis 6/2, 2009 at 21:48

That shouldn't be too hard to fix right? I tried that guys code below, didn't work either. – Thermolysis 6/2, 2009 at 22:8

I think once again, this has kinda went away from the topic. I am thinking about asking a question about why I am getting errors for my SQL queries. This question kinda died and I don't blame you if you are losing interest. – Thermolysis 6/2, 2009 at 22:28

Im going to start another question specifically on my new problem – Thermolysis 6/2, 2009 at 22:48

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Update

update

Recommended topics

Hot tags