Use OleDbDataAdapter to insert into an Access db

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?

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

1. Make sure all required columns are included in the insert query.

2. If It doesn't work then create a new method for inserting and follow this:

   OleDbConnection conn = new OleDbConnection (connectionString);

   OleDbCommand command = new OleDbCommand();
   command.Connection = conn;
   command.CommandText= "INSERT INTO myTable (col1, col2) VALUES (@p_col1, @p_col2)";
   command.Parameters.Add ("@p_col1", OleDbType.String).Value = textBox1.Text;
   ...
   command.ExecuteNonQUery();