Re: How To Return Record Number From AddNew

Jeff Gaines wrote:
I am sure I used to know how to do this but I can't find it in my
notes :-(
After adding a record to an Access Database (programmatically using
C#) I need to know the record number. The code snippet is:

adCON.Open(strConnection, "", "", 0);
adRS.Open(strQuery, adCON, ADODB.CursorTypeEnum.adOpenKeyset,
ADODB.LockTypeEnum.adLockOptimistic, 0);

if (adRS.EOF)
adRS.AddNew(fList, fValues);
else
adRS.Update(fList, fValues);

Is it possible to know what record number has been allocated by the
Autonumber field? I know it before I call the function when updating a
record but it is freshly allocated by Access for AddNew.

With a "server-side" keyset cursor, the autonumber field should cntain the
newly allocated value after the update resulting from the AddNew occurs. If
it isn't, try
1. issuing an explicit call the the recordset's Update method
2. Using a client-side cursor and issuing a call to the recordset's Requery
method after performing the AddNew

My pesonal recommendation: don't use a recordset for data maintenance.
Instead, use SQL DML (INSERT, UPDATE and DELETE) statements, wither
encapsulated in saved parameter queries (stored procedures) or parameterized
with parameter markers. This will allow you to execute the SELECT @@identity
statement to retrieve the new autonumber.

See these links for more information:
http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-for-the-row-i-inserted.html
http://support.microsoft.com/default.aspx/kb/221931

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: VB Net and ADOX
    ... not a boolean and you ... My personal preference would be likely to use SQL statements whenever ... and an access database I have to do this using ADOX. ... create an AutoNumber field. ...
    (microsoft.public.dotnet.languages.vb)
  • How to create an AutoNumber field with a SQL statement?
    ... I need to upgrade a MS Access database with a script ... only, i.e., via SQL statement, not via Access GUI, and I'm having ... trouble defining an AutoNumber field. ...
    (comp.databases.ms-access)
  • Identify Autonumber field using VB6
    ... I am reading the database schema information for an access database using ... I get all the relevant information except the autonumber field. ... Dim rs111 As ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Identify Autonumber field using VB6
    ... I am reading the database schema information for an access database using ... I get all the relevant information except the autonumber field. ... Dim rs111 As ADODB.Recordset ...
    (microsoft.public.vb.database.ado)
  • export to secured Access database using ADO
    ... I'm using this code to export entries on an Outlook Form to an secured ... Access Database by pressing a button, ... Sub Button_Click ... The probleem seems to be the "AddNew" command, ...
    (microsoft.public.outlook.program_vba)