ASP.net操作Excel导入到SQL Server出错，求解答！！！

[qq_35633825]

以下为粘出的源代码，数据连接没问题，提示导入不成功，请重试，就是最后那抛出的异常，不知道是不是因为列数太多的原因，列数较少时可以更新成功，全导进去就抛出异常，求大神指导！！


#region --------读取文件内容到服务器内存----------
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("file/") + filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
//要保证字段名和excel表中的字段名相同
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close();
#endregion
#region --------插入到数据库中---------
SqlConnection thisconnection1 = new SqlConnection("server=127.0.0.1;database=ZGXX;uid=sa;pwd=103505");
thisconnection1.Open();
int count1 = ds.Tables["[Sheet1$]"].Rows.Count;
for (int i = 0; i < count1; i++)
{
//string stuName = "", majorID = "", cardID = "";
//stuName = ds.Tables["[Sheet1$]"].Rows[i][0].ToString();
//majorID = ds.Tables["[Sheet1$]"].Rows[i][1].ToString();
//cardID = ds.Tables["[Sheet1$]"].Rows[i][2].ToString();
//string excelsql = "insert into Fee_stu(stuName,majorID,cardID) values ('" + stuName + "','" + majorID + "','" + cardID + "') ";
string cYGBH = "", cXM = "", cXMJM = "", cXingBie = "", cMinZu = "", dChuShengRQ = "", cJiGuan = "", cShenFenID = "", cJiaTingZZ = "", cLianXiDH = "", cWenHuaCD = "", nWenHuaCDID = "", cBiYeYX = "", dBiYeRQ = "", cZhuanYe = "", nXueZhi = "", dCanJiaGZRQ = "", cZhengZhiMM = "", dRuDangTRQ = "", cZhiWu = "", cXingZhengJB = "", nXingZhengJBID = "", cBuMen = "", dRenZhiRQ = "", cZhiCheng = "", dJinZhiRQ = "", cZhiChengJB = "", nZhiChengJBID = "", cGanBuSF = "", nDangAnGZ = "", cIsGuDong = "", nGuJin = "", cIsGanBu = "", cIsLinShiGong = "", cIsJingShang = "", cDengHao = "", cZiJiuQH = "", cIsHunGang = "", nJingXiaDG = "", cBZ = "", yijidanwei = "", gongzhong = "", gangwei = "", cSheHuiBZHM = "", gonghao = "", yinhang = "", zhanghao = "";
int j = 0;
cYGBH = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cXM = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cXMJM = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cXingBie = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cMinZu = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
dChuShengRQ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cJiGuan = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cShenFenID = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cJiaTingZZ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cLianXiDH = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cWenHuaCD = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nWenHuaCDID = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cBiYeYX = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
dBiYeRQ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cZhuanYe = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nXueZhi = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
dCanJiaGZRQ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cZhengZhiMM = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
dRuDangTRQ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cZhiWu = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cXingZhengJB = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nXingZhengJBID = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cBuMen = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
dRenZhiRQ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cZhiCheng = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
dJinZhiRQ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cZhiChengJB = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nZhiChengJBID = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cGanBuSF = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nDangAnGZ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cIsGuDong = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nGuJin = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cIsGanBu = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cIsLinShiGong = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cIsJingShang = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cDengHao = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cZiJiuQH = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cIsHunGang = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
nJingXiaDG = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cBZ = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
yijidanwei = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
gongzhong = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
gangwei = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
cSheHuiBZHM = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
gonghao = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
yinhang = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
zhanghao = ds.Tables["[Sheet1$]"].Rows[i][j++].ToString().Trim();
string excelsql = "insert into YGXXB(cYGBH,cXM,cXMJM,cXingBie,cMinZu,dChuShengRQ,cJiGuan,cShenFenID,cJiaTingZZ,cLianXiDH,cWenHuaCD,nWenHuaCDID,cBiYeYX,dBiYeRQ,cZhuanYe,nXueZhi,dCanJiaGZRQ,cZhengZhiMM,dRuDangTRQ,cZhiWu,cXingZhengJB,nXingZhengJBID,cBuMen,dRenZhiRQ,cZhiCheng,dJinZhiRQ,cZhiChengJB,nZhiChengJBID,cGanBuSF,nDangAnGZ,cIsGuDong,nGuJin,cIsGanBu,cIsLinShiGong,cIsJingShang,cDengHao,cZiJiuQH,cIsHunGang,nJingXiaDG,cBZ,yijidanwei,gongzhong,gangwei,cSheHuiBZHM,gonghao,yinhang,zhanghao) values ('" + cYGBH + "','" + cXM + "','" + cXMJM + "','" + cXingBie + "','" + cMinZu + "','" + dChuShengRQ + "','" + cJiGuan + "','" + cShenFenID + "','" + cJiaTingZZ + "','" + cLianXiDH + "','" + cWenHuaCD + "','" + nWenHuaCDID + "','" + cBiYeYX + "','" + dBiYeRQ + "','" + cZhuanYe + "','" + nXueZhi + "','" + dCanJiaGZRQ + "','" + cZhengZhiMM + "','" + dRuDangTRQ + "','" + cZhiWu + "','" + cXingZhengJB + "','" + nXingZhengJBID + "','" + cBuMen + "','" + dRenZhiRQ + "','" + cZhiCheng + "','" + dJinZhiRQ + "','" + cZhiChengJB + "','" + nZhiChengJBID + "','" + cGanBuSF + "','" + nDangAnGZ + "','" + cIsGuDong + "','" + nGuJin + "','" + cIsGanBu + "','" + cIsLinShiGong + "','" + cIsJingShang + "','" + cDengHao + "','" + cZiJiuQH + "','" + cIsHunGang + "','" + nJingXiaDG + "','" + cBZ + "','" + yijidanwei + "','" + gongzhong + "','" + gangwei + "','" + cSheHuiBZHM + "','" + gonghao + "','" + yinhang + "','" + zhanghao + "') ";
SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
try
{
mycommand1.ExecuteNonQuery();
}
catch
{
Response.Write("<b>导入不成功，请重试！</b>");
return;
}
}
Response.Write("更新成功");
thisconnection1.Close();
#endregion
}

