使用 OleDbDataAdapter 插入 Access 数据库

发布于 2024-08-13 15:46:35 字数 944 浏览 9 评论 0原文

我想将一些数据插入到 Access 数据库中。

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
消息：INSERT INTO 语句中存在语法错误。
来源：Microsoft JET 数据库引擎。

如果我更改插入命令：

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

并将传入数据更改为只有一个文本值，我会得到：

没有为一个或多个必需参数给出值。

我错过了什么吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海拔太高太耀眼 2024-08-20 15:46:35

确保所有必需的列都包含在插入查询中。

如果它不起作用，则创建一个新的插入方法并按照以下步骤操作：

OleDbConnection conn = new OleDbConnection(connectionString);

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

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

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

回复收藏 0 原文