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

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

 
#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
            }

10 个解决方案

#1

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

#2

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

#3

引用 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


这是抛出的异常

#4

该回复于2016-08-06 08:46:26被管理员删除

#5

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

#6

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

#7

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

#8

引用 6 楼 insus 的回复:

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

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

#9

引用 7 楼 From_* 的回复:

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

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

#10

引用 5 楼 apollokk 的回复:

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

谢谢  我改好了  超长了