还是关于sql批量导入的问题,求指教!

无风我亦扬 2013-02-01 10:20:12
现在报了个这样的错!!

然后这段我是这样写的

private void bind(string fileName)
{
// int i = 0;
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=2;'";

OleDbConnection con1 = new OleDbConnection(strConn);
con1.Open();
DataTable sheetNames = con1.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });


// string sheetname = sheetable.Rows[i]["Table_Name"].ToString();//在这一行有时报错
// MessageBox.Show(sheetname);
// String a1 = "学生基本信息表";
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * From [Sheet1$] ", strConn);
// OleDbDataAdapter da = new OleDbDataAdapter("SELECT * From [Sheet1$] ", strConn);

string[] a = new string[100];
SqlConnection coon = new SqlConnection();
coon.ConnectionString = "data source = .;initial catalog = School; user id = sa;password = 4380364054";
try
{
coon.Open();
Console.WriteLine("数据库连接成功");
button2.Enabled = false;
InData.Enabled = true;
MessageBox.Show("数据库连接成功");
}
catch (Exception ex)
{
Console.WriteLine("数据库连接失败");
MessageBox.Show("操作失败!" + ex.ToString());
}
DataSet ds = new DataSet();
try
{
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
// for (int v = 0; v < dataGridView1.Rows.Count; v++) {
string c1 = dataGridView1.Rows.Count.ToString();//获取行数
int c2 = int.Parse(c1);
c2 = c2 - 1;
c1 = c2.ToString();
string a11 = dataGridView1.Rows[0].Cells[1].Value.ToString();//获取 第一行第二列数值 ————Rows行
// ------cells列
MessageBox.Show(a11, c1, MessageBoxButtons.OK, MessageBoxIcon.Information);

int zy = 1;
for (int v = 0; v < c2 ; v++)
{
for (int v1 = 1; v1 <= 17; v1++)
{

string zx;
zx = zy.ToString();
a[v1] = dataGridView1.Rows[v].Cells[v1].Value.ToString();

// MessageBox.Show(a[v1] + "\n" + zy, c1, MessageBoxButtons.OK, MessageBoxIcon.Information);
zy=zy+1;
}

SqlConnection cocn = new SqlConnection("data source = .;initial catalog = School; user id = sa;password = 4380364054;database=School");
// string connStr="data source = .;initial catalog = School; user id = sa;password = 4380364054";

// SqlConnection conn=new SqlConnection(connStr);
//SqlConnection conn=new SqlConnection(coon);
cocn.Open();
SqlCommand cmd = cocn.CreateCommand();
cmd.CommandText= "insert into Student"+"(StuNo,StuName,ClassID,StuSex,StuBornDate,StuNation,Status,IdentityCard,StuFrom,StuPhone,FamilyPhone,StuAddress,BeginTime)"+
"values (@iStuNo,@iStuName,@iClassID,@iStuSex,@iStuBornDate,@iStuNation,@iStatus,@iIdentityCard,@iStuFrom,@iStuPhone,@iFamilyPhone,@iStuAddress,@iBeginTime)";
// SqlCommand cmdzx=new SqlCommand(sql,conn);//
// string sql1= "insert into users(Name,Age,Sex,Pwd)values (@myName,@myAge,@mySex,@myPwd)";
// SqlCommand cmd1=new SqlCommand(sql,conn);//conn请自行给出

//SqlCommand cmd = conn.CreateCommand();
cmd.Parameters.Add("@iStuNo", SqlDbType.VarChar);
cmd.Parameters.Add("@iStuName", SqlDbType.VarChar);
cmd.Parameters.Add("@iClassID", SqlDbType.Int);
cmd.Parameters.Add("@iStuSex", SqlDbType.VarChar);
cmd.Parameters.Add("@iStuBornDate", SqlDbType.SmallDateTime);
cmd.Parameters.Add("@iStuNation", SqlDbType.VarChar);
cmd.Parameters.Add("@iIdentityCard", SqlDbType.Char);
cmd.Parameters.Add("@iStuPhone", SqlDbType.VarChar);
cmd.Parameters.Add("@iFamilyPhone", SqlDbType.VarChar);
cmd.Parameters.Add("@iStuAddress", SqlDbType.Char);
// cmd.Parameters.Add( "@iImage",SqlDbType.Image);
cmd.Parameters.Add("@iStatus", SqlDbType.VarChar);
cmd.Parameters.Add("@iBeginTime", SqlDbType.SmallDateTime);
cmd.Parameters.Add("@iStuFrom", SqlDbType.VarChar);
// cmd.Parameters.Add("@iOther", SqlDbType.Text);

cmd.Parameters["@iStuNo"].Value = a[4].ToString();
cmd.Parameters["@iStuName"].Value = a[5].ToString();
cmd.Parameters["@iClassID"].Value ="1";
cmd.Parameters["@iStuSex"].Value = a[6].ToString();
cmd.Parameters["@iStuBornDate"].Value = "1992-1-1"; ;
cmd.Parameters["@iStuNation"].Value = a[7].ToString();
cmd.Parameters["@iIdentityCard"].Value = "13080219304140115";
cmd.Parameters["@iStuPhone"].Value = a[13].ToString();
cmd.Parameters["@iFamilyPhone"].Value = a[14].ToString();
cmd.Parameters["@iStuAddress"].Value = null ;
// cmd.Parameters[ "@iImage"].Value="";
cmd.Parameters["@iStatus"].Value = a[9].ToString();
cmd.Parameters["@iBeginTime"].Value = "1992-1-1";
cmd.Parameters["@iStuFrom"].Value = a[8].ToString();
// cmd.Parameters["@iOther"].Value = a[16].ToString();
cmd.ExecuteNonQuery();
cocn.Close();



;



}
}
catch (Exception err)
{
// MessageBox.Show("操作失败!" + err.ToString());
MessageBox.Show("操作失败!" + err.ToString());
}


}
...全文
209 5 点赞 打赏 收藏 举报
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
"(StuNo,StuName,ClassID,StuSex,StuBornDate,StuNation,Status,IdentityCard,StuFrom,StuPhone,FamilyPhone,StuAddress,BeginTime)"
  • 打赏
  • 举报
回复
无风我亦扬 2013-02-02
引用 1 楼 devmiao 的回复:
md.CommandText= "insert into Student"+"( => md.CommandText= "insert into Student"+"( 半角全角错了。
谢啦,搞定
  • 打赏
  • 举报
回复
sy_binbin 2013-02-02
"S 是全角的
  • 打赏
  • 举报
回复
PaulyJiang 2013-02-02
sql 语句先拿到企业管理器里面去run下再说
  • 打赏
  • 举报
回复
devmiao 2013-02-01
md.CommandText= "insert into Student"+"( => md.CommandText= "insert into Student"+"( 半角全角错了。
  • 打赏
  • 举报
回复
相关推荐
发帖
C#
加入

10.6w+

社区成员

.NET技术 C#
申请成为版主
帖子事件
创建了帖子
2013-02-01 10:20
社区公告

让您成为最强悍的C#开发者