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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <XjohnXwilliams_Xesq@xxxxxxxxxxxx> wrote in message
news:eo067u$80i$1@xxxxxxxxxxxxxxxxxxxxxxxx
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









.

Relevant Pages

  • Re: How I can sysnchronize changes in DataTable with my Database?
    ... I have removed Filland AcceptChanges. ... valid InsertCommand when passed DataRow collection with new rows. ... DataTableMapping tableMapping) ... foreach(DataRow myDataRow in myDataTable.Rows) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Identity
    ... however proces the select with the executescalar ... if i use DataSet, SqlDataAdapter and CommandBuilder? ... MyDataRow = MyDataSet.Tables.NewRow ...
    (microsoft.public.dotnet.languages.vb)
  • Identity
    ... if i use DataSet, SqlDataAdapter and CommandBuilder? ... MyDataRow = MyDataSet.Tables.NewRow ... Dim cb As New SqlCommandBuilder ... With best regards; ...
    (microsoft.public.dotnet.languages.vb)