How to get the next identity number used in SQL 2005 and ASP?
I was previously getting the next available autonumber used in Access by doing a simple query like so:

    SELECT RecordNumber, Info FROM myTABLE WHERE 0=1

This way I could create a variable to hold the currentRecord and it will use the same autonumber that Access was going to use when I was updating the row

Example

    rs.AddNew
    currentRecord = rs("RecordNumber")
    
    rsInfo = "SomeFormData" & currentRecord
    rs.Update
    rs.Close

Now this use to work on MS Access, but in SQL 2005, I am not getting back the Identity created by the new record. "SomeFormData" is been inserted correctly, the RecordNumber field in SQL is been populated by the new auto number but I don't have the RecordNumber in my variables and I need it to continue filling related forms, that save data to related tables and need to save the currentRecord number.



Question is, is there a way to get this unique number back when doing a new insert?


How to get the next identity number used in SQL 2005 and ASP?
===

I was previously getting the next available autonumber used in Access by doing a simple query like so:

    SELECT RecordNumber, Info FROM myTABLE WHERE 0=1

This way I could create a variable to hold the currentRecord and it will use the same autonumber that Access was going to use when I was updating the row

Example

    rs.AddNew
    currentRecord = rs("RecordNumber")
    
    rsInfo = "SomeFormData" & currentRecord
    rs.Update
    rs.Close

Now this use to work on MS Access, but in SQL 2005, I am not getting back the Identity created by the new record. "SomeFormData" is been inserted correctly, the RecordNumber field in SQL is been populated by the new auto number but I don't have the RecordNumber in my variables and I need it to continue filling related forms, that save data to related tables and need to save the currentRecord number.



Question is, is there a way to get this unique number back when doing a new insert?

