Re: What are OleDbParameter names with 'Original_' prefix?

Well, no, you don't have to call the DataAdapter/TableAdapter Update on each
change--these are cached in memory. When you execute Update, ADO.NET walks
through the DataTable and posts all of the changes (one at a time--unless
you have batch mode enabled)--but you've already figured that out... ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"JoWilliam" <XjohnXwilliams_Xesq@xxxxxxxxxxxx> wrote in message
news:eo333g$ug$1@xxxxxxxxxxxxxxxxxxxxxxxx
Bill, thanks for your further explanation.

In a multi-user environment, the Update method expects to update one and
only one row.

Does that mean I should call the Update method each time I update a row?

Similarly, should I call Update each time I Add or Remove a row from a
DataTable?

I've been adding 20 records at a time to my DataSet/DataTable and then
calling the Update method and it seems to work fine.

thanks,


"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
news:ODp34lBNHHA.3872@xxxxxxxxxxxxxxxxxxxxxxx
In a single-user environment you're absolutely correct--well, almost (but
I'll get to that).
In a multi-user environment, the Update method expects to update one and
only one row. The PK reference deals with that issue (as you said).
However, the Update method also expects to update the row you originally
read--it is programmed to fail if the row has changed since it was last
read. That's what the additional WHERE clause elements do. They compare
the original row data with the current contents of the row. This is a
crude way to test to see if any changes have been made. As I discuss at
length in my book, there are better ways to make this test, this approach
assumes that you have rights to the other columns and many other
(troubling) factors.

As to the single-user issue: It's entirely possible to open more than one
connection on a database from an application and the app can attempt to
update the same row from more than one part of the logic. This makes the
single user application work like (and need the protections of) a
multiuser app.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"JoWilliam" <XjohnXwilliams_Xesq@xxxxxxxxxxxx> wrote in message
news:eo00m9$4nb$1@xxxxxxxxxxxxxxxxxxxxxxxx
Hi and thanks very much for your reply.

The Data Adapter wizard generated the following UpdateCommand:

UPDATE Questions SET Reference = ?, Status = ?, DateCreated = ? WHERE
(Reference = ?) AND (DateCreated = ? OR ? IS NULL AND DateCreated IS
NULL) AND (Status = ? OR ? IS NULL AND Status IS NULL)

Since the table has a primary key column (Reference), the UPDATE command
doesn't need to include it in the SET list and the WHERE clause doesn't
need the other columns. Therefore I can simplify the command as:

UPDATE Questions SET Status = ?, DateCreated = ? WHERE (Reference = ?)

Is my reasoning correct, and is there anything else I need to add/change
to support this new UPDATE statement?

thanks,

"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
news:OLasZPpMHHA.420@xxxxxxxxxxxxxxxxxxxxxxx
This generated code is used to manage update concurrency--to determine
if the row has changed since last accessed. The "original" values are
compared (in the UPDATE) to see if the current values match the
original values. If they do, the UPDATE succeeds and returns 1 row
affected, if not it fails and the Update method throws an exception.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"JoWilliam" <XjohnXwilliams_Xesq@xxxxxxxxxxxx> wrote in message
news:enr2ch$69v$1@xxxxxxxxxxxxxxxxxxxxxxxx
Hi, I'm new to all this OleDb stuff. I'm using VB in Visual Studio
.Net 2003.

The Data Adapter wizard for the OleDbDataAdapter control generates the
following OleDbUpdateCommand object for the query "SELECT Reference,
Status, DateCreated FROM Questions":

Me.OleDbUpdateCommand1.CommandText = "UPDATE Questions SET
Reference = ?, Status = ?, DateCreated = ? WHERE (Reference " & _
"= ?) AND (DateCreated = ? OR ? IS NULL AND DateCreated IS
NULL) AND (Status = ? " & _
"OR ? IS NULL AND Status IS NULL)"
Me.OleDbUpdateCommand1.Connection = Me.dbConn
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Reference",
System.Data.OleDb.OleDbType.VarWChar, 13, "Reference"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Status",
System.Data.OleDb.OleDbType.VarWChar, 20, "Status"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("DateCreated",
System.Data.OleDb.OleDbType.DBDate, 0, "DateCreated"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Reference",
System.Data.OleDb.OleDbType.VarWChar, 13,
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_DateCreated",
System.Data.OleDb.OleDbType.DBDate, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "DateCreated", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_DateCreated1",
System.Data.OleDb.OleDbType.DBDate, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "DateCreated", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Status",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "Status", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Status1",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "Status", System.Data.DataRowVersion.Original, Nothing))

What are the 2 parameters with prefix 'Original_' and suffix '1' used
for?

What are the other 3 parameters prefixed by 'Original_' used for? Are
they used to hold the previous value of the column in case the update
needs to be rolled back?

The table has one primary key column, Reference. Therefore I can
change the update command to:

UPDATE Questions SET Status = ?, DateCreated = ? WHERE (Reference = ?)

If I do this, can I delete the Original_DateCreated1 and
Original_Status1 parameters in the above code?

thanks,











.

Relevant Pages

  • Re: ADO.NET Transaction
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... Client transactions put too much schema-dependent, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Performance problems with OracleDataReader
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... referencing the columns by ordinal is far faster than using strings. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Very slow
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ...
    (microsoft.public.sqlserver.ce)
  • Re: ADO.NET Transaction
    ... The SqlBulkCopy class can take anything you can expose with a DataReader and send it to SQL Server in a single high-speed operation. ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... Client transactions put too much schema-dependent, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: What are OleDbParameter names with Original_ prefix?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... following OleDbUpdateCommand object for the query "SELECT Reference, ... The table has one primary key column, ...
    (microsoft.public.dotnet.framework.adonet)