Access Database Updates

[Joseph Hanna]

Has anyone successfully used a Jet 4.0 Database with updates?  I simply cannot get updates working without "Concurrency Exceptions" being raised.

 

I have a basic form with a Data Bound Grid control.  I have used the IDE to create a Connection, DataAdapter and Dataset.

 

Here is some code:

 

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.OleDbDataAdapter1.Fill(Me.DataSet11)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Me.OleDbDataAdapter1.Update(Me.DataSet11)

End Sub

When I change some data in the Grid and click the Button to run the Update for the Data Adapter, I get the following exception:

 

"An Unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

 

Additional information:  Concurrency violation:  the UpdateCommand affected 0 records."

 

 

* I am using this DB exclusively - no one or any other process has this DB open

* My database has worked perfectly with VB6 and ADO 2.7

* My table is defined with an Autonumber Primary Key field

* Under Access, database is configured for Shared Open and Edited Record locking

* The UPDATE Command for the Data Adapter looks ok, with the PK included.

* This is a test form above, I was getting the same issues in my main application

* I got a sample app working with the Northwind Access Database.  What is the difference here? Am I missing a setting somewhere?

 

Environment:  Win2K Pro, VS.NET 2002, Jet 4.0 (Access 2000 format)

 

Thanks in advance

--

Joe Hanna
Advanced Software Engineering
joe.hanna_AT_advancedsoftware_DOT_net_DOT_au

 

 

[Joseph Hanna]

One more thing.

 

If I change the Connection string at runtime to point an SQL Server, the update works fine without changing anything else.

 

Thanks,

Joe

"Joseph Hanna" <zo...@msdn.com> wrote in message news:Ox4TxfUM...@TK2MSFTNGP11.phx.gbl...

[Kevin Sun [MS]]

Please refer to the following update code:

==============
'Retrieve the contents of the order into a DataTable.
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim tbl As New DataTable("Order Details")
da.Fill(tbl)

'Modify the contents of the order.
tbl.Rows(0).Delete()
tbl.Rows(1)("Quantity") = CShort(tbl.Rows(1)("Quantity")) * 2
tbl.Rows.Add(New Object() {10503, 1, 24, 18})

'Submit the pending changes.
Try
da.Update(tbl)
Console.WriteLine("Successfully submitted new changes")
Catch ex As Exception
Console.WriteLine("Call to DataAdapter.Update " & _
"threw exception:" & vbCrLf & ex.Message)
End Try
==================

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "Joseph Hanna" <zo...@msdn.com>
| Subject: Access Database Updates
| Date: Fri, 13 Jun 2003 11:05:53 +1000
| Lines: 171
| MIME-Version: 1.0
| Content-Type: multipart/alternative;
| boundary="----=_NextPart_000_024C_01C3319B.C1632CE0"
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <Ox4TxfUM...@TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: cpe-144-132-177-28.nsw.bigpond.net.au 144.132.177.28
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:53361
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

[Joseph Hanna]

Thanks Kevin, but I do not have a problem with SQL Server using both the SQL Data Adaptor and the
Ole DB Data Adaptor. My problem is updating an Access Database (Jet 4).

--

Joe Hanna
Advanced Software Engineering
joe.hanna_AT_advancedsoftware_DOT_net_DOT_au

"Kevin Sun [MS]" <zhi...@onlinemicrosoft.com> wrote in message
news:$TZS1bYM...@cpmsftngxa06.phx.gbl...

[Joseph Hanna]

I have some new test results. The Update fails on all of my Access tables that contain a Date/Time
field. ??? Have I configured something incorrectly or did someone forget to test this?

I am now considering storing all dates as a Double data type. This will also provide me with enough
power to be database independant using the OleDb provider, as querying a table with a Date/Time
qualifier requires different syntax for different Db vendors.

Does anyone know of any issues with this?

--

Joe Hanna
Advanced Software Engineering
joe.hanna_AT_advancedsoftware_DOT_net_DOT_au

"Joseph Hanna" <zo...@msdn.com> wrote in message news:OCI7bDZM...@TK2MSFTNGP11.phx.gbl...

[Ron Allen]

Joseph,
You may be using a reserved word for a field name which would prevent
updates. I know that Date is a reserved word so it needs to be quoted as
[Date] or `Date`. If you are using a CommandBuilder to generate the Update
command you can set the QuotePrefix and QuoteSuffix characters before
generating the commands otherwise do it manually.

Ron Allen

"Joseph Hanna" <zo...@msdn.com> wrote in message

news:ObbP8C1M...@tk2msftngp13.phx.gbl...

[Joseph Hanna]

Thanks for your reply Ron.

I am using obsure names for the Date/Time fields that could not be mistaken for reserved words such
as EMP_START_DATE. All of my fields start with a 3 letter code describing the table.

Also, I am not using CommandBuilder, directly anyway.

I have set up a test case where I drag the table from the Server Explorer on to the Form Designer
and Generate a Dataset, add a Data Grid and bind it in the IDE. Form Load Fills the Data Adapter,
and a Button is added to do the Update. Make a change in the Data Grid and click the button -
DBConcurrencyException

--

Joe Hanna
Advanced Software Engineering
joe.hanna_AT_advancedsoftware_DOT_net_DOT_au

"Ron Allen" <ral...@src-us.com> wrote in message news:O%23lNDXA...@TK2MSFTNGP11.phx.gbl...

[Ron Allen]

Joseph,
The only time I've had a problem like this it was due to an error in a
data column that was in an error state just before the update. You may want
to insert a breakpoint and examine the DataTable Rows collection and look at
the state of each. The other problem that would genereate a concurrency
error is doing an update with no changed rows as this will return 0 rows
affected.
Another thing that I'd try is to look at the UpdateStatement's
CommandText for the DataAdapter used as it may be doing concurrency checking
against original data that you don't want.
I have a deployed application that is currently running against an
Access database with 48 tables and updates, etc are working fine. I do
generate most of my DataAdapter statements by hand though with the exception
of a few simple tables that I let a CommandBuilder generate statements for.

Ron Allen
"Joseph Hanna" <zo...@msdn.com> wrote in message

news:ufxk5MIN...@TK2MSFTNGP12.phx.gbl...

[Joseph Hanna]

I did all that you suggested and it all checks out.

Once I removed the Date/Time fields from the tables all went ok. I have decided to convert the
Dates to Double before storing them. Sounds like a Hack but hey - it works and I cannot afford
anymore time mucking arounding trying to get it going.

Thanks for your time and effort.

Joe

--

Joe Hanna
Advanced Software Engineering
joe.hanna_AT_advancedsoftware_DOT_net_DOT_au

"Ron Allen" <ral...@src-us.com> wrote in message news:%23lmyHCN...@TK2MSFTNGP12.phx.gbl...

[Obe One]

Joseph,

I have exactly the same problem has you have.

The reason is that the ACCESS oleDB provider truncates the TIME information
of a DATE/TIME field and preserves only the DATE. This mean that it is
unable to find back any modified records that contains a DATETIME field.

This is definetively a bug and I wonder why it has not been fixed yet as it
makes ADO.NET unusable with ACCESS databases.

-- Obe.

"Joseph Hanna" <zo...@msdn.com> wrote in message

news:%23$iSKwdND...@TK2MSFTNGP10.phx.gbl...

[Obe One]

See also the topic "Can't get datetime value saved in access database" in
this forum