Re: ADO Recordset second Update fails

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 02/06/05 

Date: Sun, 6 Feb 2005 04:31:21 -0800

"desavera" <desavera@discussions.microsoft.com> wrote in message
news:CB505984-4ED9-429D-BC96-EE594E5B0A90@microsoft.com...
> Hi Folks, first time in MSDN....
>
> could someone please help me with the issue of having a two steps Update
> call
> on a ADO.Recordset object problem ?
>
> the error message is :
>
> Identity(1,1) cannot be determined for newly inserted row.
>
> the description of the context is that I am using an autonumber column to
> determine the value for another column. The autonumber returns Ok from the
> first Update (doing GetCollect) but when I use GetItem in order to *set*
> the
> new value (retreived from the autonumber) for the other column it fails :
>
> Recordset.addNew()
> Recordset.getItem() // several GetItem() calls
>
> Recordset.Update()
>
> tmpValue = Recordset.GetCollect()
> Recordset.GetItem(colname) = tmpValue // ERROR !!!
> Recordset.Update()
>
> any tip would be appreciated. The funny thing is that I am trying to do
> this
> using SQLServer provider but when I use MS-Access provider it works just
> fine !!!
>
> Mario.

Try setting this dynamic property after you create, but before you open the
Recordset object:

    rs.Properties("Change Inserted Rows") = True

That tells the provider that you want to be able to update records that have
been inserted using this recordset, without requerying first.

-Mark

Relevant Pages

  • Re: Error -2147418113 with AddNew in transaction
    ... > Microsoft OLE DB Provider for SQL Server 7.01 ... > Any operation following a rollback or a commit transaction on a recordset ... > The OLE DB provider must support preserving cursors. ... > SQL Server supports preserving cursors on Commit and Abort. ...
    (microsoft.public.data.ado)
  • Re: Data provider or other service returned an E_FAIL status every
    ... 'repeat each merge above for each recordset ... SQLOLEDB provider and that the stream itself is using a TEXT provider. ... For Each objField In recSourceClone.Fields ... I am thinking I could run a connection ...
    (microsoft.public.data.ado)
  • Re: Newbie Q. for referencing DAO recordsets with VBA in Access front
    ... recordset. ... designer, keep in mind that sometimes the columns are renamed to something ... isn´t supported by that provider. ... HTH, Jens Suessmeyer. ...
    (microsoft.public.sqlserver.msde)
  • Re: MDAC 2.8 vs MDAC 2.7
    ... Actually RecordCount has to do with the provider, ... If just changing of the connection string to use OLEDB provider solves this ... then it looks like a bug in OLEDB for ODBC provider or in ODBC ... But do you get an actual recordset opened in your case? ...
    (microsoft.public.data.ado)
  • Re: Recordset or Object is closed HELP PLEASE!
    ... this is the first time i'm doing VBA. ... how am i supposed to display the records found in the recordset onto the excel sheet? ... >> different query that is simpler. ... >> that this query Declares Variables and Sets those ...
    (microsoft.public.excel.programming)