Help - Problem Getting DataAdapter to update

[Siv]

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.

[Siv]

Cor,

I sussed it, my problem was that I was using the same connection object in
two of the updating routines.
I have a global var that holds a connection open at all times. So far
elsewhere in the program it hasn't caused me any problems.
My reason for having one permanently open connection was to save time (false
economy given the day and a half I have just spent trying to figure out what
the hell is going on), I will now go through and make sure each routine
creates and destroys its own connection.
Unless you think that might adversely affect performance?

--
Siv
Martley, Near Worcester, UK.
"Cor Ligthert" <no************ @planet.nl> wrote in message
news:Od******** ******@TK2MSFTN GP12.phx.gbl...

Siv,

I think that you find it yourself because of that you built that small
program.

And when not, than sent it to me. I will try than at least to help you to
look to it and try to find the error.

(I assume that you understand what is my email address)

I can of course not promise that I find it.

(Than you are maybe even happier, but I can as well not promise that I
find it not)

:-)

Cor

[Cor Ligthert]

Siv,

Nice that you did solve it. What performance do you think to win.

In SQL server it is adviced to close the connections as soon as possible.

In access there is an aspect not to do that, however I would not think to
long about that.

A user sees normally no performance if he knows that there is some updating
or reading done.

However good you solved it.
This I could not see from here.

:-)

Cor