Finding Autonumber Value after Addnew 1

[ahmun]

I would search for this answer, as I'm sure the question has been asked, but the search function on this website isn't working and Google hasn't been much help

How do you find the autonumber value after running an "rs.addnew" on a recordset?

I'm running:
[ul][li]IIS 5.0[/li]
[li]Access 2000 back end[/li]
[li]ASP/VBScript[/li][/ul]

Earnie Eng

 

[Lothario]

SELECT TOP 1 autonumfld FROM yourtable ORDER BY autonumfld DESC

-L

 

[ahmun]

hm... I'm trying to find the new autonumber at the time I add the new record...

Such as:

rs.addnew
intNewID = rs("intID")

I was successful in doing this by running a .update, and a movelast command:

rs.addnew
[b][blue]rs.update
rs.movelast[/blue][/b]
intNewID = rs("intID")

but before I go trusting this solution... I want to know if anybody has some insight into how this would play out in a multi-user environment.

Some other environment varialbes I'm using:
[ul][li]I use Driver [blue]Microsoft Access Driver (*.mdb)[/blue] in my connection string[/li]
[li]I am creating an [blue]ADODB.RecordSet [/blue]object[/li][/ul]

Earnie Eng

 

[Genimuse]

You can easily do it like this (with Access):

[COLOR=gray]'objConn is the connection object
'objRS is a recordset object[/color]
strSQL = "Your Insert Statement Here"
objConn.Execute(strSQL)
strSQL = "SELECT @@IDENTITY AS NewID"
set objRS = objConn.Execute(strSQL)
intNewID = objRS("NewID")

Using this example (and your real INSERT sql), the identity field of your inserted record (usually the autonumbered primary key of the table you just inserted into, in Access, commonly "ID") will now be in the variable intNewID.

 

[ahmun]

Can you select the rest of the fields as well?

I plan to update the record by assigning the form values to recordset fields, then running a .update method.

Which brings up a question... which is a better method? opening a recordset? or running an SQL INSERT Query?



Earnie Eng

 

[ahmun]

The reason for finding this autonumber is that I wanted to use an autonumber to generate a unique ID. The user wants to have the ID field formatted under a certain syntax which includes the two-letter State, a two digit year, then the autonumber.

So I have two fields in the DB: the ID field (autonumber), and an additional "ID" field that the user will see.

[blue]WA040001[/blue] would be an example of this...

Earnie Eng

 

[pkailas]

If you are using SQL, you I'd use the INSERT method followed by a SELECT to retrieve the identity. Like:

SQL = "INSERT INTO

(INDEX1, INDEX2) VALUES (VALUE1, VALUE2)"

SQL = SQL & " SELECT SCOPE.IDENTITY() AS EXPR1"

RS.OPEN SQL, CONNECTION

You should retrieve the identity of the inserted record as EXPR1

 

[Genimuse]

The code I posted just give you the identity field. Not sure why you'd want to retrieve the other fields, since you must know what they are as you just inserted them.

It is better by far to perform a true SQL Insert or Update rather than needlessly creating a recordset and then updating it.

 

[ahmun]

Let's say I have the following fields in a table
[ul][li]intClaimID [gray](autonumber)[/gray][/li]
[li]strAccidentCode [gray](**)[/gray][/li]
[li]dtmDateOfLoss [gray](date)[/gray][/li]
[li]strState [gray](string)[/gray][/li][/ul]

for simplicity's sake let's just work with those...

** the strAccidentCode is generated from the other three fields as such:
[blue]CA[/blue][green]04[/green][purple]001[/purple]
which is a compliation of the State ("CA"), the two-digit year ("04"), and the autonumber field's value ("001").

How would I go about creating this number when I'm adding a new record? Is there a way to find that @@IDENTITY within the same SQL statement? Or would I have to perform the insert first, then perform another update?


Earnie Eng

 

[Genimuse]

I see. Well, the way I'd do it, then, would be to perform an insert of all of the fields except the accident code, grab the identity (like I show above), and then perform an update on that record, adding the accident code.

'objConn is the connection object
'objRS is a recordset object
strSQL = "INSERT INTO YourTable (dtmDateOfLoss, strState) "
strSQL = strSQL & "VALUES (#" & YourDate & "#, '" & YourState & "')"
objConn.Execute(strSQL)

strSQL = "SELECT @@IDENTITY AS NewID"
set objRS = objConn.Execute(strSQL)
intNewID = objRS("NewID")
objRS.Close
Set objRS = Nothing

Dim strAccidentCode
strAccidentCode = YourState & Right(Year(YourDate), 2) & intNewID

strSQL = "UPDATE YourTable SET strAccidentCode = '" & strAccidentCode & "'"
objConn.Execute(strSQL)

 

[ahmun]

Thanks Genimuse!

I have one question though... how do you add a "TRUE or FALSE" value in a SQL statement?

I've tried using -1 as the value as well as True... but I get a syntax error...

Earnie Eng

 

[Genimuse]

With something like SQL Server, you can use just TRUE or FALSE. With Access, though, it depend on how the boolean field was set up. In Access you can tell it that it should be "YES/NO", "TRUE/FALSE", and something else. Whatever it's set to is what you have to send it, with YES/NO as the default. (As such if I create a new database I alway make certain to set such fields to TRUE/FALSE so standard SQL will work with them.)

 

[Genimuse]

Oops, that next-to-last line of code should be:

strSQL = "UPDATE YourTable SET strAccidentCode = '" & strAccidentCode & "' [COLOR=blue]WHERE intClaimID = " & intNewID[/color]

VERY important, as otherwise the update statement will set every record to that accident code.

 

[ahmun]

Thanks for the input, Genimuse.

Earnie Eng