Updating Identity field in disconnected recordset

Updating Identity field in disconnected recordset

Post by Kesh » Sat, 03 Feb 2001 05:57:52

I'm trying to realize following:
SQL Server 7.0, ADODC, DataGrid

Public db as Connection

DataGrid1.Datasourse = ADODC1
set ADODC1.Recordset = db.execute(MySQL)
ADODC1.Recordset.ActiveConnection = Nothing

I edit or add new record from dialog via stored procedure
When stored procedure succesfuly complete I edit or add new record to

How can I store Identity of this record?

Thanks for help
Verbovsky Innokenty
( ICQ 16999735 )


1. updating virtual fields in an ADO disconnected recordset


I'm having a really frustrating problem trying to update a
virtual field (i.e. a field hard-coded in my SQL query,
not retrieved from the database) in a disconnected ADO
recordset. Using exactly the same query from a Sybase 12
database works, but against MSSQLServer 7 it doesn't.

The code is very simple:-

Dim rs As New ADODB.Recordset
Dim oCon As New ADODB.Connection
Dim sConnStr As String
Dim sSQL As String

sConnStr = "DSN=myDSN;UID=me;PWD=password"
oCon.Open sConnStr
rs.CursorType = adOpenKeyset
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
sSQL = "select name, 'N' visible FROM ztable"
rs.Open sSQL, oCon
rs("visible") = "Y"

The final line fails on MSSQL7, but not on Sybase. The
other field (name) can be updated using either database
without problems.

Any ideas why...!? Does MSSQL7 intentionally prevent
updates of virtual fields for some unknown reason?

I've checked the recordset properties for settings which I
though might be relevant
(i.e. "updatability", "IRowsetChange", "IRowsetUpdate"),
but they all seem to have the correct settings, and are
the same for both databases.

Many thanks in advance for any ideas. Cheers,

David Cowell

2. Trace file

3. Disconnect recordset, SQL 2000 and Internet and @@IDENTITY

4. Other reporting tools

5. Checking an identity in a disconnected recordset


7. Identity column and Disconnected recordset

8. Restoring backup w/different binary sort order

9. ADO Update the Database with the modify done on a disconnected recordset in background mode

10. Lock record with disconnected recordsets - avoid lost updates

11. Data Repeater control updating a disconnected recordset.

12. Disconnected Recordset Update

13. Updating a disconnected recordset