ADO update problem 
Author Message
 ADO update problem

I have a number of client-side recordsets that have been disconnected from
their connection object (adLockOptimistic, adUseClient).  (There is only 1
connection object per application.)  Here is the problem:  I do a
Connection.Execute statement to insert a new record into a table.  I then
call a stored procedure to retrieve the IDENTITY value of the new record.
This stored procedure is executed by creating a Command object, associating
it with the single Connection object, and calling Execute (adCmdStoredProc +
adExecuteNoRecords).  Following this stored procedure call, I reconnect one
of my recordset objects with the Connection object and call Update to save
changes.

I have a couple of problems:

1) The IDENTITY value returned is always zero.  I can see where this might
happen because ADO will create additional connections as needed if the
current connection is busy.

2) My call to Recordset.Update throws an exception (_com_error).  The most
common HRESULT value is 0x800a0cc1.  The error description is, "Item cannot
be found in the collection corresponding to the requested name or ordinal.".
I have actually seen this error message before when I tried to access beyond
the end of the Fields collection, only why would I get this error on the
Update?  I set all of my field values without any errors!

The really wierd part is that the update actually succeeds, since I check my
database and everything looks fine!

Aaaaargh!!!!!

Does anyone know of a bug in ADO that would cause this behavior?  I have
found bug reports similar to this (same HRESULT, same error message), but
none of the fixes seem to help.

Any insight would be GREATLY appreciated.
--
=========================
Brian D. Archer
Senior Software Engineer
(314)692-0202 x227
=========================
PC Innovators Consulting
www.pcinnovators.com
11856 Lackland Rd.
St. Louis, MO 63143
=========================



Sat, 25 Jan 2003 03:00:00 GMT  
 ADO update problem

Ok, I thought the database was getting updated, but it really isn't.
Anyway, the Update command should work according to everything I've read
about ADO.


Quote:
> I have a number of client-side recordsets that have been disconnected from
> their connection object (adLockOptimistic, adUseClient).  (There is only 1
> connection object per application.)  Here is the problem:  I do a
> Connection.Execute statement to insert a new record into a table.  I then
> call a stored procedure to retrieve the IDENTITY value of the new record.
> This stored procedure is executed by creating a Command object,
associating
> it with the single Connection object, and calling Execute (adCmdStoredProc
+
> adExecuteNoRecords).  Following this stored procedure call, I reconnect
one
> of my recordset objects with the Connection object and call Update to save
> changes.

> I have a couple of problems:

> 1) The IDENTITY value returned is always zero.  I can see where this might
> happen because ADO will create additional connections as needed if the
> current connection is busy.

> 2) My call to Recordset.Update throws an exception (_com_error).  The most
> common HRESULT value is 0x800a0cc1.  The error description is, "Item
cannot
> be found in the collection corresponding to the requested name or
ordinal.".
> I have actually seen this error message before when I tried to access
beyond
> the end of the Fields collection, only why would I get this error on the
> Update?  I set all of my field values without any errors!

> The really wierd part is that the update actually succeeds, since I check
my
> database and everything looks fine!

> Aaaaargh!!!!!

> Does anyone know of a bug in ADO that would cause this behavior?  I have
> found bug reports similar to this (same HRESULT, same error message), but
> none of the fixes seem to help.

> Any insight would be GREATLY appreciated.
> --
> =========================
> Brian D. Archer
> Senior Software Engineer
> (314)692-0202 x227
> =========================
> PC Innovators Consulting
> www.pcinnovators.com
> 11856 Lackland Rd.
> St. Louis, MO 63143
> =========================



Sat, 25 Jan 2003 03:00:00 GMT  
 ADO update problem
You should be using adLockBatchOptimistic


Quote:
> Ok, I thought the database was getting updated, but it really isn't.
> Anyway, the Update command should work according to everything I've read
> about ADO.



> > I have a number of client-side recordsets that have been disconnected
from
> > their connection object (adLockOptimistic, adUseClient).  (There is only
1
> > connection object per application.)  Here is the problem:  I do a
> > Connection.Execute statement to insert a new record into a table.  I
then
> > call a stored procedure to retrieve the IDENTITY value of the new
record.
> > This stored procedure is executed by creating a Command object,
> associating
> > it with the single Connection object, and calling Execute
(adCmdStoredProc
> +
> > adExecuteNoRecords).  Following this stored procedure call, I reconnect
> one
> > of my recordset objects with the Connection object and call Update to
save
> > changes.

> > I have a couple of problems:

> > 1) The IDENTITY value returned is always zero.  I can see where this
might
> > happen because ADO will create additional connections as needed if the
> > current connection is busy.

