100分求教一个导入问题。。急用

[yao110119]

做EXCEL导入SQL表时，只导入了EXCEL里面的第一行，其余的都不显示，代码应该如何改呢？谢谢。。。。代码如下：

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "; Extended Properties=Excel 8.0;"
Try
Dim oleDbConnection As OleDbConnection = New OleDbConnection(sConnectionString)
oleDbConnection.Open()

'获取excel表
Dim dataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

'获取sheet名，其中(0)(1)...(N): 按名称排列的表单元素
Dim tableName As String = dataTable.Rows(0)(2).ToString().Trim()
tableName = "[" & tableName.Replace(" ' ", " ") & "]"

'利用SQL语句从Excel文件里获取数据
Dim query As String = "SELECT 发生时间,工号,制造单号,货号,床号,工序标识,工价,数量 FROM " & tableName
Dim dataset As DataSet = New DataSet()

Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)

oleAdapter.Fill(dataset, "导入表")

'SQL数据库连接
Dim sqlcon As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=(local);database=sx;user id=sa;password=123")
sqlcon.Open()

'从excel文件获得数据后，插入记录到SQL Server的数据表
Dim dataTable1 As DataTable = New DataTable()

Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT 发生时间,工号,制造单号,货号,床号,工序标识,工价,数量 FROM 导入表 ", sqlcon)

Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)

sqlDA1.Fill(dataTable1)

Dim dataRow11 As DataRow
For Each dataRow11 In dataset.Tables("导入表").Rows
'sql里数据dataRow1
Dim dataRow1 As DataRow = dataTable1.NewRow()
dataRow1("发生时间") = dataRow11("发生时间")
dataRow1("工号") = dataRow11("工号")
dataRow1("制造单号") = dataRow11("制造单号")
dataRow1("货号") = dataRow11("货号")
dataRow1("床号") = dataRow11("床号")
dataRow1("工序标识") = dataRow11("工序标识")
dataRow1("工价") = dataRow11("工价")
dataRow1("数量") = dataRow11("数量")

dataTable1.Rows.Add(dataRow1)

Next
MsgBox("新插入 " & dataTable1.Rows.Count.ToString() & " 条记录 ")
sqlDA1.Update(dataTable1)

oleDbConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString())
End Try

[古今多少事]

For i=0 to dataset.Tables("导入表").Rows.count-1
'sql里数据dataRow1
Dim dataRow1 As DataRow = dataTable1.NewRow()
dataRow1(i).item("发生时间") = dataRow11("发生时间")
……
……
dataTable1.Rows.Add(dataRow1)

Next

不行的话，将添加行放在循环之外，先全部添加上，然后循环赋值……

[wuyq11]

dataset.Tables("导入表").Rows.Count记录数是多少

[wuyq11]

跟踪调试看看 dataTable1.Rows.Add(dataRow1) 执行情况
protected void button1_click(object sender, EventArgs e)
{

string conStr = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
SqlConnection conn = new SqlConnection(conStr);
conn.Open();


string sql = "select * from A";
SqlCommand com = new SqlCommand(sql,conn);
SqlTransaction tran = conn.BeginTransaction();
com.Transaction = tran;
SqlDataAdapter da = new SqlDataAdapter(com);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);


DataTable tb = this.getExcelDate();


for (int i = 0; i < tb.Rows.Count; i++)
{
DataRow dr = ds.Tables[0].NewRow();
dr[0] = tb.Rows[i][0];
dr[1] = tb.Rows[i][1];
dr[2] = tb.Rows[i][2];

ds.Tables[0].Rows.Add(dr);
}
try
{
da.Update(ds);
tran.Commit();
}
catch
{
tran.Rollback();
}

conn.Dispose();
}

public DataTable getExcelDate()
{
string strExcelFileName = "D:\\sheet.xls";
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
string sql = "select * from [Data$]";
OleDbConnection xlsConn = new OleDbConnection(strcon);
OleDbCommand cmdOle = new OleDbCommand(sql, xlsConn);
xlsConn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmdOle);
DataSet ds = new DataSet();
da.Fill(ds);

xlsConn.Close();
xlsConn.Dispose();
return ds.Tables[0];
}

[xue1234567890]

zhichi..

[超维电脑科技]

学习

[nices0028]

For i = 0 To myDs.Tables(0).Rows.Count - 1 Step i + 1
strSql = "Insert Into 导入表 (发生时间,工号,制造单号,货号,床号,工序标识,工价,数量) values ('" + myDs.Tables(0).Rows(i).ItemArray(0).ToString() + "','" + myDs.Tables(0).Rows(i).ItemArray(1).ToString() + "','" + myDs.Tables(0).Rows(i).ItemArray(2).ToString() + "','" + myDs.Tables(0).Rows(i).ItemArray(3).ToString() + "','" + myDs.Tables(0).Rows(i).ItemArray(4).ToString + "','" + myDs.Tables(0).Rows(i).ItemArray(5).ToString() + "','" + myDs.Tables(0).Rows(i).ItemArray(6).ToString() + "','" + myDs.Tables(0).Rows(i).ItemArray(7).ToString() + "')"

Try
myCmd = New SqlCommand(strSql, conn)
myCmd.ExecuteNonQuery()
Catch

End Try
Next

[gongsun]

...

[appleller]

先点BUTTON1读EXCEL数据到DATAGRIDVIEW, 然后点BUTTON2写入SQL表，
导入结果再反应到DATAGRIDVIEW加首列。 最后一步怎么处理？