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

  • Thread starter Thread starter JoWilliam
  • Start date Start date
J

JoWilliam

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
 
Perhaps you can post some of the code otherwise it is very difficult to try
to help.



Garry
 
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 said:
Perhaps you can post some of the code otherwise it is very difficult to
try to help.



Garry


JoWilliam said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
Perhaps you can post some of the code otherwise it is very difficult to
try to help.



Garry


JoWilliam said:
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
Click to expand...
Click to expand...
Click to expand...
 
You might like to view the code that I wrote which uses the CommandBuilder
object and simplifies the code for a Windows application.

However, there may be overheads relative to your detailed code.
Notice the use of the CommandBuilder object
Notice that the DataTable(dt) is already in existance

Public Function UpdateTable(ByRef dt As DataTable, ByVal strSQL As String)
As Boolean

Dim cmd As New OleDbCommand
Dim myBuilder As OleDbCommandBuilder
Dim daOLEDB As OleDbDataAdapter

If gconPlan.State = ConnectionState.Closed Then
gconPlan.Open()
End If

cmd.CommandText = strSQL
cmd.Connection = gconPlan
daOLEDB = New System.Data.OleDb.OleDbDataAdapter(cmd)
myBuilder = New System.Data.OleDb.OleDbCommandBuilder(daOLEDB)
daOLEDB.UpdateCommand = myBuilder.GetUpdateCommand()
daOLEDB.Update(dt)

End Function

The strSql contains the original SQL string which returned the DataTable
from the .Fill(dt) method.
The code is however, more robust than the detailed code in your version.
The myBuilder.GetUpdateCommand() will fail if the SQL contains a JOIN. This
is a serious limitation BUT works otherwise
I call this function whenever a row on the grid is changed/edited OR the
form is closing, to update the source database file.
If you use it otherwise, you may be required to use dt.AcceptChanges.
Otherwise you may get an error if you have done an 'ADD' or 'DELETE' to the
DataTable.
Mine is not a DataBound application.
I 'concocted' the code from various examples that I came across using Google
etc
Bottom line - it works and suprisingly - seems efficient.
If you have any comments/improvements - I will be happy to consider them. I
iz a learner too.

Garry

JoWilliam said:
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 said:
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 said:
Perhaps you can post some of the code otherwise it is very difficult to
try to help.



Garry


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
Click to expand...
Click to expand...
Click to expand...
 
Garry, thanks for your reply. I used your UpdateTable routine with my code
and it works successfully.

It generates the UpdateCommand each time it's called and because the command
doesn't change for the same table, one improvement would be to call
OleDbCommandBuilder outside it and pass the output of
OleDbCommandBuilder.GetUpdateCommand as a parameter.

Another thing is that the UPDATE command generated by the command builder is
a little odd:

UPDATE Table1 SET Reference = ? , Name = ? , DateCreated = ? WHERE (
(Reference = ?) AND ((? = 1 AND Name IS NULL) OR (Name = ?)) AND ((? = 1 AND
DateCreated IS NULL) OR (DateCreated = ?)) )

I don't understand what the '? = 1' parts are for.

cheers,

Garry said:
You might like to view the code that I wrote which uses the CommandBuilder
object and simplifies the code for a Windows application.

However, there may be overheads relative to your detailed code.
Notice the use of the CommandBuilder object
Notice that the DataTable(dt) is already in existance

Public Function UpdateTable(ByRef dt As DataTable, ByVal strSQL As String)
As Boolean

Dim cmd As New OleDbCommand
Dim myBuilder As OleDbCommandBuilder
Dim daOLEDB As OleDbDataAdapter

If gconPlan.State = ConnectionState.Closed Then
gconPlan.Open()
End If

cmd.CommandText = strSQL
cmd.Connection = gconPlan
daOLEDB = New System.Data.OleDb.OleDbDataAdapter(cmd)
myBuilder = New System.Data.OleDb.OleDbCommandBuilder(daOLEDB)
daOLEDB.UpdateCommand = myBuilder.GetUpdateCommand()
daOLEDB.Update(dt)

End Function

The strSql contains the original SQL string which returned the DataTable
from the .Fill(dt) method.
The code is however, more robust than the detailed code in your version.
The myBuilder.GetUpdateCommand() will fail if the SQL contains a JOIN.
This is a serious limitation BUT works otherwise
I call this function whenever a row on the grid is changed/edited OR the
form is closing, to update the source database file.
If you use it otherwise, you may be required to use dt.AcceptChanges.
Otherwise you may get an error if you have done an 'ADD' or 'DELETE' to
the DataTable.
Mine is not a DataBound application.
I 'concocted' the code from various examples that I came across using
Google etc
Bottom line - it works and suprisingly - seems efficient.
If you have any comments/improvements - I will be happy to consider them.
I iz a learner too.

Garry

JoWilliam said:
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 said:
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,

Perhaps you can post some of the code otherwise it is very difficult to
try to help.



Garry


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
Click to expand...
Click to expand...
Click to expand...
 
Jo Hi,

What you say about the Update Command is true BUT, it is only implemented
when the user closes the form.

The great thing about this is that as the user edits the grid, I update the
data table dynamically on the ChangeCell event, and only when he has
finished and closes the form, the data adapter 'knows' which datarows in the
datatable have been updated, deleted or added and performs the update with
great efficiency. This was probably developed for INTERNET updating but
makes windows applications updating efficient. Only the changes are taken
written back to the database file.

It took a while for me to understand this coming from VB6.

Also, I originally tried to use SQLCE BUT, because of the absence of easily
available tools to display file data and alter file definitions, I returned
to Access. The conversion from existing SQLCE code to Access code (OLEDB)
was fantastically easy with a few simple 'Replaces' and it immediately
worked. No SQL was changed. Just, for instance - REPLACE
Data.SQLCE.SQLCEDataAdapter with Data.OleDb.OleDbDataAdapter. It
demonstrates that the DataTable and other ado.net objects are completely
independant of the source database.

I mention this as SQLCE was very fast and Microsoft wants us to leave OLEDB
and MSAccess and move to SQLCE. Also SQLCE is completely suported by ADO.NET
However, it is an example of a microsoft product that wasn't completely
cooked before being released to general programming.

I wish you success

Garry
 
Back
Top