///读取Excel写入数据库方法
protected void Finish_Click(object sender, EventArgs e)
{
string CnnString = "Provider=SQLOLEDB;database=Rs_Sysyem;server=.;uid=sa;pwd=123456";
string fPath = this.FileUpload2.PostedFile.FileName;//获得要导入的文件名
string extName = fPath.Substring(fPath.LastIndexOf(".") + 1);//获得文件的扩展名
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + fPath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
OleDbConnection conn = new OleDbConnection(CnnString);
conn.Open();
OleDbCommand myCmd = null;
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)//第一个工作表中行数,不包括第一行,
{
strSql = "insert into RS_Person (P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_PHOTO,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values (";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[3].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[4].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[5].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[6].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[7].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[8].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[9].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[10].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[11].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[12].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[13].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[14].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[15].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[16].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[17].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[18].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[19].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[20].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[21].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[22].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[23].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[24].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[25].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[26].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[27].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[28].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[29].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[30].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[31].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[32].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[33].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[34].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[35].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[36].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[37].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[38].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[39].ToString() + "'";
myCmd = new OleDbCommand(strSql, conn);
try
{
myCmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('数据导入成功!')</script>");
}
catch (OleDbException err)
{
Response.Write("导入数据库时出错:" + err.ToString());
break;
}
conn.Close();
}
}
}我调试的时候看到的sql语句怎么是这样的?这条sql语句到底改怎么写? 我这里有40列, 还有一个错误就是ItemArray数组索引超出范围 我看到的:{维数:[35]}怎么会是35 应该要是40,为什么?
insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_PHOTO,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values (''蒙古族'天津市''''''''''''高中'''
protected void Finish_Click(object sender, EventArgs e)
{
string CnnString = "Provider=SQLOLEDB;database=Rs_Sysyem;server=.;uid=sa;pwd=123456";
string fPath = this.FileUpload2.PostedFile.FileName;//获得要导入的文件名
string extName = fPath.Substring(fPath.LastIndexOf(".") + 1);//获得文件的扩展名
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + fPath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
OleDbConnection conn = new OleDbConnection(CnnString);
conn.Open();
OleDbCommand myCmd = null;
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)//第一个工作表中行数,不包括第一行,
{
strSql = "insert into RS_Person (P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_PHOTO,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values (";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[3].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[4].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[5].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[6].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[7].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[8].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[9].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[10].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[11].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[12].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[13].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[14].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[15].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[16].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[17].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[18].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[19].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[20].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[21].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[22].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[23].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[24].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[25].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[26].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[27].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[28].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[29].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[30].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[31].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[32].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[33].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[34].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[35].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[36].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[37].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[38].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[39].ToString() + "'";
myCmd = new OleDbCommand(strSql, conn);
try
{
myCmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('数据导入成功!')</script>");
}
catch (OleDbException err)
{
Response.Write("导入数据库时出错:" + err.ToString());
break;
}
conn.Close();
}
}
}我调试的时候看到的sql语句怎么是这样的?这条sql语句到底改怎么写? 我这里有40列, 还有一个错误就是ItemArray数组索引超出范围 我看到的:{维数:[35]}怎么会是35 应该要是40,为什么?
insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_PHOTO,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values (''蒙古族'天津市''''''''''''高中'''
解决方案 »
- 为啥我调试程序时本身是int类型的值会变成“tm.M_Age = 0x00069930”“tm.M_Id = 0x00000000”,这种结果,求高手帮助啊
- 在后台怎么往一个div中插入html代码啊。
- 怎么样可以把VB里的Session变量传到asp.NET的页面里呢?
- (在线等待...)listbox绑定dataset(查询出来的一列),为什么显示的数据是system.data.dataview?
- .net 动网论坛时间问题,高分在线等...
- vs2005中Webform怎么使用内似winform里动态帮定数据集 在用水晶报表?
- 求助:开发ASP.NET网站后台
- 我要崩溃了,高手进来帮忙,这到底是怎么回事啊?
- 什么时候使用 String ? 什么时候使用 StringBuilder ?
- 在asp.net 1.1里,如何避免出现确认对话框,直接下载
- 关于Rediobutton_CheckedChanged事件问题
- Visual stutio 2005 的属性面板
只取得了35列呗,查一下Excel
或者在myDa.Fill(myDs);后面加个断点调试一下
看看到底取了几列
仁兄Excel 里 肯定也是40 列啦
看看到底取了几列
values (";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "'";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "'";
-------------------------------
改为
values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "',"; 另外这么拼sql效率不好,建议改用 System.Text.StringBuilder
照 gdjlc仁兄的改的 但还是有点问题
protected void Finish_Click(object sender, EventArgs e)
{
string CnnString = "Provider=SQLOLEDB;database=Rs_Sysyem;server=.;uid=sa;pwd=123456";
string fPath = this.FileUpload2.PostedFile.FileName;//获得要导入的文件名
string extName = fPath.Substring(fPath.LastIndexOf(".") + 1);//获得文件的扩展名
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + fPath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
OleDbConnection conn = new OleDbConnection(CnnString);
conn.Open();
OleDbCommand myCmd = null;
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)//第一个工作表中行数,不包括第一行,
{
strSql = "insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[3].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[4].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[5].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[6].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[7].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[8].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[9].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[10].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[11].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[12].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[13].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[14].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[15].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[16].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[17].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[18].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[19].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[20].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[21].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[22].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[23].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[24].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[25].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[26].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[27].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[28].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[29].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[30].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[31].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[32].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[33].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[34].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[35].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[36].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[37].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[38].ToString() + "',";
//strSql += myDs.Tables[0].Rows[i].ItemArray[39].ToString() + "',";
myCmd = new OleDbCommand(strSql, conn);
try
{
myCmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('数据导入成功!')</script>");
}
catch (OleDbException err)//////////"第 1行: ',111111111111111111' 附近有语法错误。"111111111111111111" 是身份证那个字段……
{
Response.Write("导入数据库时出错:" + err.ToString());
break;
}
conn.Close();
}
}
}
以P_NAME,P_NUM为例,如P_NAME是varchar,P_NUM是int,则为
insert into RS_Person(P_NAME,P_NUM) values('asdf',123)写成上面sql则为strSql = "insert into RS_Person(P_NAME,P_NUM) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString(); 其它的也照这样子写....
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "','";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "','"; ....
strSql += myDs.Tables[0].Rows[i].ItemArray[39].ToString() + "'";
修改为:
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[18].ToString() + "',"; 你逗号后面都没有',sql肯定会有问题
strSql = "insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM)
values ( "
for (int i = 0; i < myDs.Tables[0].Rows.Count-2; i++)//第一个工作表中行数,不包括第一行,
{
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "',";
strSql += '"myDs.Tables[0].Rows[i].ItemArray[i].ToString() + "',";
} strSql += '"myDs.Tables[0].Rows[39].ItemArray[39].ToString() + "') ";
myCmd = new OleDbCommand(strSql2, conn);
{
strSql = "insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[1].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[2].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[3].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[4].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[5].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[6].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[7].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[8].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[9].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[10].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[11].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[12].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[13].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[14].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[15].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[16].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[17].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[18].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[19].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[20].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[21].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[22].ToString() +",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[23].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[24].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[25].ToString()+",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[26].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[27].ToString() + ",";
strSql += myDs.Tables[0].Rows[i].ItemArray[28].ToString() + ",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[29].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[30].ToString() + ",";
strSql += myDs.Tables[0].Rows[i].ItemArray[31].ToString() + ",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[32].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[33].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[34].ToString() + ",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[35].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[36].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[37].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[38].ToString();
myCmd = new OleDbCommand(strSql, conn);
try
{
myCmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('数据导入成功!')</script>");
}
catch (OleDbException err)/////第 1 行: '0' 附近有语法错误。
{
Response.Write("导入数据库时出错:" + err.ToString());
break;
}
conn.Close();
}第 1 行: '0' 附近有语法错误。
sql语句还是有问题少了一边括号:insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values ('sasa','4353','111111111111111111','1111-11-11','男','否','侗族','党员','重庆市','重庆市','1111111','11111111111','[email protected]','saasa','111111','11','1111','博士','dsada','dsada','dsasda','dasda',1,'2009-9-1 0:00:00','首都师大',1,'非全日制',8,24,'2009-9-1 0:00:00',1,3245,'2009-9-1 0:00:00','2009-9-1 0:00:00',1900-1-9 0:00:00,'2009-9-1 0:00:00','2009-9-1 0:00:00','2009-9-1 0:00:00',1
protected void Finish_Click(object sender, EventArgs e)
{
string CnnString = "Provider=SQLOLEDB;database=Rs_Sysyem;server=.;uid=sa;pwd=123456";
string fPath = this.FileUpload2.PostedFile.FileName;//获得要导入的文件名
string extName = fPath.Substring(fPath.LastIndexOf(".") + 1);//获得文件的扩展名
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + fPath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
OleDbConnection conn = new OleDbConnection(CnnString);
conn.Open();
OleDbCommand myCmd = null;
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)//第一个工作表中行数,不包括第一行,
{
strSql = "insert into RS_Person(P_NAME,P_NUM,P_IDCARD,P_BirthDay,P_SEX,P_MARRIED,P_NATIONAL,P_PARTY,P_ORIGIN,P_ACCOUNT,P_TEL,P_MOBLIE,P_EMAIL,P_ADDRESS,P_POSTNUM,P_SSNUM,P_BANKNUM,P_QUA,P_SCHOOL,P_PRO,P_COMPUTER,P_LANGUAGE,P_SKILLGRADE,P_ChkSkillDay,P_TITLE,P_JOBSTATUS,P_WORKTYPE,P_JOB,P_DEPARTMENT,P_DoWorkDay,P_WORKAGE,P_WAGE,P_JoinJobDay,P_LeaveJobDay,P_WAGE_GRADE,P_StartDay,P_EndDay,P_FirstDesDay,P_DESNUM) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[0].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[1].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[2].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[3].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[4].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[5].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[6].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[7].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[8].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[9].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[10].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[11].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[12].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[13].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[14].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[15].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[16].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[17].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[18].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[19].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[20].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[21].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[22].ToString() +",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[23].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[24].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[25].ToString()+",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[26].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[27].ToString() + ",";
strSql += myDs.Tables[0].Rows[i].ItemArray[28].ToString() + ",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[29].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[30].ToString() + ",";
strSql += myDs.Tables[0].Rows[i].ItemArray[31].ToString() + ",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[32].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[33].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[34].ToString() + ",";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[35].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[36].ToString().Replace("'", "''") + "',";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[37].ToString().Replace("'", "''") + "',";
strSql += myDs.Tables[0].Rows[i].ItemArray[38].ToString()+")";
myCmd = new OleDbCommand(strSql, conn);
try
{
myCmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('数据导入成功!')</script>");
}
catch (OleDbException err)
{
Response.Write("导入数据库时出错:" + err.ToString());
break;
}
conn.Close();
}
}
}
1900-1-9 0:00:00 前面少个单引号,在相应sql前加个。。