我遇到了一个问题,我使用数据适配器更新然后重新填充数据表。调用fill方法后,行被复制。一个ID具有正确的(新)ID,另一个ID显示-1。下面的代码工作完美,是我希望我的更复杂的代码做的更简单的形式。考虑以下内容:
Imports WindowsApplication1.testDataSet
Imports WindowsApplication1.testDataSetTableAdapters
Imports System.Data.OleDb
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim DA As New testTableAdapter
Dim DT As New testDataTable
DA.Fill(DT)
Dim NR As testRow = DT.Rows.Add
NR.SomeText = "Test"
Dim DA2 As New OleDbDataAdapter("SELECT * FROM test", _
DA.Connection.ConnectionString)
Dim CB As New OleDbCommandBuilder(DA2)
DA2.Update(DT)
DA.Fill(DT)
For Each R As testRow In DT.Rows
Debug.Print(R.ID)
Next
End Sub
End Class
上面的代码工作完美。键列没有显示-1,没有重复项。现在考虑我的应用程序中的下面代码,它导致在最后一个LoadLoadNumbers()
之后出现一个具有键列的重复行,结果是-1。
Dim AccountLoans As IEnumerable(Of LoanNumbersRow) = _
From L As LoanNumbersRow In LoanNumbers _
Select L Where L.AccountID = ID
If Not frmFindLoans.IsDisposed AndAlso _
frmFindLoans.DialogResult = Windows.Forms.DialogResult.OK Then
For Each L As LoanNumbersRow In AccountLoans
If (From R As DataGridViewRow In frmFindLoans.dgvLoans.Rows _
Select R Where R.Cells("LoanNumber").Value = L.LoanNumber).Count = 0 Then
If L.IsWhenDeletedNull Then
L.WhenDeleted = Now
L.DeletedBy = UserName()
End If
End If
Next
Dim NewLoan As LoanNumbersRow
Dim FindLoan As IEnumerable(Of LoanNumbersRow)
For Each R As DataGridViewRow In frmFindLoans.dgvLoans.Rows
FindLoan = From L As LoanNumbersRow In LoanNumbers.Rows _
Select L Where L.LoanNumber = R.Cells("LoanNumber").Value And _
L.AccountID = ID
If FindLoan.Count = 0 Then
NewLoan = LoanNumbers.Rows.Add
NewLoan.AccountID = Acc.AccountID
NewLoan.LoanNumber = R.Cells("LoanNumber").Value
NewLoan.LoanBusinessName = R.Cells("LoanBusiness").Value
NewLoan.LoanBorrower = R.Cells("LoanBorrower").Value
NewLoan.AddedBy = UserName()
NewLoan.WhenAdded = Now
End If
Next
Try
Dim CB As New OleDbCommandBuilder(LoanNumbersAdapter)
LoanNumbersAdapter.Update(LoanNumbers)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error saving loan number data")
Exit Sub
End Try
If Not LoadLoanNumbers() Then Exit Sub
End If
模块中的其他变量:
Public LoanNumbersAdapter As OleDbDataAdapter
Public LoanNumbers As New LoanNumbersDataTable
Public Sub InitializeAdapters()
LoanNumbersAdapter = New OleDbDataAdapter( _
"SELECT * FROM LoanNumbers WHERE WhenDeleted IS NULL ORDER BY WhenAdded DESC", AccountingConn)
End Sub
Public Function LoadData(ByVal DA As OleDbDataAdapter, ByVal DT As DataTable) As Boolean
Try
DA.Fill(DT)
Return True
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error loading the " & DT.TableName & " table")
Return False
End Try
End Function
Public Function LoadLoanNumbers() As Boolean
Return LoadData(LoanNumbersAdapter, LoanNumbers)
End Function
为什么顶部的简单测试工作得很好,但我的实际应用程序在键列上创建了带-1的重复行?我想我可以在更新后填充之前清除数据表,但一旦它开始成为一个大表,它不会陷入困境吗?*顺便说一句:数据库是MS访问,它是。net 3.5
这是我的管道胶带解决方案:(
''' <summary>
''' Removes any rows where the ID/key column is less than zero
''' </summary>
<Extension()> Public Sub DeleteRelics(ByVal DT As DataTable)
If DT.PrimaryKey.Count = 0 Then Exit Sub
For Each R As DataRow In _
(From Rows As DataRow In DT.Rows _
Select Rows Where Rows(DT.PrimaryKey.First.ColumnName) < 0)
R.Delete()
Next
DT.AcceptChanges()
End Sub