473,695 Members | 2,708 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Nov 21 '05 #1
11 2239
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.upd ate 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 PopulateInvoice List(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
Nov 21 '05 #2
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
"PopulateInvoic e" (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 "MultiPartY N" 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 UpdateInvoiceLi st
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
UpdateInvoiceli st 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 PopulateInvoice s 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
Martley, Near Worcester, UK.
"Cor Ligthert" <no************ @planet.nl> wrote in message
news:eJ******** ******@tk2msftn gp13.phx.gbl...
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.upd ate
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 PopulateInvoice List(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

Nov 21 '05 #3
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 "MultiPartY N" 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.

--
Siv
Martley, Near Worcester, UK.
"Siv" <ms**********@r emoveme.sivill. com> wrote in message
news:uD******** ******@TK2MSFTN GP15.phx.gbl... 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
"PopulateInvoic e" (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 "MultiPartY N" 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 UpdateInvoiceLi st
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
UpdateInvoiceli st 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 PopulateInvoice s
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
Martley, Near Worcester, UK.
"Cor Ligthert" <no************ @planet.nl> wrote in message
news:eJ******** ******@tk2msftn gp13.phx.gbl...
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.upd ate
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 PopulateInvoice List(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


Nov 21 '05 #4
Siv,

Strange and you are not using multithreading ?

Cor
Nov 21 '05 #5
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??

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

Strange and you are not using multithreading ?

Cor

Nov 21 '05 #6
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
Nov 21 '05 #7
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.
--
Siv
Martley, Near Worcester, UK.
"Cor Ligthert" <no************ @planet.nl> wrote in message
news:ua******** ********@TK2MSF TNGP09.phx.gbl. ..
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

Nov 21 '05 #8
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
Nov 21 '05 #9
Siv
Cor,
Yes I think I know what your email will be, thanks for your help.
--
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

Nov 21 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2251
by: randy | last post by:
Hello all, I have a DataTable which I am building column by column and adding rows after each new column. The DataTable columns match the columns in my database table. I'm building the DataTable first and I then want to roll through the DataTable while in memory checking for errors and then commit the rows to my database table (btw this is in ASP.NET). Is it possible to have data in a datable before attaching at DataAdapter? I'm a...
4
2618
by: Frnak McKenney | last post by:
I'm using an in-core DataSet as an image of my application's 'database' (a multi-table Access97 mdb file). Updates are made to the DataTables within the DataSet via forms with bound TextBoxes, then written to the database... or at least that's what's supposed to be happening. Unfortunately, I've discovered that while it appears that when I create a new record/row I'm successfully updating the Access database, once the Update is...
4
1733
by: Richard | last post by:
In normal asp i used --------- objRS.Open "tbl_Nieuws", objConn, 1, 3 objRS.AddNew objRS.Fields("N_Datum") = FormatDateTime(Now(),2) objRS.Fields("N_Title") = ReplaceHTML(Upload.Form("title")) objRS.Fields("N_Intro") = ReplaceHTML(Upload.Form("intro")) objRS.Fields("N_Body") = ReplaceHTML(Upload.Form("body"))
2
2064
by: Gary | last post by:
I have a function defined In a Class1. Ex Public Class1 Public Function GetData () as DataSet ******** Using SqlDatAdapter I fill the DataSet
13
2085
by: Doug Bell | last post by:
Hi, I thought I had this sorted this morning but it is still a problem. My application has a DataAccess Class. When it starts, it: Connects to a DB (OLE DB) If it connects it uses an OleDbCommand with an SQL String and the connection it has a DataAdapter with the command then it fills the DataSet's DataTable with the streamed data.
28
1881
by: Siv | last post by:
Hi, If I run the following: strSQL = "Select * FROM Clients;" da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter dt = New Data.DataTable da.Fill(dt) 'pour in the data using the adapter
2
13966
by: susan.f.barrett | last post by:
Hi, Despite me being able to type the following in to SQL Server and it updating 1 row: > updatestockcategory 1093, 839 In my code, it is not updating any rows. dataSet = new DataSet();
7
1802
by: Max | last post by:
I've included the needed tables in the DataSource. Those tables that are bound to controls I can workwith. But how do you get access to the DataAdaptors that are not bound? me.Dataset1.table is a table with no Insert or Update methods. pll.DataSet1.table has the Row, ChangeEvent, and ChangeEventHandler. It seems like it should be fairly straight forward to use the DataAdapter without binding it to a control. How do you do this?
3
12508
by: Rich | last post by:
What is the diffeence bewtween a dataAdapter.InsertCommand and dataAdapter.SelectCommand (and dataAdapter.UpdateCommand for that matter)? Dim da As SqlDataAdapter conn.Open da.SelectCommand = New SqlCommand da.SelectCommand.Connectoin = conn da.SelectCommand.CommandType = Command.Text da.SelectCommand.CommandText = "insert Into tbl1 Select * from tbl2" da.SelectCommand.ExecuteNonQuery
0
8619
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8559
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
7652
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6487
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5832
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4338
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2996
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2261
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.