I have 3 tables that I use temporarily to display search results in a DataGridView. At the start of each search, I delete all of the rows and then save the search results. For the first 2 tables, this process works entirely as expected, but for the 3rd table, the delete only works the first time in a session, leaving all rows in the table for subsequent searches. Here is the code for one of the 2 successful routines: CODE Private Sub SaveMaintenanceFileSearchResults()
Dim Row As Integer = 0
Dim strSQL As String = "SELECT Structure, DocumentDate, Category, ID FROM MaintenanceFileSearchResults ORDER BY Structure, DocumentDate"
Dim Adapter As OleDb.OleDbDataAdapter = _
DataLayer.GetDataAdapter(cMaintenanceManualsConnection, _
strSQL)
Dim MyTable As DataTable = _
DataLayer.GetDataTable(Adapter, _
cMaintenanceManualsConnection, _
strSQL, _
True)
Dim Builder As OleDbCommandBuilder = New OleDbCommandBuilder(Adapter)
Dim NewRow As DataRow
With MyTable
Adapter.DeleteCommand = Builder.GetDeleteCommand
For Each NewRow In .Rows
NewRow.Delete()
Next NewRow
Adapter.Update(MyTable)
For Row = 0 To BDMDSSearchArrays.NumMaintenanceFiles
NewRow = .NewRow
NewRow.Item("Structure") = BDMDSSearchArrays.MaintenceFileResults(Row).StructureNo
NewRow.Item("DocumentDate") = BDMDSSearchArrays.MaintenceFileResults(Row).DocumentDate
NewRow.Item("Category") = BDMDSSearchArrays.MaintenceFileResults(Row).Category
.Rows.Add(NewRow)
Next Row
End With
With Adapter
.InsertCommand = Builder.GetInsertCommand
.UpdateCommand = Builder.GetUpdateCommand
.Update(MyTable)
End With
End Sub I adapted this code for the 3rd table, copying and pasting to make it as like as possible: CODE Private Sub SaveCalcBookSearchResults()
Dim Row As Integer = 0
Dim strSQL As String = "SELECT CalcBook, ProjectName, Requester, Category, RequestYear, Structure, ID FROM CalcBookSearchResults"
Dim Adapter As OleDb.OleDbDataAdapter = _
DataLayer.GetDataAdapter(cMaintenanceManualsConnection, _
strSQL)
Dim MyTable As DataTable = _
DataLayer.GetDataTable(Adapter, _
cMaintenanceManualsConnection, _
strSQL, _
True)
Dim Builder As OleDbCommandBuilder = New OleDbCommandBuilder(Adapter)
Dim NewRow As DataRow
With MyTable
Adapter.DeleteCommand = Builder.GetDeleteCommand
For Each NewRow In .Rows
NewRow.Delete()
Next NewRow
Adapter.Update(MyTable)
For Row = 0 To BDMDSSearchArrays.NumCalcBooks
NewRow = .NewRow
NewRow.Item("CalcBook") = BDMDSSearchArrays.CalcBookResults(Row).CalcBook
NewRow.Item("ProjectName") = BDMDSSearchArrays.CalcBookResults(Row).ProjectName
NewRow.Item("Requester") = BDMDSSearchArrays.CalcBookResults(Row).Requester
NewRow.Item("Category") = BDMDSSearchArrays.CalcBookResults(Row).Category
NewRow.Item("RequestYear") = BDMDSSearchArrays.CalcBookResults(Row).RequestYear
NewRow.Item("Structure") = BDMDSSearchArrays.CalcBookResults(Row).StructureNo
.Rows.Add(NewRow)
Next Row
End With
With Adapter
.InsertCommand = Builder.GetInsertCommand
.UpdateCommand = Builder.GetUpdateCommand
.Update(MyTable)
End With
End Sub Both tables were created in the same manner (Design View) in the same Access database. I'm scratching my head a bit here, needless to say. |
|
Talk To Other Members
Read More Threads Like This One
