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.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!!

 

[Cor Ligthert]

Siv,

The main part for me that is not clear for me is how you built that list.
(Because I assume that the rows are deleted by the dataadapter.update before
you start reading again)

You say that you are using 3 passes. And I have the idea that I only see 2.

Beside that, know that in your code all those settings to nothing and
disposing do nothing.
The only thing that can is important is your connection and datareader close
and/or dispose.

I don't see the problem, however maybe can you show or tell what you mean
with.

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:

Is this about the second or the thirth part?

Cor

 

[Siv]

Cor,
The list is drawn initially when the form loads, it obtains a customer ID
and then just lists all the corresponding invoices that have a relational
link to that customer.
You have the code for that in my initial post. I basically open a
datareader on the invoices table using an SQL string to limit the rows to
just those relating to the customer whose invoices the form is displaying.

The listview control (lstInvoices) has a number of columns, one of which
holds the Invoice Number which is the key field of the Invoices table. When
the user clicks the list the column containing the invoice number is used to
call another routine that populates the screen. This is called
"PopulateInvoice" (it just updates all the textboxes on the screen with the
relevant invoice data), it uses a datareader to get the information and it
updates the screen.

The user then decides they want to modify the selected Invoice (Invoices can
have multiple parts, they all have the same invoice number, but different
part numbers, the two fields comprise a unique key). The invoices can exist
as single part invoices where there is one record and the part number is
zero, or they can be multipart invoices where there are multiple records all
with the same invoice number but different part numbers.

My problem is with the multipart invoices, what I am trying to achieve is
that the user can convert a multipart to a single. This is achieved by the
user clicking a button to convert from multipart to single. It then opens
all the records held for a given multipart invoice number. Let's say its
invoice 123 with 3 parts, so the records are

Invoice Number | Part Number
123 | 001
123 | 002
123 | 003

I call the first routine is showed which opens a data adapter with a
commandbuilder using the Invoice Number as the basis of the query. The
resulting 3 records are retrieved with the DataAdapter and DataTable. I
then delete the records 123-003 and 123-002, I then modify the remaining
record 123-001 so that the Part Number field is set to 000 and set a flag
field "MultiPartYN" to false and then ask DA to update DT with :
DA.Fill(DT). When I have done that I expect the database to have deleted
the two records 123-002 and 123-003 which if I look at them in MS Access
they now are deleted, so I know the DataAdapter is doing the update.

The final step after tidying up my vars (Thanks for your comment about not
needing to set everything to Nothing) is to recall the UpdateInvoiceList
routine, this is passed the Invoice number 123 (only so that I can ensure
that the 123 record is highlighted when the list is rebuilt). The
UpdateInvoicelist clears the list completely then uses a DataReader to get
all the Invoices for the customer again based on the Customer Number, I have
a little subroutine that watches each record being retrieved and if it
matches the passed Invoice Number 123 it stores the position in the list so
that at the end of the process I can set the selection onto that item.

What should happen is that the old records 123-002 and 123-003 should now
not be present as they have been deleted, what actually happens is that the
DataReader still finds the old records and puts them in the list. If when
the list is rebuilt showing these old deleted records I click on one of them
to see what happens I get an exception in my PopulateInvoices routine which
fills all the text boxes, because it correctly finds that these two records
don't exist!?

I have found if I put a break in the code and step it manually it works as
expected, but if I let it run normally it always errors and always puts the
deleted records into the list.

Aggghhhh!

Siv

 

[Siv]

Whoops made a mistake in my reply? My sixth paragraph should read:

I call the first routine is showed which opens a data adapter with a

commandbuilder using the Invoice Number as the basis of the query. The
resulting 3 records are retrieved with the DataAdapter and DataTable. I
then delete the records 123-003 and 123-002, I then modify the remaining
record 123-001 so that the Part Number field is set to 000 and set a flag
field "MultiPartYN" to false and then ask DA to update DT with : ****
DA.UPDATE(DT). ***** When I have done that I expect the database to have
deleted the two records 123-002 and 123-003 which if I look at them in MS
Access they now are deleted, so I know the DataAdapter is doing the
update.

 

[Cor Ligthert]

Siv,

Strange and you are not using multithreading ?

Cor

 

[Siv]

Cor,

I wouldn't know how to or dare to try it??
The only thing I have noticed is that if I close the form and then re-open
it the same routine works OK. It's as if something is being cached and only
clears when the form closes and re-opens?
I tried just marking the record as deleted, i.e. I didn't physically remove
it from the database, I just created a DeletedYN field and set it to true
and then in the screen update routine I eliminated records with that set as
part of the SQL String, but that didn't work either as the records appeared
not to have their records marked deleted??

 

[Cor Ligthert]

Siv,

In my opinion is the only thing you can is make a little program that
simulates it in a very easy way.

The biggest problem is, that helping is almost impossible in this way.
I don't see anything wrong in your code that you use to delete. Moreover you
say that it is working in a lot of situations (by instance debugging),
however not were it has to.

(When the dataadapter is ready the deletes should be done)

I would real go in your situation for a little simulation program.

Sorrry, because of the fact that it is not forever going wrong, is it in my
opinion impossible to help you from out of a newsgroup

Cor

 

[Siv]

Cor,
Sorry I don't quite follow, did you want me to put a small program together
that suffers the same problem and send it to you?
I would be quite happy to do that if you have time to look at it.

 

[Cor Ligthert]

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

 

[Siv]

Cor,
Yes I think I know what your email will be, thanks for your help.

 

[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?

 

[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