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

I've finally sussed it.

The Update command and parameters as generated by the designer were:

Me.OleDbUpdateCommand1.CommandText = "UPDATE Table1 SET Reference =
?, Name = ? WHERE (Reference = ?) AND (Name = ? OR " & _
"? IS NULL AND Name IS NULL)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Reference",
System.Data.OleDb.OleDbType.VarWChar, 10, "Reference"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Name",
System.Data.OleDb.OleDbType.VarWChar, 50, "Name"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Reference",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Reference", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Name",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Name", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Name1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Name", System.Data.DataRowVersion.Original, Nothing))

I changed the command and deleted 3 of the parameters:

Me.OleDbUpdateCommand1.CommandText = "UPDATE Table1 SET Name = ?
WHERE (Reference = ?)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Name",
System.Data.OleDb.OleDbType.VarWChar, 50, "Name"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Reference",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Reference", System.Data.DataRowVersion.Original, Nothing))

and now the OleDbDataAdapter.Update method works successfully when updating
an existing record.

"JoWilliam" <XjohnXwilliams_Xesq@xxxxxxxxxxxx> wrote in message
news:envuc3$3ef$1@xxxxxxxxxxxxxxxxxxxxxxxx
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







.