ADO does not return SQL Server Identity value 
Author Message
 ADO does not return SQL Server Identity value

Use a stored procedure to accomplish this task.

Peter Larsson


Quote:
> When using ADO to add a new record to a SQL Server database, the Update
> method on my ADO.Recordset object (following an AddNew) does not return
the
> auto generated Identity value. I am using a Server Side Cursor and a
Dynamic
> Cursor Type. I used to get this prblem when using an Access 97 database
but
> Access 2000 sorted it out (something about Access 97 not supporting Server
> Side Cursors).

> Any ideas?





Sat, 09 Nov 2002 03:00:00 GMT  
 ADO does not return SQL Server Identity value
When using ADO to add a new record to a SQL Server database, the Update
method on my ADO.Recordset object (following an AddNew) does not return the
auto generated Identity value. I am using a Server Side Cursor and a Dynamic
Cursor Type. I used to get this prblem when using an Access 97 database but
Access 2000 sorted it out (something about Access 97 not supporting Server
Side Cursors).

Any ideas?




Sat, 09 Nov 2002 03:00:00 GMT  
 ADO does not return SQL Server Identity value
Peter

Thanks for the suggestion, why will this work over and above what I have
used in the past with Access 2000?

Paul Gascoigne


Quote:
> Use a stored procedure to accomplish this task.

> Peter Larsson



> > When using ADO to add a new record to a SQL Server database, the Update
> > method on my ADO.Recordset object (following an AddNew) does not return
> the
> > auto generated Identity value. I am using a Server Side Cursor and a
> Dynamic
> > Cursor Type. I used to get this prblem when using an Access 97 database
> but
> > Access 2000 sorted it out (something about Access 97 not supporting
Server
> > Side Cursors).

> > Any ideas?





Sat, 09 Nov 2002 03:00:00 GMT  
 ADO does not return SQL Server Identity value
Further info.....

Changing my CursorLocation value on my recordset object from

rs.CursorLocation = adUseServer

to

rs.CursorLocation = 3

appears to have solved the problem, but I'm not sure why?

The reason I have done this is because my connection object with it's cursor
location set to adUseServer returns 3 if retrieved using Debug.Print, the
same property of my recordset object returns 2 (...shouldn't they be the
same?)

It was a stab in the dark and it works, although I'm not altogether happy
about it......


Quote:
> Peter

> Thanks for the suggestion, why will this work over and above what I have
> used in the past with Access 2000?

> Paul Gascoigne



> > Use a stored procedure to accomplish this task.

> > Peter Larsson



> > > When using ADO to add a new record to a SQL Server database, the
Update
> > > method on my ADO.Recordset object (following an AddNew) does not
return
> > the
> > > auto generated Identity value. I am using a Server Side Cursor and a
> > Dynamic
> > > Cursor Type. I used to get this prblem when using an Access 97
database
> > but
> > > Access 2000 sorted it out (something about Access 97 not supporting
> Server
> > > Side Cursors).

> > > Any ideas?





Sat, 09 Nov 2002 03:00:00 GMT  
 ADO does not return SQL Server Identity value

http://www.able-consulting.com/ADO_Faq.htm#Q9

--

Andrew Grillage
http://www.concresco.com



Quote:
> When using ADO to add a new record to a SQL Server database, the Update
> method on my ADO.Recordset object (following an AddNew) does not return
the
> auto generated Identity value. I am using a Server Side Cursor and a
Dynamic
> Cursor Type. I used to get this prblem when using an Access 97 database
but
> Access 2000 sorted it out (something about Access 97 not supporting
Server
> Side Cursors).

> Any ideas?





Sat, 09 Nov 2002 03:00:00 GMT  
 ADO does not return SQL Server Identity value
Change "adOpenDynamic" with  "adOpenKeyset" cursor type;
after the update method you can read the value of the identity field
( that you must have in the same recordset).
Quote:

> Use a stored procedure to accomplish this task.

> Peter Larsson



> > When using ADO to add a new record to a SQL Server database, the Update
> > method on my ADO.Recordset object (following an AddNew) does not return
> the
> > auto generated Identity value. I am using a Server Side Cursor and a
> Dynamic
> > Cursor Type. I used to get this prblem when using an Access 97 database
> but
> > Access 2000 sorted it out (something about Access 97 not supporting Server
> > Side Cursors).

> > Any ideas?





Fri, 15 Nov 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Learning value of identity field on SQL Server 7 after ADO-based insert

2. Get Identity Field Value ADO SQL Server

3. Stored Proc Return values / Output Params w ADO and SQL Server 7

4. Stored Proc Return values / Output Params w ADO and SQL Server 7

5. Stored Proc Return values / Output Params w ADO and SQL Server 7

6. Stored Proc Return values / Output Params w ADO and SQL Server 7

7. ADO & SQL SERVER RETURN VALUES

8. Returning identity value when using ADO Recorset...help

9. identity not returning values

10. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

11. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

12. ADO update not inserting identity value

 

 
Powered by phpBB® Forum Software