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



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" <garrygrolman@xxxxxxxxx> wrote in message
news:OkNKWMBNHHA.3952@xxxxxxxxxxxxxxxxxxxxxxx
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: Standby Operation Master
    ... command line) but when I was checking on the GUI it was still pointing to the ... I didn't know where to check if the operation is successful or not ... That means there is no Standby Operation Master ...
    (microsoft.public.win2000.active_directory)
  • Re: hind indexes
    ... this command will say it's successful whether or not it ... > Thanks Andrew, ... > IF EXISTS (SELECT name FROM sysindexes ...
    (microsoft.public.sqlserver.programming)
  • Re: Automatic Reboot Server 2003
    ... But I know that "shutdown /r" works because when I have unlocked the server ... and run that command it reboots successfully no problem. ... > There may be processes or prompts preventing a successful shutdown. ... I am trying to set up an automatic reboot of our 2003 ...
    (microsoft.public.windows.server.general)
  • Re: Kerberos 5 v1.5.1 on AIX 5.2 or AIX 5.3
    ... Configure is successful with the following flags: ... 1254-004 The error code from the last command is 1. ... Same errors on AIX 5.2 as well as AIX 5.3. ...
    (comp.protocols.kerberos)