Re: Correct way of using OleDbAdapter and DataSet to update Access database?

I've got a little further and think I need to use the DataTable.Rows.Find
method to determine whether the record exists or not. If it doesn't, add a
new record, otherwise update the existing record.

Here is the test code:

Dim myDataRow As DataRow
Dim myDataTable As DataTable

OleDbDataAdapter1.Fill(DataSet11, "Table1")
myDataTable = DataSet11.Tables("Table1")

myDataRow = myDataTable.Rows.Find("1")

If myDataRow Is Nothing Then

'Add new record

myDataRow = myDataTable.NewRow
myDataRow("Reference") = "1"
myDataRow("Name") = "A"
myDataTable.Rows.Add(myDataRow)

Else

'Record already exists, so just update the name

myDataRow("Name") = myDataRow("Name") + "B"

End If

Try
OleDbDataAdapter1.Update(DataSet11, "Table1")
Catch ex As Exception
Debug.WriteLine(ex.ToString)
End Try

Adding the new record works successfully. However updating fails at the
OleDbDataAdapter Update call with:

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand
affected 0 records.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

thanks,

"Garry" <garrygrolman@xxxxxxxxx> wrote in message
news:OOD6Xc9MHHA.3312@xxxxxxxxxxxxxxxxxxxxxxx
Perhaps you can post some of the code otherwise it is very difficult to
try to help.



Garry


"JoWilliam" <XjohnXwilliams_Xesq@xxxxxxxxxxxx> wrote in message
news:enrn2c$ilc$1@xxxxxxxxxxxxxxxxxxxxxxxx
Hi,

I've written a VB .Net program (written in Visual Studio .Net 2003) which
scrapes data from a web site and stores it in an Access database. The
database has 1 table with 11 columns, one of which is the primary key
(not an auto-increment field).

The program first calls OleDbDataAdapter.Fill to populate a DataSet with
6 of the columns from the Access db. It then adds data from the web page
to this DataSet, using DataTable.Rows.Add. When it has parsed the whole
page (20 records), it calls OleDbDataAdapter.Update with the DataSet and
then continues with the next web page.

This works fine when I start with an empty Access database. However,
when I run the program again (on the same web page and the data on it
hasn't changed), each call to the Update method causes an exception which
I trap:

System.Data.OleDb.OleDbException: The changes you requested to the table
were not successful because they would create duplicate values in the
index, primary key, or relationship. Change the data in the field or
fields that contain duplicate data, remove the index, or redefine the
index to permit duplicate entries and try again.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

I'd rather than not use a Try... Catch... End Try block to prevent this
exception from crashing my program, and reckon that the Update method
should be able to work out which rows have changed and which haven't.
Starting with an empty database, on the 2nd run of the program the
dataset contains 158 records. The first 79 of these are the ones read
from the Access db using the initial .Fill call and have a RowState of
Unchanged; the next 79 are the ones read from the web site (identical to
the first 79) and have a RowState of Added. This is obviously where the
problem originates and I think the DataSet should only contain 79 records
since the primary keys of the first 79 are identical to the second 79,
but somehow maybe I haven't told the DataAdapter which is the primary key
column so it can't resolve the dataset.

I must have missed something or coded something incorrectly (this is my
first OleDb program!), so would appreciate any advice.

thanks, JW





.

Relevant Pages

  • Re: Correct way of using OleDbAdapter and DataSet to update Access database?
    ... "Reference", System.Data.DataRowVersion.Original, Nothing)) ... Dim myDataRow As DataRow ... This works fine when I start with an empty Access database. ... primary key column so it can't resolve the dataset. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Correct way of using OleDbAdapter and DataSet to update Access database?
    ... scrapes data from a web site and stores it in an Access database. ... This works fine when I start with an empty Access database. ... were not successful because they would create duplicate values in the ... index, primary key, or relationship. ...
    (microsoft.public.dotnet.framework.adonet)
  • Correct way of using OleDbDataAdapter and DataSet to update Access database?
    ... scrapes data from a web site and stores it in an Access database. ... database has 1 table with 11 columns, one of which is the primary key (not ... This works fine when I start with an empty Access database. ... were not successful because they would create duplicate values in the index, ...
    (microsoft.public.data.oledb)
  • Correct way of using OleDbAdapter and DataSet to update Access database?
    ... scrapes data from a web site and stores it in an Access database. ... This works fine when I start with an empty Access database. ... were not successful because they would create duplicate values in the index, ... told the DataAdapter which is the primary key column so it can't resolve the ...
    (microsoft.public.dotnet.framework.adonet)
Loading