Returning @@Identity in VB6 ADO

[PaulyWally]

I can't figure this one out, but this is basically what I have:

==================
Dim rsTemp As ADODB.Recordset
Set rsTemp = New ADODB.Recordset
With rsTemp
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.ActiveConnection = SQLConnectionString
End With

connMain.Open

Set rsTemp = connMain.Execute("SELECT @@Identity AS ClientID INSERT INTO Client (FirstName,LastName) VALUES ('Phil','McCrackin')")

connMain.Close

===================

The INSERT statement works, but rsTemp results with a Recordcount of -1 and its ClientID field is NULL. I've often had weird problems in ADO with recordsets having recordcounts of -1... but it was usually due to the cursor location/type. I've tried changing the cursor type, location, and locktype... but nothing. Maybe I just haven't hit the correct combination yet? *shrug*

Any ideas? Or is there another/better way to return @@identity?

Thanks in advance!

[brucevde]

A RecordCount of -1 does not mean the recordset is empty, actually it is the opposite. ADO was not able to determine the exact number of records but you can be sure there is at least one record. Use the EOF and BOF properties instead.

The Select @@Identity statement should be executed after the Insert statement.

Every SQL Statement executed returns a recordset. Some may be closed others may be open. An Insert statement will cause a closed recordset to be created. Use the NextRecordset property to find the result of the Select @@Identity statement.

Since you are using the Connection.Execute method, the code to instantiate the recordset and to set its properties is useless. The execute method returns a new recordset object (Server Side, Forward Only, Read Only), basically overwriting anything the rsTemp variable currently contains.

I am assuming you are using SQL Server, I don't know if you can run multiple statements with MS Access.