我想把Excel表中的数据导入到Access数据库中.Excel表有4个sheet,Access中对应4个数据表,用相同的代码前三个表我已经成功导入,可是最后一个表一直提示:"insert into 语句的语法错误".我用断点调试查看insert 语句语法根本没有错误啊,真是奇怪,附部分代码如下,请各位大侠帮忙想想办法啊!
try
{
OleDbConnection cnn_excel = new OleDbConnection();
cnn_excel = conn_excel(dbpath); //excel的conn
OleDbConnection cnn_access = new OleDbConnection();
cnn_access = conn_access(dbname); //access的conn
string strsql_excel = "";
string strsql_access = "";
DataTable dt = new DataTable();
strsql_excel = "select * from [" + tablename + "$]";
strsql_access = "select * from " + tablename;
OleDbDataAdapter da_excel = new OleDbDataAdapter(strsql_excel, cnn_excel); //建立excel的dataset数据集
DataSet ds_excel = new DataSet();
da_excel.Fill(ds_excel);
dt = ds_excel.Tables[0];
OleDbDataAdapter da_access = new OleDbDataAdapter(strsql_access, cnn_access);//建立access的dataset数据集
DataSet ds_access = new DataSet();
da_access.Fill(ds_access);
DataTable dtaccess = new DataTable();
dtaccess = dt.Copy();
dtaccess.TableName = tablename;
ds_access.Tables.Add(dtaccess);
for (int i = 0; i < ds_access.Tables[tablename].Rows.Count; i++)
{
string strinsertaccess1 = "";
string strinsertaccess2 = "";
string strinsertaccess = "";
switch (tablename)
{
case "table1":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
case "table2":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
case "table3":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
case "table4":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
}
for (int j = 0; j < ds_access.Tables[tablename].Columns.Count; j++)
{
if (j == 0)
{
strinsertaccess2 += "'" + ds_access.Tables[tablename].Rows[i][j] + "'";
}
else
{
strinsertaccess2 += ",'" + ds_access.Tables[tablename].Rows[i][j] + "'";
}
}
strinsertaccess= strinsertaccess1+strinsertaccess2 + ")";
OleDbCommand cmd_exceltoaccess = new OleDbCommand(strinsertaccess, cnn_access);
cmd_exceltoaccess.CommandText = strinsertaccess;
cmd_exceltoaccess.ExecuteNonQuery();
cnn_excel.Close();
cnn_access.Close();
}
MessageBox.Show("添加数据表"+tablename+"成功!");
}
catch (Exception ee)
{
MessageBox.Show(ee);
}
try
{
OleDbConnection cnn_excel = new OleDbConnection();
cnn_excel = conn_excel(dbpath); //excel的conn
OleDbConnection cnn_access = new OleDbConnection();
cnn_access = conn_access(dbname); //access的conn
string strsql_excel = "";
string strsql_access = "";
DataTable dt = new DataTable();
strsql_excel = "select * from [" + tablename + "$]";
strsql_access = "select * from " + tablename;
OleDbDataAdapter da_excel = new OleDbDataAdapter(strsql_excel, cnn_excel); //建立excel的dataset数据集
DataSet ds_excel = new DataSet();
da_excel.Fill(ds_excel);
dt = ds_excel.Tables[0];
OleDbDataAdapter da_access = new OleDbDataAdapter(strsql_access, cnn_access);//建立access的dataset数据集
DataSet ds_access = new DataSet();
da_access.Fill(ds_access);
DataTable dtaccess = new DataTable();
dtaccess = dt.Copy();
dtaccess.TableName = tablename;
ds_access.Tables.Add(dtaccess);
for (int i = 0; i < ds_access.Tables[tablename].Rows.Count; i++)
{
string strinsertaccess1 = "";
string strinsertaccess2 = "";
string strinsertaccess = "";
switch (tablename)
{
case "table1":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
case "table2":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
case "table3":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
case "table4":
strinsertaccess1 = "insert into " + tablename + "(字段1,..) values(";
break;
}
for (int j = 0; j < ds_access.Tables[tablename].Columns.Count; j++)
{
if (j == 0)
{
strinsertaccess2 += "'" + ds_access.Tables[tablename].Rows[i][j] + "'";
}
else
{
strinsertaccess2 += ",'" + ds_access.Tables[tablename].Rows[i][j] + "'";
}
}
strinsertaccess= strinsertaccess1+strinsertaccess2 + ")";
OleDbCommand cmd_exceltoaccess = new OleDbCommand(strinsertaccess, cnn_access);
cmd_exceltoaccess.CommandText = strinsertaccess;
cmd_exceltoaccess.ExecuteNonQuery();
cnn_excel.Close();
cnn_access.Close();
}
MessageBox.Show("添加数据表"+tablename+"成功!");
}
catch (Exception ee)
{
MessageBox.Show(ee);
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货