473,898 Members | 2,914 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help - Problem Getting DataAdapter to update

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

StrSQL = "Select * from Invoices where [Invoice Number] = " &
InvNum & "AND DeletedYN=False ORDER BY PartPaymentNumb er desc;"
Cn = New OleDb.OleDbConn ection(DefCon & DBFilePath)
DA = New OleDb.OleDbData Adapter(StrSQL, Cn)
DT = New Data.DataTable
CB = New OleDb.OleDbComm andBuilder(DA)
CB.QuotePrefix = "["
CB.QuoteSuffix = "]"

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()
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
End If

'Tidy up
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


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.
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 = 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")
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


'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

End If

'Tidy Up
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!!

Martley, Near Worcester, UK.
Nov 21 '05
11 2264

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?

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

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

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)



Nov 21 '05 #11

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.


Nov 21 '05 #12

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

Similar topics

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...
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...
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"))
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
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.
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
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();
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?
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
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,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
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...
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();...
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...
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
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.