Returning ID from SQL through ADO recordset 
Author Message
 Returning ID from SQL through ADO recordset

This function should return the ID of the most recently added record.
Unfortunately, it always returns 0 event though the record gets added
correctly in the db with a non-zero ID value. Interestingly, the
recordset object doesn't take on the CursorType of adOpenKeySet or the
LockType of adLockOptimistic. I'm thinking this might be related to the
problem. The recordset gets the default CursorType and LockType even
though I'm explicitly providing them as part of the rs.open statement.

Anybody know what might be going on here?

Function GetNewID(strTableName As String, strIDName As String) As
Integer
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer

        Set cn = CreateObject("ADODB.Connection")
        cn.Open sConnectionString, sRuntimeUserName, sRuntimePassword

    rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable

    rs.AddNew
    rs.Update

    i = rs.Fields(strIDName)

    GetNewID = i

    rs.close
    Set rs = Nothing
    Set cn = Nothing

End Function



Mon, 18 Dec 2000 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. VB ADO recordset returned from MS SQL

2. ADO SQL Recordsets Return Duplicate Names

3. Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure

4. SQL stored procedure returning unusual ado recordset

5. Recordset.[id] vs Recordset![id] what is the difference

6. Recordset.[id] vs Recordset![id] what is the difference

7. Recordset.[id] vs Recordset![id] what is the difference

8. ADO SQL Search That Returns 0 Records When it Should Return 1

9. How do I retreive autonumber ID's from an ADO recordset after .addnew

10. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

11. SQL login ID versus user ID

12. SP w/ADO not returning recordset

 

 
Powered by phpBB® Forum Software