Use OleDbDataAdapter to insert into an Access db

Question

I would like to insert some data into an Access Database.

DataTable dt = new DataTable();
String sql = string.Format("SELECT * FROM {0} where 1=0; ", tmap.SqlTableName);
string con = string.Format(conn, accessPath);
OleDbDataAdapter da = new OleDbDataAdapter(sql, con);
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);
da.InsertCommand = cmdBuilder.GetInsertCommand(true); // Returns "INSERT INTO test (int, bdate, amt, text, bit) VALUES (?, ?, ?, ?, ?)"
da.Fill(dt);
//Add data to the DateTable
for (int i = 0; i < rowCount; i++)
{
DataRow dr = dt.NewRow();
//....
dt.Rows.Add(dr);
}
da.Update(dt); //This is where things go south.

System.Data.OleDb.OleDbException
Message: Syntax error in INSERT INTO statement.
Source: Microsoft JET Database Engine.

If I change the insert command:

da.InsertCommand = new OleDbCommand("INSERT INTO test ([text]) VALUES (?)");  

and change the incoming data to only have a single text value I get:

No value given for one or more required parameters.

Am I missing something?

Answer 1

The issue was in the data types. The code in the question works if the data types are compatible.