> > 2) My call to Recordset.Update throws an exception (_com_error).  The
most
> > common HRESULT value is 0x800a0cc1.  The error description is, "Item
> cannot
> > be found in the collection corresponding to the requested name or
> ordinal.".
> > I have actually seen this error message before when I tried to access
> beyond
> > the end of the Fields collection, only why would I get this error on the
> > Update?  I set all of my field values without any errors!

> > The really wierd part is that the update actually succeeds, since I
check
> my
> > database and everything looks fine!

> > Aaaaargh!!!!!

> > Does anyone know of a bug in ADO that would cause this behavior?  I have
> > found bug reports similar to this (same HRESULT, same error message),
but
> > none of the fixes seem to help.

> > Any insight would be GREATLY appreciated.
> > --
> > =========================
> > Brian D. Archer
> > Senior Software Engineer
> > (314)692-0202 x227
> > =========================
> > PC Innovators Consulting
> > www.pcinnovators.com
> > 11856 Lackland Rd.
> > St. Louis, MO 63143
> > =========================



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem

I don't think so.  The ADO documentation says to use adLockBatchOptimistic
if you plan on calling UpdateBatch to do batch updates from your recordset
object.  I (always) call Update on my recordset object, so I don't see how
this would help.  If there is a good reason to try this, please explain it
to me.

Brian


Quote:
> You should be using adLockBatchOptimistic



> > Ok, I thought the database was getting updated, but it really isn't.
> > Anyway, the Update command should work according to everything I've read
> > about ADO.



> > > I have a number of client-side recordsets that have been disconnected
> from
> > > their connection object (adLockOptimistic, adUseClient).  (There is
only
> 1
> > > connection object per application.)  Here is the problem:  I do a
> > > Connection.Execute statement to insert a new record into a table.  I
> then
> > > call a stored procedure to retrieve the IDENTITY value of the new
> record.
> > > This stored procedure is executed by creating a Command object,
> > associating
> > > it with the single Connection object, and calling Execute
> (adCmdStoredProc
> > +
> > > adExecuteNoRecords).  Following this stored procedure call, I
reconnect
> > one
> > > of my recordset objects with the Connection object and call Update to
> save
> > > changes.

> > > I have a couple of problems:

> > > 1) The IDENTITY value returned is always zero.  I can see where this
> might
> > > happen because ADO will create additional connections as needed if the
> > > current connection is busy.

> > > 2) My call to Recordset.Update throws an exception (_com_error).  The
> most
> > > common HRESULT value is 0x800a0cc1.  The error description is, "Item
> > cannot
> > > be found in the collection corresponding to the requested name or
> > ordinal.".
> > > I have actually seen this error message before when I tried to access
> > beyond
> > > the end of the Fields collection, only why would I get this error on
the
> > > Update?  I set all of my field values without any errors!

> > > The really wierd part is that the update actually succeeds, since I
> check
> > my
> > > database and everything looks fine!

> > > Aaaaargh!!!!!

> > > Does anyone know of a bug in ADO that would cause this behavior?  I
have
> > > found bug reports similar to this (same HRESULT, same error message),
> but
> > > none of the fixes seem to help.

> > > Any insight would be GREATLY appreciated.
> > > --
> > > =========================
> > > Brian D. Archer
> > > Senior Software Engineer
> > > (314)692-0202 x227
> > > =========================
> > > PC Innovators Consulting
> > > www.pcinnovators.com
> > > 11856 Lackland Rd.
> > > St. Louis, MO 63143
> > > =========================



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem


Quote:
> I don't think so.  The ADO documentation says to use adLockBatchOptimistic
> if you plan on calling UpdateBatch to do batch updates from your recordset
> object.  I (always) call Update on my recordset object, so I don't see how
> this would help.  If there is a good reason to try this, please explain it
> to me.

You have to use adLockBatchOptimistic if you want to make changes to a
disconnected recordset and then save the changes back to the database. If
you think about it, how would the Update() call really be able to do
anything without a connection? I think if you change the locktype to
adLockBatchOptimistic and then call BatchUpdate once you're ready to save
all the changes to the database, it should work. The only other option you
have is to leave the Recordset connected to the database during its
lifetime, rather than disconnecting it.

As for your second error below, that sounds similar to an intermittent error
I was getting once that had to do with the fact that vtMissing was getting
corrupted. Make sure that you do not pass &vtMissing for the RecordsAffected
parameter of Command.Execute or Connection.Execute; you need to pass 0
instead.

Jeff

Quote:


> > You should be using adLockBatchOptimistic



> > > Ok, I thought the database was getting updated, but it really isn't.
> > > Anyway, the Update command should work according to everything I've
read
> > > about ADO.



