Hi,
I seem to be having a problem with a DataAdapter against an Access database.
My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable.
I then proceed to update a list to reflect that the 3 items have been
deleted only to discover that the 3 items appear, however when I click on
them to display their information which runs a datareader over the same
database it appears that the data has now gone.
I wondered whether this is a timing issue, I tried inserting a timer that
waited for half a second and then updated the list but it still doesn't
work.
If I exit the form that all this is running on (it's an MDI app, so I don't
close the application) then re-open it (creates a new instance), the list is
fine and reflect the fact that the 3 items have been deleted??
Anyone know why I am geting this grief?
Essentially the code is:
The deletion part:
Dim CB As OleDb.OleDbCommandBuilder, DT As Data.DataTable, DA As
OleDb.OleDbDataAdapter
Dim StrSQL As String, Cn As OleDb.OleDbConnection
Dim NumRows As Integer, n As Integer
Try
StrSQL = "Select * from Invoices where [Invoice Number] = " &
InvNum & "AND DeletedYN=False ORDER BY PartPaymentNumber desc;"
Cn = New OleDb.OleDbConnection(DefCon & DBFilePath)
Cn.Open()
DA = New OleDb.OleDbDataAdapter(StrSQL, Cn)
DT = New Data.DataTable
CB = New OleDb.OleDbCommandBuilder(DA)
CB.QuotePrefix = "["
CB.QuoteSuffix = "]"
DA.Fill(DT)
NumRows = DT.Rows.Count
If NumRows > 0 Then
'We have some records
'The records will be in part number reverse order ie, if a 3
parter, it will
'be 3,2,1. We can delete from the database parts 3 and 2,
and then save 1 as part 0
'Change its "Multipart" field to False and return to caller.
If NumRows >= 2 Then
'Remember that the rows are actually numbered from zero
so 1st row is Dt.Rows(0)
For n = 1 To (NumRows - 1)
DT.Rows(n).Delete()
Next
Application.DoEvents()
DT.Rows(0).Item("Multipart") = False
IA.IsMultiPart = False
DT.Rows(0).Item("PartPaymentNumber") = 0
IA.CurrentPartNumber = 0
Else 'Numrows must be 1, ie DT.Rows(0)
DT.Rows(0).Item("Multipart") = False
IA.IsMultiPart = False
DT.Rows(0).Item("PartPaymentNumber") = 0
IA.CurrentPartNumber = 0
End If
'Finally update the database
DA.Update(DT)
End If
'Tidy up
DA.Dispose()
DT.Dispose()
CB.Dispose()
Cn.Close()
StrSQL = Nothing
'update the list
PopulateInvoiceList(InvNum)
Catch etc ...
The rebuild teh list part:
The PopulateInvoiceList(InvNum) sub just rebuilds the list using the passed
InvNum to identify which one to highlight. It uses the follwoing db calls:
Dim StrSQL As String, Dr As OleDb.OleDbDataReader, Cmd As
OleDb.OleDbCommand, Str As String, n As Integer
Dim SelItem As Integer, TempStr As String, cn As
OleDb.OleDbConnection
Try
StrSQL = "Select * from Invoices where fkCustomerID = " &
CurrentClientID & " ORDER BY [Invoice Number] Desc, PartPaymentNumber;"
cn = New OleDb.OleDbConnection(DefCon & DBFilePath) 'DefCon and
DBfile path just build the connection string.
cn.Open()
Cmd = New OleDb.OleDbCommand(StrSQL, Conn)
Dr = Cmd.ExecuteReader
lstInvoices.Items.Clear()
If Not Dr.HasRows Then
'No Invoices found for this customer
'Tidy Up
Dr.Close()
Cmd.Dispose()
Dr = Nothing
Cmd = Nothing
StrSQL = Nothing
Exit Sub
End If
Do While Dr.Read
Str = Format(Dr("Invoice Number")) 'There has to be one of
these as it's a required field
lstInvoices.Items.Add(Str) 'Push it into the list
TempStr += Str
Str = Format(Dr("PartPaymentNumber"), "000")
lstInvoices.Items(n).SubItems.Add(Str)
TempStr += ", " & Str
If Not IsDBNull(Dr("Note 1")) Then
Str = Dr("Note 1")
End If
If Not IsDBNull(Dr("Note 2")) Then
If Str = "" Then
Str = Dr("Note 2")
Else
Str += ", " & Dr("Note 2")
End If
End If
TempStr += ", " & Str
lstInvoices.Items(n).SubItems.Add(Str)
'MsgBox(TempStr, MsgBoxStyle.Information, "Siv Testing")
TempStr = ""
'If an item has been requested to be selected then check for
it here and
'store the index so at the end we can highlight that one.
If (Dr("Invoice Number") = InvNum) And
(Dr("PartPaymentNumber") = PartNum) Then
'We are passing the one to select
SelItem = n
End If
n += 1
Loop
'If no match found, then selitem = 0 or first item in list
If lstInvoices.Items.Count > 0 Then
lstInvoices.Items(SelItem).Selected = True
If SelItem = 0 Then
'Get the InvNum for that item
InvNum = GetSelectedInvoiceNumber() 'If we haven't
passed an Invoice number to select, system just works out what the Invoice
ID is from the list and shows that.
PartNum = GetSelectedPartNumber() 'Ditto the part number
if it's a multipart invoice.
End If
PopulateInvoice(InvNum, False, PartNum) 'This call then
updates teh main part of the screen to reflect the selected item in the
list.
End If
'Tidy Up
Dr.Close()
Cmd.Dispose()
Dr = Nothing
Cmd = Nothing
StrSQL = Nothing
I have tried creating a RowUpdated handler for the DataAdapter, but that
just fires and I still get errors when trying to draw the list.
It's driving me mad!!
--
Siv
Martley, Near Worcester, UK.
