我这个是先重Excel中导入DataGridView中 然后判断数据的类型是否有问题
最后是将DataGridView中的数据 添加到数据库里面
我导入1到99条都没有问题 当导入100条或者以上的时候就会报错
具体报错原因不明 那位高人来指点下SqlConnection conn = help.getConnection();
SqlTransaction myTrans;
//= conn.BeginTransaction();
if (conn.State != ConnectionState.Open)
conn.Open();
myTrans = conn.BeginTransaction();
int iRows = dgExcel1.Rows.Count;
int iCol = dgExcel1.Columns.Count; try
{
for (int m = 0; m < iRows; m++)
{
#region 赋值
string cInvCode = dgExcel1[0, m].Value.ToString();
string cInvAddCode = dgExcel1[1, m].Value.ToString();
string cInvName = dgExcel1[2, m].Value.ToString();
string cInvStd = dgExcel1[3, m].Value.ToString();
string cInvCCode = dgExcel1[4, m].Value.ToString(); string bSale = dgExcel1[5, m].Value.ToString();
string bPurchase = dgExcel1[6, m].Value.ToString();
string bSelf = dgExcel1[7, m].Value.ToString();
string bComsume = dgExcel1[8, m].Value.ToString();
string iTaxRate = dgExcel1[9, m].Value.ToString();
string cGroupCode = dgExcel1[10, m].Value.ToString();
string iGroupType = CheckType(cGroupCode);
string cComUnitCode = dgExcel1[11, m].Value.ToString();
string cAssComUnitCode = dgExcel1[12, m].Value.ToString(); string cInvDefine1 = dgExcel1[13, m].Value.ToString();
string cInvDefine2 = dgExcel1[14, m].Value.ToString();
string cInvDefine3 = dgExcel1[15, m].Value.ToString();
string cInvDefine4 = dgExcel1[16, m].Value.ToString();
string cInvDefine5 = dgExcel1[17, m].Value.ToString();
string cInvDefine6 = dgExcel1[18, m].Value.ToString();
string cInvDefine7 = dgExcel1[19, m].Value.ToString();
string cInvDefine8 = dgExcel1[20, m].Value.ToString();
string cInvDefine9 = dgExcel1[21, m].Value.ToString();
string cInvDefine10 = dgExcel1[22, m].Value.ToString();
string cInvDefine11 = dgExcel1[23, m].Value.ToString();
string cInvDefine12 = dgExcel1[24, m].Value.ToString();
string cInvDefine13 = dgExcel1[25, m].Value.ToString();
string cInvDefine14 = dgExcel1[26, m].Value.ToString(); string cInvDefine15 = "";
if (dgExcel1[27, m].Value.ToString() != "")
{
DateTime cInvDefineDate15 = DateTime.Parse(dgExcel1[27, m].Value.ToString());
cInvDefine15 = cInvDefineDate15.ToShortDateString().ToString();
}
string cInvDefine16 = "";
if (dgExcel1[28, m].Value.ToString() != "")
{
DateTime cInvDefineDate16 = DateTime.Parse(dgExcel1[28, m].Value.ToString());
cInvDefine16 = cInvDefineDate16.ToShortDateString().ToString();
} string iInvSPrice = dgExcel1[29, m].Value.ToString();
string iInvSCost = dgExcel1[30, m].Value.ToString(); string iSafeNum = dgExcel1[31, m].Value.ToString();
string iTopSum = dgExcel1[32, m].Value.ToString();
string iLowSum = dgExcel1[33, m].Value.ToString(); DateTime SDate = DateTime.Parse(dgExcel1[34, m].Value.ToString());
string dSDate = SDate.ToShortDateString().ToString();
//dModifyDate
string cCreatePerson = dgExcel1[35, m].Value.ToString();
//cModifyPerson
string cBarCode = dgExcel1[36, m].Value.ToString();
#endregion string InsertSql = "insert into Inventory(cInvCode,cInvAddCode,cInvName,cInvStd,cInvCCode,bSale,bPurchase,bSelf,bComsume,iTaxRate," +
"cGroupCode ,iGroupType,cComUnitCode ,cAssComUnitCode,cSAComUnitCode,cPUComUnitCode,cSTComUnitCode,cCAComUnitCode,cInvDefine1," +
"cInvDefine2,cInvDefine3,cInvDefine4 ,cInvDefine5,cInvDefine6,cInvDefine7 ,cInvDefine8, cInvDefine9,cInvDefine10 ,cInvDefine11," +
"cInvDefine12,cInvDefine13,cInvDefine14,cInvDefine15,cInvDefine16,iInvSPrice,iInvSCost,iSafeNum,iTopSum,iLowSum,dSDate,dModifyDate," +
"cCreatePerson,cModifyPerson,cBarCode) values('" + cInvCode + "','" + cInvAddCode + "','" + cInvName + "','" + cInvStd + "','" +
cInvCCode + "','" + bSale + "','" + bPurchase + "','" + bSelf + "','" + bComsume + "','" + iTaxRate + "','" + cGroupCode + "','" +
iGroupType + "','" + cComUnitCode + "','" + cAssComUnitCode + "','" + cAssComUnitCode + "','" + cAssComUnitCode + "','" +
cAssComUnitCode + "','" + cAssComUnitCode + "','" + cInvDefine1 + "','" + cInvDefine2 + "','" + cInvDefine3 + "','" + cInvDefine4 +
"','" + cInvDefine5 + "','" + cInvDefine6 + "','" + cInvDefine7 + "','" + cInvDefine8 + "','" + cInvDefine9 + "','" + cInvDefine10 +
"','" + cInvDefine11 + "','" + cInvDefine12 + "','" + cInvDefine13 + "','" + cInvDefine14 + "','" + cInvDefine15 + "','" +
cInvDefine16 + "','" + iInvSPrice + "','" + iInvSCost + "','" + iSafeNum + "','" + iTopSum + "','" + iLowSum + "','" + dSDate +
"','" + dSDate + "','demo','demo" + "','" + cBarCode + "')"; SqlCommand com = new SqlCommand(InsertSql, conn);
com.Transaction = myTrans;
int num = com.ExecuteNonQuery(); BarInfo(iRows, m, "导入数据");
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
return false;
}
conn.Close();
return true;
最后是将DataGridView中的数据 添加到数据库里面
我导入1到99条都没有问题 当导入100条或者以上的时候就会报错
具体报错原因不明 那位高人来指点下SqlConnection conn = help.getConnection();
SqlTransaction myTrans;
//= conn.BeginTransaction();
if (conn.State != ConnectionState.Open)
conn.Open();
myTrans = conn.BeginTransaction();
int iRows = dgExcel1.Rows.Count;
int iCol = dgExcel1.Columns.Count; try
{
for (int m = 0; m < iRows; m++)
{
#region 赋值
string cInvCode = dgExcel1[0, m].Value.ToString();
string cInvAddCode = dgExcel1[1, m].Value.ToString();
string cInvName = dgExcel1[2, m].Value.ToString();
string cInvStd = dgExcel1[3, m].Value.ToString();
string cInvCCode = dgExcel1[4, m].Value.ToString(); string bSale = dgExcel1[5, m].Value.ToString();
string bPurchase = dgExcel1[6, m].Value.ToString();
string bSelf = dgExcel1[7, m].Value.ToString();
string bComsume = dgExcel1[8, m].Value.ToString();
string iTaxRate = dgExcel1[9, m].Value.ToString();
string cGroupCode = dgExcel1[10, m].Value.ToString();
string iGroupType = CheckType(cGroupCode);
string cComUnitCode = dgExcel1[11, m].Value.ToString();
string cAssComUnitCode = dgExcel1[12, m].Value.ToString(); string cInvDefine1 = dgExcel1[13, m].Value.ToString();
string cInvDefine2 = dgExcel1[14, m].Value.ToString();
string cInvDefine3 = dgExcel1[15, m].Value.ToString();
string cInvDefine4 = dgExcel1[16, m].Value.ToString();
string cInvDefine5 = dgExcel1[17, m].Value.ToString();
string cInvDefine6 = dgExcel1[18, m].Value.ToString();
string cInvDefine7 = dgExcel1[19, m].Value.ToString();
string cInvDefine8 = dgExcel1[20, m].Value.ToString();
string cInvDefine9 = dgExcel1[21, m].Value.ToString();
string cInvDefine10 = dgExcel1[22, m].Value.ToString();
string cInvDefine11 = dgExcel1[23, m].Value.ToString();
string cInvDefine12 = dgExcel1[24, m].Value.ToString();
string cInvDefine13 = dgExcel1[25, m].Value.ToString();
string cInvDefine14 = dgExcel1[26, m].Value.ToString(); string cInvDefine15 = "";
if (dgExcel1[27, m].Value.ToString() != "")
{
DateTime cInvDefineDate15 = DateTime.Parse(dgExcel1[27, m].Value.ToString());
cInvDefine15 = cInvDefineDate15.ToShortDateString().ToString();
}
string cInvDefine16 = "";
if (dgExcel1[28, m].Value.ToString() != "")
{
DateTime cInvDefineDate16 = DateTime.Parse(dgExcel1[28, m].Value.ToString());
cInvDefine16 = cInvDefineDate16.ToShortDateString().ToString();
} string iInvSPrice = dgExcel1[29, m].Value.ToString();
string iInvSCost = dgExcel1[30, m].Value.ToString(); string iSafeNum = dgExcel1[31, m].Value.ToString();
string iTopSum = dgExcel1[32, m].Value.ToString();
string iLowSum = dgExcel1[33, m].Value.ToString(); DateTime SDate = DateTime.Parse(dgExcel1[34, m].Value.ToString());
string dSDate = SDate.ToShortDateString().ToString();
//dModifyDate
string cCreatePerson = dgExcel1[35, m].Value.ToString();
//cModifyPerson
string cBarCode = dgExcel1[36, m].Value.ToString();
#endregion string InsertSql = "insert into Inventory(cInvCode,cInvAddCode,cInvName,cInvStd,cInvCCode,bSale,bPurchase,bSelf,bComsume,iTaxRate," +
"cGroupCode ,iGroupType,cComUnitCode ,cAssComUnitCode,cSAComUnitCode,cPUComUnitCode,cSTComUnitCode,cCAComUnitCode,cInvDefine1," +
"cInvDefine2,cInvDefine3,cInvDefine4 ,cInvDefine5,cInvDefine6,cInvDefine7 ,cInvDefine8, cInvDefine9,cInvDefine10 ,cInvDefine11," +
"cInvDefine12,cInvDefine13,cInvDefine14,cInvDefine15,cInvDefine16,iInvSPrice,iInvSCost,iSafeNum,iTopSum,iLowSum,dSDate,dModifyDate," +
"cCreatePerson,cModifyPerson,cBarCode) values('" + cInvCode + "','" + cInvAddCode + "','" + cInvName + "','" + cInvStd + "','" +
cInvCCode + "','" + bSale + "','" + bPurchase + "','" + bSelf + "','" + bComsume + "','" + iTaxRate + "','" + cGroupCode + "','" +
iGroupType + "','" + cComUnitCode + "','" + cAssComUnitCode + "','" + cAssComUnitCode + "','" + cAssComUnitCode + "','" +
cAssComUnitCode + "','" + cAssComUnitCode + "','" + cInvDefine1 + "','" + cInvDefine2 + "','" + cInvDefine3 + "','" + cInvDefine4 +
"','" + cInvDefine5 + "','" + cInvDefine6 + "','" + cInvDefine7 + "','" + cInvDefine8 + "','" + cInvDefine9 + "','" + cInvDefine10 +
"','" + cInvDefine11 + "','" + cInvDefine12 + "','" + cInvDefine13 + "','" + cInvDefine14 + "','" + cInvDefine15 + "','" +
cInvDefine16 + "','" + iInvSPrice + "','" + iInvSCost + "','" + iSafeNum + "','" + iTopSum + "','" + iLowSum + "','" + dSDate +
"','" + dSDate + "','demo','demo" + "','" + cBarCode + "')"; SqlCommand com = new SqlCommand(InsertSql, conn);
com.Transaction = myTrans;
int num = com.ExecuteNonQuery(); BarInfo(iRows, m, "导入数据");
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
return false;
}
conn.Close();
return true;
SqlConnection conn = help.getConnection();
SqlTransaction myTrans;
//= conn.BeginTransaction();
if (conn.State != ConnectionState.Open)
conn.Open();
myTrans = conn.BeginTransaction();
int iRows = dgExcel1.Rows.Count;
int iCol = dgExcel1.Columns.Count; try
{
for (int m = 0; m < iRows; m++)
{
#region 赋值
string cInvCode = dgExcel1[0, m].Value.ToString();
string cInvAddCode = dgExcel1[1, m].Value.ToString();
string cInvName = dgExcel1[2, m].Value.ToString();
string cInvStd = dgExcel1[3, m].Value.ToString();
string cInvCCode = dgExcel1[4, m].Value.ToString(); string bSale = dgExcel1[5, m].Value.ToString();
string bPurchase = dgExcel1[6, m].Value.ToString();
string bSelf = dgExcel1[7, m].Value.ToString();
string bComsume = dgExcel1[8, m].Value.ToString();
string iTaxRate = dgExcel1[9, m].Value.ToString();
string cGroupCode = dgExcel1[10, m].Value.ToString();
string iGroupType = CheckType(cGroupCode);
string cComUnitCode = dgExcel1[11, m].Value.ToString();
string cAssComUnitCode = dgExcel1[12, m].Value.ToString(); string cInvDefine1 = dgExcel1[13, m].Value.ToString();
string cInvDefine2 = dgExcel1[14, m].Value.ToString();
string cInvDefine3 = dgExcel1[15, m].Value.ToString();
string cInvDefine4 = dgExcel1[16, m].Value.ToString();
string cInvDefine5 = dgExcel1[17, m].Value.ToString();
string cInvDefine6 = dgExcel1[18, m].Value.ToString();
string cInvDefine7 = dgExcel1[19, m].Value.ToString();
string cInvDefine8 = dgExcel1[20, m].Value.ToString();
string cInvDefine9 = dgExcel1[21, m].Value.ToString();
string cInvDefine10 = dgExcel1[22, m].Value.ToString();
string cInvDefine11 = dgExcel1[23, m].Value.ToString();
string cInvDefine12 = dgExcel1[24, m].Value.ToString();
string cInvDefine13 = dgExcel1[25, m].Value.ToString();
string cInvDefine14 = dgExcel1[26, m].Value.ToString(); string cInvDefine15 = "";
if (dgExcel1[27, m].Value.ToString() != "")
{
DateTime cInvDefineDate15 = DateTime.Parse(dgExcel1[27, m].Value.ToString());
cInvDefine15 = cInvDefineDate15.ToShortDateString().ToString();
}
string cInvDefine16 = "";
if (dgExcel1[28, m].Value.ToString() != "")
{
DateTime cInvDefineDate16 = DateTime.Parse(dgExcel1[28, m].Value.ToString());
cInvDefine16 = cInvDefineDate16.ToShortDateString().ToString();
} string iInvSPrice = dgExcel1[29, m].Value.ToString();
string iInvSCost = dgExcel1[30, m].Value.ToString(); string iSafeNum = dgExcel1[31, m].Value.ToString();
string iTopSum = dgExcel1[32, m].Value.ToString();
string iLowSum = dgExcel1[33, m].Value.ToString(); DateTime SDate = DateTime.Parse(dgExcel1[34, m].Value.ToString());
string dSDate = SDate.ToShortDateString().ToString();
//dModifyDate
string cCreatePerson = dgExcel1[35, m].Value.ToString();
//cModifyPerson
string cBarCode = dgExcel1[36, m].Value.ToString();
#endregion string InsertSql = "insert into Inventory(cInvCode,cInvAddCode,cInvName,cInvStd,cInvCCode,bSale,bPurchase,bSelf,bComsume,iTaxRate," +
"cGroupCode ,iGroupType,cComUnitCode ,cAssComUnitCode,cSAComUnitCode,cPUComUnitCode,cSTComUnitCode,cCAComUnitCode,cInvDefine1," +
"cInvDefine2,cInvDefine3,cInvDefine4 ,cInvDefine5,cInvDefine6,cInvDefine7 ,cInvDefine8, cInvDefine9,cInvDefine10 ,cInvDefine11," +
"cInvDefine12,cInvDefine13,cInvDefine14,cInvDefine15,cInvDefine16,iInvSPrice,iInvSCost,iSafeNum,iTopSum,iLowSum,dSDate,dModifyDate," +
"cCreatePerson,cModifyPerson,cBarCode) values('" + cInvCode + "','" + cInvAddCode + "','" + cInvName + "','" + cInvStd + "','" +
cInvCCode + "','" + bSale + "','" + bPurchase + "','" + bSelf + "','" + bComsume + "','" + iTaxRate + "','" + cGroupCode + "','" +
iGroupType + "','" + cComUnitCode + "','" + cAssComUnitCode + "','" + cAssComUnitCode + "','" + cAssComUnitCode + "','" +
cAssComUnitCode + "','" + cAssComUnitCode + "','" + cInvDefine1 + "','" + cInvDefine2 + "','" + cInvDefine3 + "','" + cInvDefine4 +
"','" + cInvDefine5 + "','" + cInvDefine6 + "','" + cInvDefine7 + "','" + cInvDefine8 + "','" + cInvDefine9 + "','" + cInvDefine10 +
"','" + cInvDefine11 + "','" + cInvDefine12 + "','" + cInvDefine13 + "','" + cInvDefine14 + "','" + cInvDefine15 + "','" +
cInvDefine16 + "','" + iInvSPrice + "','" + iInvSCost + "','" + iSafeNum + "','" + iTopSum + "','" + iLowSum + "','" + dSDate +
"','" + dSDate + "','demo','demo" + "','" + cBarCode + "')"; SqlCommand com = new SqlCommand(InsertSql, conn);
com.Transaction = myTrans;
int num = com.ExecuteNonQuery(); BarInfo(iRows, m, "导入数据");
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
return false;
}
conn.Close();
return true;
求高手指点
插入的语句有列数限制吗,把你的sql语句到数据库中直接运行看看,
可能是插入语句对数据库有列数限制。