> > > > I have a number of client-side recordsets that have been
disconnected
> > from
> > > > their connection object (adLockOptimistic, adUseClient).  (There is
> only
> > 1
> > > > connection object per application.)  Here is the problem:  I do a
> > > > Connection.Execute statement to insert a new record into a table.  I
> > then
> > > > call a stored procedure to retrieve the IDENTITY value of the new
> > record.
> > > > This stored procedure is executed by creating a Command object,
> > > associating
> > > > it with the single Connection object, and calling Execute
> > (adCmdStoredProc
> > > +
> > > > adExecuteNoRecords).  Following this stored procedure call, I
> reconnect
> > > one
> > > > of my recordset objects with the Connection object and call Update
to
> > save
> > > > changes.

> > > > I have a couple of problems:

> > > > 1) The IDENTITY value returned is always zero.  I can see where this
> > might
> > > > happen because ADO will create additional connections as needed if
the
> > > > current connection is busy.

> > > > 2) My call to Recordset.Update throws an exception (_com_error).
The
> > most
> > > > common HRESULT value is 0x800a0cc1.  The error description is, "Item
> > > cannot
> > > > be found in the collection corresponding to the requested name or
> > > ordinal.".
> > > > I have actually seen this error message before when I tried to
access
> > > beyond
> > > > the end of the Fields collection, only why would I get this error on
> the
> > > > Update?  I set all of my field values without any errors!

> > > > The really wierd part is that the update actually succeeds, since I
> > check
> > > my
> > > > database and everything looks fine!

> > > > Aaaaargh!!!!!

> > > > Does anyone know of a bug in ADO that would cause this behavior?  I
> have
> > > > found bug reports similar to this (same HRESULT, same error
message),
> > but
> > > > none of the fixes seem to help.

> > > > Any insight would be GREATLY appreciated.
> > > > --
> > > > =========================
> > > > Brian D. Archer
> > > > Senior Software Engineer
> > > > (314)692-0202 x227
> > > > =========================
> > > > PC Innovators Consulting
> > > > www.pcinnovators.com
> > > > 11856 Lackland Rd.
> > > > St. Louis, MO 63143
> > > > =========================



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem


Quote:
> As for your second error below, that sounds similar to an intermittent
error
> I was getting once that had to do with the fact that vtMissing was getting
> corrupted. Make sure that you do not pass &vtMissing for the
RecordsAffected
> parameter of Command.Execute or Connection.Execute; you need to pass 0
> instead.

Absolutely. All the RecordsAffected parameters are like this. I don't know
why Microsoft made these methods like this for VC++.

Stephen Howe



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem
Hi Jeff.  Thanks for the reply.

I think you misunderstood the first problem.  Update does work correctly as
long as you reconnect the recordset before calling it.  UpdateBatch is used
with adLockBatchOptimistic when you are changing more than 1 record and want
to update all of them with a single call.  I am only updating one record, so
I only need to call Update on the current row.

Thanks for the tip about replacing vtMissing with NULL.  It worked!  I love
all of these undocumented ADO "features".

Thanks again to everyone for their help.
-Brian


Quote:


> > I don't think so.  The ADO documentation says to use

adLockBatchOptimistic
Quote:
> > if you plan on calling UpdateBatch to do batch updates from your
recordset
> > object.  I (always) call Update on my recordset object, so I don't see
how
> > this would help.  If there is a good reason to try this, please explain
it
> > to me.

> You have to use adLockBatchOptimistic if you want to make changes to a
> disconnected recordset and then save the changes back to the database. If
> you think about it, how would the Update() call really be able to do
> anything without a connection? I think if you change the locktype to
> adLockBatchOptimistic and then call BatchUpdate once you're ready to save
> all the changes to the database, it should work. The only other option you
> have is to leave the Recordset connected to the database during its
> lifetime, rather than disconnecting it.

> As for your second error below, that sounds similar to an intermittent
error
> I was getting once that had to do with the fact that vtMissing was getting
> corrupted. Make sure that you do not pass &vtMissing for the
RecordsAffected
> parameter of Command.Execute or Connection.Execute; you need to pass 0
> instead.

> Jeff



> > > You should be using adLockBatchOptimistic



> > > > Ok, I thought the database was getting updated, but it really isn't.
> > > > Anyway, the Update command should work according to everything I've
> read
> > > > about ADO.



