从Dataset导数据到Access中,但第一行不见了,请高手看看怎解决!!

[gh_li]

代码如下:
Protected Function OutToAccess(ByVal dtSource As DataTable, ByVal strSavePath As String) As Boolean
Try
Dim clg As Catalog = New Catalog
clg.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strSavePath + ";Jet OLEDB:Engine Type=5")
Dim dtResult As ADOX.Table = New ADOX.Table

dtResult.Name = "QueryResult"

'根据的Table里面的表结构生成一个新的表.
For Each colTemp As DataColumn In dtSource.Columns
Dim axCol As ADOX.Column = New ADOX.Column
With axCol
.Name = colTemp.ColumnName
.Type = GetAdoxType(colTemp.DataType.Name)
End With
dtResult.Columns.Append(axCol)
Next
dtResult.Keys.Append("PK", KeyTypeEnum.adKeyPrimary, dtResult.Columns.Item(0))
clg.Tables.Append(dtResult)

'建立连接到目标MDB文件
Dim cn As System.Data.OleDb.OleDbConnection
Dim da As System.Data.OleDb.OleDbDataAdapter
Dim ds As DataSet = New Data.DataSet
cn = New System.Data.OleDb.OleDbConnection("provider = microsoft.jet.oledb.4.0;" & _
"data source = " + strSavePath)
da = New System.Data.OleDb.OleDbDataAdapter("select * from QueryResult", cn)

cn.Open() '打开连接
da.Fill(ds, "QueryResult")

ds.Tables("QueryResult").Clear()
'填充数据到新表里面
For Each rowSource As DataRow In dtSource.Rows
ds.Tables("QueryResult").Rows.Add(rowSource.ItemArray)
Next

Dim tempBuilder As OleDb.OleDbCommandBuilder
tempBuilder = New OleDb.OleDbCommandBuilder(da)
da.Update(ds, "QueryResult") '更新数据库
cn.Close()
Catch ex As Exception
Throw New Exception("输出到Access出错:" + ex.Message)
Return False
End Try
Return True
End Function

比如说Dataset中有18条数据,但在MDB文件中就只有十七条,第一条不见了

[S170393163]

da.Update(ds, "QueryResult") '更新数据库
这个方法才能看出问题吧。。

[gh_li]

能不能说具体一点呀

[gh_li]

问题已解决,原因是我用ADOX创建了一个新的Access表,但没有关闭ADOX
然后再用ADO.net连接ACCESS,因为ADOX是独占方式进行连接的.所以在Update数据时会出错,生成的ACCESS文件有两个,一个是MDB文件,一个是LBD文件.
修改后的代码:
Protected Function OutToAccess(ByVal dtSource As DataTable, ByVal strSavePath As String) As Boolean
Try
Dim clg As Catalog = New Catalog
clg.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strSavePath + ";Jet OLEDB:Engine Type=5")
Dim dtResult As ADOX.Table = New ADOX.Table

dtResult.Name = "QueryResult"

'根据的Table里面的表结构生成一个新的表.
For Each colTemp As DataColumn In dtSource.Columns
Dim axCol As ADOX.Column = New ADOX.Column
With axCol
.Name = colTemp.ColumnName
.Type = GetAdoxType(colTemp.DataType.Name)
End With
dtResult.Columns.Append(axCol)
Next
dtResult.Keys.Append("PK", KeyTypeEnum.adKeyPrimary, dtResult.Columns.Item(0))
clg.Tables.Append(dtResult)

clg = Nothing '关闭ADOX
GC.Collect()
'建立连接到目标MDB文件
Dim cn As System.Data.OleDb.OleDbConnection
Dim da As System.Data.OleDb.OleDbDataAdapter
Dim cb As OleDb.OleDbCommandBuilder
cn = New System.Data.OleDb.OleDbConnection("provider = microsoft.jet.oledb.4.0;" & _
"data source = " + strSavePath)

da = New System.Data.OleDb.OleDbDataAdapter("select * from QueryResult", cn)
cb = New OleDb.OleDbCommandBuilder(da)
Dim ds As DataSet = New Data.DataSet
cn.Open() '打开连接
da.Fill(ds, "QueryResult")

For Each rowSource As DataRow In dtSource.Rows
ds.Tables("QueryResult").Rows.Add(rowSource.ItemArray)
Next
da.Update(ds, "QueryResult") '更新数据库
cn.Close()
Catch ex As Exception
Throw New Exception("输出到Access出错:" + ex.Message)
Return False
End Try
Return True
End Function

[gh_li]

谁有更好的方法请回答,不然分给费了!谢谢大家!

[Plougher]

来往了，问题已经解决，结贴

[CathySun118]

lz解决了，还需要什么方法？