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();
}

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
– kks, Commented Feb 25, 2013 at 9:06
It didnt work. By the way, I found the solution already. Thanks for providing the suggestion. — kks
– kks, Commented Feb 26, 2013 at 1: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
– Wayne Conrad, Commented Feb 26, 2013 at 14:35

Parrish Husband · Accepted Answer · 2018-09-08 20:18:06Z

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

Collectives™ on Stack Overflow

How do I insert a datatable into Microsoft Access?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related