"JoWilliam" <XjohnXwilliams_X***@Xhotmail.com> wrote in message
news:enr2ch$69v$1@news.freedom2surf.net...
> 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,
>

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:OLasZPpMHHA.420@TK2MSFTNGP06.phx.gbl...
> 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_X***@Xhotmail.com> wrote in message
> news:enr2ch$69v$1@news.freedom2surf.net...
>> 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,
>>
>
>

"JoWilliam" <XjohnXwilliams_X***@Xhotmail.com> wrote in message
news:eo00m9$4nb$1@news.freedom2surf.net...
> 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" <billvaRemoveT***@nwlink.com> wrote in message
> news:OLasZPpMHHA.420@TK2MSFTNGP06.phx.gbl...
>> 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_X***@Xhotmail.com> wrote in message
>> news:enr2ch$69v$1@news.freedom2surf.net...
>>> 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,
>>>
>>
>>
>
>

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:ODp34lBNHHA.3872@TK2MSFTNGP06.phx.gbl...
> 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_X***@Xhotmail.com> wrote in message
> news:eo00m9$4nb$1@news.freedom2surf.net...
>> 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" <billvaRemoveT***@nwlink.com> wrote in message
>> news:OLasZPpMHHA.420@TK2MSFTNGP06.phx.gbl...
>>> 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_X***@Xhotmail.com> wrote in message
>>> news:enr2ch$69v$1@news.freedom2surf.net...
>>>> 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,
>>>>
>>>
>>>
>>
>>
>
>

"JoWilliam" <XjohnXwilliams_X***@Xhotmail.com> wrote in message
news:eo333g$ug$1@news.freedom2surf.net...
> 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" <billvaRemoveT***@nwlink.com> wrote in message
> news:ODp34lBNHHA.3872@TK2MSFTNGP06.phx.gbl...
>> 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_X***@Xhotmail.com> wrote in message
>> news:eo00m9$4nb$1@news.freedom2surf.net...
>>> 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" <billvaRemoveT***@nwlink.com> wrote in message
>>> news:OLasZPpMHHA.420@TK2MSFTNGP06.phx.gbl...
>>>> 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_X***@Xhotmail.com> wrote in message
>>>> news:enr2ch$69v$1@news.freedom2surf.net...
>>>>> 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,
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>