Regarding Update method of ADO Recordset object 
Author Message
 Regarding Update method of ADO Recordset object

Hi,

I have a recordset opened using a query "select * from tablename where
primarykeyfld='" & somevalue & "'"

I loop through the recordset's fields collection and assign the values to
the fields, like this:
(I can also do general validation in this loop before assigning the value to
the field)

for each fld in rsTmp.Fields
    fld.value = GetNewValueFor(fld.Name)
next fld

[GetNewValueFor function returns the value of the field name passed to it]

Then, before calling update method, in order to log what fields were changed
from what value
to what value, I go through the fields collection again and write the
changed fields and their old
and new values, like this:

for each fld in rsTmp.Fields
    if fld.originalvalue <> fld.value then
        debug.print fld.name & " changed from " & fld.originalvalue & " to "
& fld.value
    endif
next fld
rsTmp.update

And then I call update method of the recordset to update the values.
Ok, now my question is. What happens if the above select query contains the
primary key field (which it will, if I give '*')  And that primary key
field's value is also
returned by the function 'GetNewValueFor' (note: the value of that primary
key field never changes)
and assigned to the field's value.

Does the call to Update method tries to update the primary key field value
also, thereby locking the
table? It looks like that this happens. (How do I make sure that this is the
case, if it is one?)

This is a part of generalized validate-log-update procedure, used for
updating records at other places
in the program.

Another option is to prepare the UPDATE sql statement for changed fields and
call an Execute method
of the connection or a command object.

The ADO connection and recordsets are opened against an Oracle8 database and
the application
is made with VB6+SP4+ADO2.5

Please guide me in this regard, I would be thankful to you.

Thanks for your time.



Fri, 23 May 2003 03:00:00 GMT  
 Regarding Update method of ADO Recordset object
Sorry, if I missed something, I'm rushing here.

But I think if you attempt to Update primary keys, it would depend on how
the ADO Provider would handle this.  That said, I don't know how Oracle
handles this.  An update to primary keys is usually equivalent to a delete
and insert, so some Providers probably allow this, and some don't.  Depends
on how nice the Provider is to you.  But if the recordset didn't allow this
to happen, you would probably get some sort of error.

So if you get an error doing an Update, do a Delete on the current record
(old Values), and a AddNew with the new values.

Quote:

> Hi,

> I have a recordset opened using a query "select * from tablename where
> primarykeyfld='" & somevalue & "'"

> I loop through the recordset's fields collection and assign the values to
> the fields, like this:
> (I can also do general validation in this loop before assigning the value
to
> the field)

> for each fld in rsTmp.Fields
>     fld.value = GetNewValueFor(fld.Name)
> next fld

> [GetNewValueFor function returns the value of the field name passed to it]

> Then, before calling update method, in order to log what fields were
changed
> from what value
> to what value, I go through the fields collection again and write the
> changed fields and their old
> and new values, like this:

> for each fld in rsTmp.Fields
>     if fld.originalvalue <> fld.value then
>         debug.print fld.name & " changed from " & fld.originalvalue & " to
"
> & fld.value
>     endif
> next fld
> rsTmp.update

> And then I call update method of the recordset to update the values.
> Ok, now my question is. What happens if the above select query contains
the
> primary key field (which it will, if I give '*')  And that primary key
> field's value is also
> returned by the function 'GetNewValueFor' (note: the value of that primary
> key field never changes)
> and assigned to the field's value.

> Does the call to Update method tries to update the primary key field value
> also, thereby locking the
> table? It looks like that this happens. (How do I make sure that this is
the
> case, if it is one?)

> This is a part of generalized validate-log-update procedure, used for
> updating records at other places
> in the program.

> Another option is to prepare the UPDATE sql statement for changed fields
and
> call an Execute method
> of the connection or a command object.

> The ADO connection and recordsets are opened against an Oracle8 database
and
> the application
> is made with VB6+SP4+ADO2.5

> Please guide me in this regard, I would be thankful to you.

> Thanks for your time.



Fri, 23 May 2003 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Regarding Update method of ADO Recordset object

2. ADO recordset Update method BUG

3. Using the .Update method with and ADO recordset

4. Transaction and update-method of the recordset object

5. ADO Recordset Object, Seek Method

6. ADO Recordset Object, Seek Method

7. ADO Recordset Object, Seek Method

8. ? regarding UPDATING a recordset (VB5 - bound)...

9. Bound controls not updating ADO with adodc.recordset.update command

10. Problem with recordset's Update method

11. 2147467259 Recordset Update Method (SQL Server)

12. Update: Problems using AddNew method of Recordset

 

 
Powered by phpBB® Forum Software