使用c#插入数据表以访问数据库 [英] Insert a Data Table to Access Database using c#
问题描述
我已经尝试了一些代码来在 Access
数据库中插入一个 DataTable
.代码如下:
I have tried few codes from my end to insert a DataTable
inside a Access
db.
Below is the code:
public void WriteToAccess(DataTable dt)
{
string strDSN = "DSN=MYDSN";
string cmdText="Insert into AccessTable (ColumnA,ColumnB,ColumnC,ColumnD,ColumnE,ColumnF,ColumnG,ColumnH) Values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8)";
using (OdbcConnection cn = new OdbcConnection(strDSN))
{
using (OdbcCommand cmd = new OdbcCommand(cmdText, cn))
{
cn.Open();
foreach (DataRow r in dt.Rows)
{
cmd.Parameters.AddWithValue("@p1", r["ColumnA"].ToString());
cmd.Parameters.AddWithValue("@p2", r["ColumnB"].ToString());
cmd.Parameters.AddWithValue("@p3", r["ColumnC"].ToString());
cmd.Parameters.AddWithValue("@p4", r["ColumnD"].ToString());
cmd.Parameters.AddWithValue("@p5", r["ColumnE"].ToString());
cmd.Parameters.AddWithValue("@p6", r["ColumnF"].ToString());
cmd.Parameters.AddWithValue("@p7", r["ColumnG"].ToString());
cmd.Parameters.AddWithValue("@p8", r["ColumnH"].ToString());
cmd.ExecuteNonQuery();//Exception at this line
}
}
}
要插入的 DataTable
有 8 列,我创建的 Access
中的表也有 8 列.当我执行上面这段代码时,我遇到了一个异常.它说:
The DataTable
to be inserted has 8 columns, the table in Access
which i have created also has 8 columns.
When I execute the above piece of code, I encounter an exception. It says that:
ERROR [07002ױ] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8.
我提供了 8 个参数,但它抛出了错误.
I'm providing 8 params, yet it throws the error.
谁能告诉我我做错了什么?
Can anybody let me know what I am doing wrong?
推荐答案
您当前的代码至少有两个问题:
There are at least two issues with your current code:
[1] 使用 System.Data.Odbc
和 Access ODBC 驱动程序的参数化查询必须对所有参数占位符使用问号 (?
).它无法识别 @p1
@p2
, ... 作为 CommandText 中的参数,因此您会收到参数太少"错误.你需要使用
[1] Parameterized queries using System.Data.Odbc
with the Access ODBC driver must use the question mark (?
) for all parameter placeholders. It is not recognizing @p1
@p2
, ... as parameters in the CommandText so you are getting the "Too few parameters" error. You need to use
string cmdText="Insert into AccessTable (ColumnA,ColumnB,ColumnC,ColumnD,ColumnE,ColumnF,ColumnG,ColumnH)"
+ " Values (?,?,?,?,?,?,?,?)";
[2] 如果您打算在循环内使用 Parameters.AddWithValue()
,您需要在添加参数值之前执行 Parameters.Clear()
.(没有它,您将不断添加新的参数值 - 一次 8 个 - 而不是替换现有的.)
[2] If you are going to use Parameters.AddWithValue()
inside the loop you need to do a Parameters.Clear()
before adding the parameter values. (Without it, you will keep adding new parameter values - 8 at a time - instead of replacing the existing ones.)
foreach (DataRow r in dt.Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("?", r["ColumnA"].ToString());
cmd.Parameters.AddWithValue("?", r["ColumnB"].ToString());
// and so on
这篇关于使用c#插入数据表以访问数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!