How do I insert a datatable into Microsoft Access?

Question

I have a datatable with data information and I would like to put it in a table in microsoft access with insert query. Anyone can help me do that?

foreach ( DataRow row in table.Rows)
{
    insertCommand.CommandText = "INSERT INTO [tableName] VALUES (row[a], row[b], row[c])";
    insertCommand.ExecuteNonQuery();
}

Look at MSDN - http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.insertcommand.aspx — Derek, Feb 25 '13 at 09:03
For the row[a], row[b], and row[c] it will be different data each time it loop. How to I ensure that I will insert all different data into the table? — kks, Feb 25 '13 at 09:06
It didnt work. By the way, I found the solution already. Thanks for providing the suggestion. — kks, Feb 26 '13 at 01:29
@kks, As it stands, this question doesn't look like it will be helpful to anyone; it may be deleted for that reason. However, if you answer your own question, showing your solution, then it may be helpful to someone else and might be kept. — Wayne Conrad, Feb 26 '13 at 14:35

score 1 · Answer 1 · edited Sep 08 '18 at 20:18

You can use a command builder. A command builder will determine commands for you.

Your Data Adapter requires all of the different types of commands available to call on as and when required. By providing the command builder with the SELECT command, it will determine the UPDATE, DELETE & INSERT command for you.

All you need to do, it pass the DataTable object to the Adapter when you attempt to UPDATE your database.

using (OleDbConnection con = new OleDbConnection("YourConnectionString"))
{
    var adapter = new OleDbDataAdapter();
    adapter.SelectCommand = new OleDbCommand("SELECT * FROM [YourAccessTable]", con);

    var cbr = new OleDbCommandBuilder(adapter);

    cbr.GetDeleteCommand();
    cbr.GetInsertCommand();
    cbr.GetUpdateCommand();

    try
    {
        con.Open();
        adapter.Update(YourDataTable);
    }
    catch (OleDbException ex)
    {
        MessageBox.Show(ex.Message, "OledbException Error");
    }
    catch (Exception x)
    {
        MessageBox.Show(x.Message, "Exception Error");
    }
}

How do I insert a datatable into Microsoft Access?

1 Answers1

Linked