为什么我导入数据库的时候
为什么到如的时候差一条 每次都少导入一条不知道为什么
if (FileUpload1.HasFile)
{
string conpath = this.FileUpload1.PostedFile.FileName;
string type = System.IO.Path.GetExtension(conpath);
string filePath = Server.MapPath( "~/admin/upfiles/ ") + DateTime.Now.ToString( "yyyyMMddHHmmss ")+System.IO.Path.GetFileName(conpath) ;
// 假设 upload 为你上传的服务器目标的文件夹
FileUpload1.PostedFile.SaveAs(filePath);
string constr = "provider=microsoft.jet.oledb.4.0;data source= ' " + filePath + " ';Extended Properties=Excel 8.0;HDR=Yes;IMEX=1; ";
OleDbConnection conn = new OleDbConnection(constr);
#region try
try
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tname = dt.Rows[0][ "TABLE_NAME "].ToString();
string query = "SELECT * FROM [ " + tname + "] ";
OleDbCommand com = new OleDbCommand(query, conn);
OleDbDataReader dr = com.ExecuteReader();
int num = 0;
string stu_xh = string.Empty;
string stu_xm = string.Empty;
string stu_sex = string.Empty;
string stu_xb = string.Empty;
string stu_zy = string.Empty;
string stu_bj = string.Empty;
string stu_nj = string.Empty;
string stu_xl = string.Empty;
string stu_xz = string.Empty;
while (dr.Read())
{
stu_xh = dr[ "stu_xh "].ToString();
stu_xm = dr[ "stu_xm "].ToString();
stu_sex = dr[ "stu_sex "].ToString();
stu_xb = dr[ "stu_xb "].ToString();
stu_zy = dr[ "stu_zy "].ToString();
stu_bj = dr[ "stu_bj "].ToString();
stu_nj = dr[ "stu_nj "].ToString();
stu_xl = dr[ "stu_xl "].ToString();
stu_xz = dr[ "stu_xz "].ToString();
//调用存储过程进行插入数据
num = dbuser.drbm(stu_xh, stu_xm, stu_sex, stu_xb, stu_zy, stu_bj, stu_nj, stu_xl, stu_xz);
};
dr.Close();
if (num > 0)
{
MyDataGrid1.DataSource = dbuser.searbmzg();
MyDataGrid1.DataBind();
conn.Close();
dbuser.Alert( "成功导入数据 ");
}
else
{
dbuser.Alert( "导入失败,不是正确的数据导入格式,请检查数据! ");
conn.Close();
dr.Close();
return;
}
}
#endregion
catch (System.Data.SqlClient.SqlException exp)
{
dbuser.Alert( "错误!试图导入重复的数据,请确认数据是否已经导入! ");
conn.Close();
}
}
else
{
dbuser.Alert( "不是正确格式的Excel格式数据,请重试！ ");
return;
}
[解决办法]
string excelConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + Server.MapPath( "abc.xls ") + " ; Extended Properties=Excel 8.0; ";
OleDbConnection excelConn = new OleDbConnection(excelConnectionString);
excelConn.Open();
OleDbCommand excelCmd = new OleDbCommand( "SELECT * FROM [Sheet1$] ", excelConn);
string accessConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + Server.MapPath( "data.mdb ");
OleDbConnection accessConn = new OleDbConnection(accessConnectionString);
accessConn.Open();
OleDbTransaction trans = accessConn.BeginTransaction();
string sql = "INSERT INTO [temp] (ABC) VALUES ( '{0} ') ";
try
{
using (OleDbDataReader excelDr = excelCmd.ExecuteReader())
{
while (excelDr.Read())
{
OleDbCommand accessCmd = new OleDbCommand(string.Format(sql, System.Convert.ToString(excelDr[0])), accessConn);
accessCmd.Transaction = trans;
accessCmd.ExecuteNonQuery();
}
}
trans.Commit();
Response.Write( "OK ");
}
catch (Exception ex)
{
trans.Rollback();
Response.Write( "NO " + ex.Message);
}
finally
{
excelConn.Close();
accessConn.Close();
}