[qq_35633825]

小白求指导，SQL部分语句都没有问题，就是列数多的话就打印不出来

[Hello World,]

你catch获取错误消息看看是什么？

[qq_35633825]

引用 2 楼 apollokk 的回复:

你catch获取错误消息看看是什么？

System.Data.SqlClient.SqlException: 将截断字符串或二进制数据。 语句已终止。 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 在 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) 在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) 在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 在 LZ_import_man.btnUpload_Click(Object sender, EventArgs e) 位置 e:\煤矿\煤矿系统\ZGXXGL\LZ\import_man.aspx.cs:行号 113 这是抛出的异常

[Hello World,]

在113行打个断点，看看出错时的数据是什么？

[insus]

各个字段类型是否正确，长度是否足够存储数据。 当设计过小，数据被截除时，它直接提示与直接失败！！！

[秋的红果实]

这么多数据，我建议逐条导入 从excel读取一条，然后马上写入sql表；让两个连接（一个连接excel，一个sql）同时工作，虽然慢点，但能保证完成任务

[qq_35633825]

引用 6 楼 insus 的回复:

各个字段类型是否正确，长度是否足够存储数据。 当设计过小，数据被截除时，它直接提示与直接失败！！！

谢谢，发问题那天我就改完了，确实是有一条超长了

[qq_35633825]

引用 7 楼 From_TaiWan 的回复:

这么多数据，我建议逐条导入 从excel读取一条，然后马上写入sql表；让两个连接（一个连接excel，一个sql）同时工作，虽然慢点，但能保证完成任务

谢谢 我改好了 有一条超长了

[qq_35633825]

引用 5 楼 apollokk 的回复:

在113行打个断点，看看出错时的数据是什么？

谢谢 我改好了 超长了