> > > > > I have a number of client-side recordsets that have been
> disconnected
> > > from
> > > > > their connection object (adLockOptimistic, adUseClient).  (There
is
> > only
> > > 1
> > > > > connection object per application.)  Here is the problem:  I do a
> > > > > Connection.Execute statement to insert a new record into a table.
I
> > > then
> > > > > call a stored procedure to retrieve the IDENTITY value of the new
> > > record.
> > > > > This stored procedure is executed by creating a Command object,
> > > > associating
> > > > > it with the single Connection object, and calling Execute
> > > (adCmdStoredProc
> > > > +
> > > > > adExecuteNoRecords).  Following this stored procedure call, I
> > reconnect
> > > > one
> > > > > of my recordset objects with the Connection object and call Update
> to
> > > save
> > > > > changes.

> > > > > I have a couple of problems:

> > > > > 1) The IDENTITY value returned is always zero.  I can see where
this
> > > might
> > > > > happen because ADO will create additional connections as needed if
> the
> > > > > current connection is busy.

> > > > > 2) My call to Recordset.Update throws an exception (_com_error).
> The
> > > most
> > > > > common HRESULT value is 0x800a0cc1.  The error description is,
"Item
> > > > cannot
> > > > > be found in the collection corresponding to the requested name or
> > > > ordinal.".
> > > > > I have actually seen this error message before when I tried to
> access
> > > > beyond
> > > > > the end of the Fields collection, only why would I get this error
on
> > the
> > > > > Update?  I set all of my field values without any errors!

> > > > > The really wierd part is that the update actually succeeds, since
I
> > > check
> > > > my
> > > > > database and everything looks fine!

> > > > > Aaaaargh!!!!!

> > > > > Does anyone know of a bug in ADO that would cause this behavior?
I
> > have
> > > > > found bug reports similar to this (same HRESULT, same error
> message),
> > > but
> > > > > none of the fixes seem to help.

> > > > > Any insight would be GREATLY appreciated.
> > > > > --
> > > > > =========================
> > > > > Brian D. Archer
> > > > > Senior Software Engineer
> > > > > (314)692-0202 x227
> > > > > =========================
> > > > > PC Innovators Consulting
> > > > > www.pcinnovators.com
> > > > > 11856 Lackland Rd.
> > > > > St. Louis, MO 63143
> > > > > =========================



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem


Quote:
> Hi Jeff.  Thanks for the reply.

> I think you misunderstood the first problem.  Update does work correctly
as
> long as you reconnect the recordset before calling it.  UpdateBatch is
used
> with adLockBatchOptimistic when you are changing more than 1 record and
want
> to update all of them with a single call.  I am only updating one record,
so
> I only need to call Update on the current row.

OK, I missed the part where you said you reconnect the recordset.

Quote:
> Thanks for the tip about replacing vtMissing with NULL.  It worked!  I
love
> all of these undocumented ADO "features".

Glad I could help.

Jeff



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem


Quote:



> > As for your second error below, that sounds similar to an intermittent
> error
> > I was getting once that had to do with the fact that vtMissing was
getting
> > corrupted. Make sure that you do not pass &vtMissing for the
> RecordsAffected
> > parameter of Command.Execute or Connection.Execute; you need to pass 0
> > instead.

> Absolutely. All the RecordsAffected parameters are like this. I don't know
> why Microsoft made these methods like this for VC++.

I guess it has to do with the fact that they're delcared as [out, optional],
whereas the parameters that vtMissing works for are usually [in, optional].
I agree, though, trying to figure stuff like this out can be a real pain; I
spent literally weeks struggling with wierd intermittent problems that were
because of vtMissing getting corrupted.

Jeff



Sun, 26 Jan 2003 03:00:00 GMT  
 ADO update problem

Quote:

> I don't think so.  The ADO documentation says to use adLockBatchOptimistic
> if you plan on calling UpdateBatch to do batch updates from your recordset
> object.  I (always) call Update on my recordset object, so I don't see how
> this would help.  If there is a good reason to try this, please explain it
> to me.

You call Update when updating data in your disconnected recordset. After you
reconnect it, you need to call UpdateBatch to send the changes to the actual
database. And you need to have opened your recordset with
adLockBatchOptimistic to do this.

--
Nikki Locke, Trumphurst Ltd.      PC & Unix consultancy & programming
http://www.trumphurst.com/



Sun, 26 Jan 2003 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. ADO update problem ??

2. ADO.NET problem Updating Access-DB

3. Joined Table update ADO.NET and C#

4. ado.net inset/update binary fields in databases ?

5. Updating multiple tables in ADO.NET

6. ADO Recordset.Update crash on a multiprocessor machine

7. ADO Update very slow against SQL Server 6.5

8. ATL-ADO AddNew/Update Phenomenon

9. ADO storedproc error: Updating not allowed on recordset

10. adDBTimeStamp and C++ ADO blinding/update record

11. Update a date-field with ADO

12. ADO: Update

 

 
Powered by phpBB® Forum Software