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.OleDbComm andBuilder, DT As Data.DataTable, DA As
OleDb.OleDbData Adapter
Dim StrSQL As String, Cn As OleDb.OleDbConn ection
Dim NumRows As Integer, n As Integer
Try
StrSQL = "Select * from Invoices where [Invoice Number] = " &
InvNum & "AND DeletedYN=False ORDER BY PartPaymentNumb er desc;"
Cn = New OleDb.OleDbConn ection(DefCon & DBFilePath)
Cn.Open()
DA = New OleDb.OleDbData Adapter(StrSQL, Cn)
DT = New Data.DataTable
CB = New OleDb.OleDbComm andBuilder(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).Dele te()
Next
Application.DoE vents()
DT.Rows(0).Item ("Multipart" ) = False
IA.IsMultiPart = False
DT.Rows(0).Item ("PartPaymentNu mber") = 0
IA.CurrentPartN umber = 0
Else 'Numrows must be 1, ie DT.Rows(0)
DT.Rows(0).Item ("Multipart" ) = False
IA.IsMultiPart = False
DT.Rows(0).Item ("PartPaymentNu mber") = 0
IA.CurrentPartN umber = 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
PopulateInvoice List(InvNum)
Catch etc ...
The rebuild teh list part:
The PopulateInvoice List(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.OleDbData Reader, Cmd As
OleDb.OleDbComm and, Str As String, n As Integer
Dim SelItem As Integer, TempStr As String, cn As
OleDb.OleDbConn ection
Try
StrSQL = "Select * from Invoices where fkCustomerID = " &
CurrentClientID & " ORDER BY [Invoice Number] Desc, PartPaymentNumb er;"
cn = New OleDb.OleDbConn ection(DefCon & DBFilePath) 'DefCon and
DBfile path just build the connection string.
cn.Open()
Cmd = New OleDb.OleDbComm and(StrSQL, Conn)
Dr = Cmd.ExecuteRead er
lstInvoices.Ite ms.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("Invo ice Number")) 'There has to be one of
these as it's a required field
lstInvoices.Ite ms.Add(Str) 'Push it into the list
TempStr += Str
Str = Format(Dr("Part PaymentNumber") , "000")
lstInvoices.Ite ms(n).SubItems. Add(Str)
TempStr += ", " & Str
If Not IsDBNull(Dr("No te 1")) Then
Str = Dr("Note 1")
End If
If Not IsDBNull(Dr("No te 2")) Then
If Str = "" Then
Str = Dr("Note 2")
Else
Str += ", " & Dr("Note 2")
End If
End If
TempStr += ", " & Str
lstInvoices.Ite ms(n).SubItems. Add(Str)
'MsgBox(TempStr , MsgBoxStyle.Inf ormation, "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("PartPaymen tNumber") = 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.Ite ms.Count > 0 Then
lstInvoices.Ite ms(SelItem).Sel ected = True
If SelItem = 0 Then
'Get the InvNum for that item
InvNum = GetSelectedInvo iceNumber() '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 = GetSelectedPart Number() '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.
