How to find out what's the next record

[paulafernandes]

Hi! I told I'd be back... :)

Today's problem is this:
I have in my database (SQL Server 7.0) a table with a field that uses automatic increment. When I add a new record through a ASP form, I need to know the number of the record in which are going to store the data, in order to use that record number in another table.

Now, I'm using "cont = objrecordset.RecordCount + 1", but if a record is deleted, this is going to be wrong, because the "recordcount + 1" is not the number of the record that is going to be used. (I hope this is not so confusing as it seems to me now...)

So, to make it simple, I need to know the number that the database is going to store the data. Is there a way, or I have to do my one increment?

Thank's
Paula

[arnyinc]

Here is a page that gives a very good way of doing this in SQL Server:
http://www.aspfaqs.com/webtech/tips/t122600-1.shtml

Here is a more generic way of doing it. You must include the standard adovbs.inc file or change the last three variables in the RS.open line to be their static values.


RS.Open tableName, yourAlreadyOpenConnection, adOpenKeySet, adLockOptimistic, adCmdTable
RS.AddNew
RS("field1") = value1
RS("field2") = value2
...
RS("fieldN") = valueN
RS.Update
idOfAddedRecord = RS("nameOfAutonumberField")


http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

[whammy]

Hey, that's a neat trick. Thanks!

[paulafernandes]

Yep! It worked just fine!

Thank's a lot!

Paula