CODEWITHSUNDEEP
c#c#-4.0ms-access
kks
kks

Reputation: 13

How do I insert a datatable into Microsoft Access?

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

Upvotes: 1

Views: 10528

Answers (1)

Derek
Derek

Reputation: 8630

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

Upvotes: 1

Related Questions