Retrieving AutoNumber after INSERT via @@IDENTITY 3

[Rydel]

I've read on msdn.microsoft.com that you can use "select @@IDENTITY" to get the AutoNumber ID field value after INSERT from Access database via OLEDB4, but it didn't work. any help greatly appreciated! I search the archives of the group but didn't find anything appropriate. ---

http://www.geocities.com/rydel23/

---

 

[tlbroadbent]

If done immediately following the INSERT,

SELECT @@identity As Ident

should return the value. However, you can't count on it being right if you execute the INSERT query and then execute another query to obtain the value in a separate batch. Both INSERT and SELECT must be in the same SQL batch.

How about showing us the code you use? Terry

http://members.home.net/tlbroadbent/index.htm

 

[Rydel]

Thanks a lot! Well, it didn't work (but I still don't lose hope), here is my ASP code:

strSQL = "insert into questions
(AskedByID,AskedByName,AskedByEMail,CategoryID,QuestionTitle
,QuestionDetails,notifyUser) values
('"&user1&"','"&name1&"','"&email1&"','"&category1&"','"&sub
ject1&"','"&question1&"','"¬ify1&"')"
Set RS = cnnQA.Execute(strSQL)

strSQL = "select @@IDENTITY"
Set RS = cnnQA.Execute(strSQL)
newID = RS(0).value

In the above code newID always returns 0.

Now if I put together two statements in one strSQL
(i.e. strSQL="insert.... ; select @@IDENTITY as Ident") separating them with ";" then I get an error message:
"Characters found after end of SQL statement."
And if I don't put semicolon, it gives me an error message:
"Missing semicolon ( at end of SQL statement."

Any ideas?
---

http://www.geocities.com/rydel23/

---

 

[Rydel]

anyone out there??? ---

http://www.geocities.com/rydel23/

---

 

[tlbroadbent]

Once again I failed to read a post carefully. You asked about MS Access and @@IDENTITY. @@IDENTITY is not available in MS Access. I'm not sure which MSDN article you read but it certainly misled you. And as I keyed off @@IDENTITY in your post, I thought of SQL Server and gave you a SQL Server reply.

The only way that I know of obtaining the value of an autonumber column after an insert is to select DMAX of the column. This of course assumes that the autonumber increments sequentially. There are drawbacks in a multi-user system as another insert could occur between your insert and querying DMAX. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Rydel]

Thanks a lot. I see your point, Terry. Getting the MAX value should work for me as this system will have only 100-200 users altogether, so most probably there never will be more than 3-4 users simultaneously, and the probability of getting the "ID" of the other guy's insert is extremely low.

What concerns me more (that's why I didn't employ this solution) is what happens when you delete the rows? I think if you have records with ID's 1,2,3,4,5 and, let's say, you delete record with ID 3, the Autoincrement will still go on and use 6,7,8 for consequent records (3 will be abandoned). But, I think, if you compact the database then it starts using the old ID's of those records that were deleted (I don't know whether it's true, as I couldnt' recreate the behavior), so in our case the new insert will get ID 3, while the Max value will be something else. Am I getting across here?

BTW, I will look up the URL of the MSDN article for you tomorrow, as it was stored in my computer at work. There was one more interesting artcile (with an alternative way to get an autoincrement after insert in Access), but it requires a separate post.



---

http://www.geocities.com/rydel23/

---

 

[tlbroadbent]

The Access will not use previous numbers such as the #3 in your example. However, if you delete #7 and compact the database, Access will reuse 7, assuming #7 was the last autonumber on the table prior to the delete and compact.

There has been a lot of discussion of autonumber in several usenet groups. Autonumber in Access causes a lot of grief.

Another popular and safer solution is to create a table to carry the last number used for the column. You would query the table for the number, increment it and update the table. This could all be done in a recordset with locking on to prevent someone else from updating it between your query and update. You could then use this number for your insert and subsequent updates to other records. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Rydel]

Dear Terry,

At this address

http://support.microsoft.com/support/kb/articles/Q232/1/44.ASP

you'll find the article I was referring to: Q232144 Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity.

I've also found another interesting solution to Autonumber problem at this URL

http://support.microsoft.com/support/kb/articles/Q221/9/31.ASP

Q221931 HOWTO: Return Record's Autonumber Value Inserted into Access DB

I tried the sample code from that article and it worked. But then I realized that there is a problem with the code. The database cursor "gets stuck" (I can't find more appropriate description to the problem). I inserted around 10-15 records then the cursor returned 35 and no matter how many more records I inserted it still returned 35 as the ID. Then I deleted all the records from 34 and up. And it got "unstuck" and worked for a while, but then it got stuck again at record number 45 and kept returning 45 no matter how what I did (insertions, deletions). Weird, isn't it? I am looking forward to hearing from you!


---

http://www.geocities.com/rydel23/

---

 

[tlbroadbent]

The article "Q221931 HOWTO: Return Record's Autonumber Value Inserted into Access DB" refers to using a recordset to add the new record and return the value. You are excuting of your SQL insert statement to add the new record. Therefore, you cannot use the method described to return the ID value. I had seen that article but it is not applicable unless you change the way you add records.

Thanks for the information regarding the @@identity. I had not seen it and couldn't find that article while searching the past couple of days. It appears you are having the same problem mentioned by others in various news groups. Autonumber seems to be rather unpredictable in way it works and reading the value is even more so.

When you add records, past 35 or 45 did the autonumber value increase properly? Is the error only in the return of the value or is it actually inserting a duplicate value? Terry

http://members.home.net/tlbroadbent/index.htm

 

[Rydel]

Thanks. Here are some clarifications:

1) Sure, I customized the code as follows:


Set objRS = CreateObject("ADODB.Recordset&quot
objRS.CursorLocation = adUseClient
objRS.Open "select * from questions", cnnQA, adOpenStatic, adLockOptimistic

objRS.AddNew
objRS("questDate&quot = questDate1
objRS("AskedByID&quot = user1
objRS("AskedByIP&quot = IP1
objRS("AskedByEMail&quot = email1
objRS("AskedByName&quot = name1
objRS("CategoryID&quot = category1
objRS("QuestionTitle&quot = subject1
objRS("QuestionDetails&quot = question1
objRS.Update
[COLOR=#f0000]
Response.Write &quot;<br>ID before Requery = &quot; & objRS(&quot;ID&quot
[/color]
bookmark = objRS.absolutePosition
objRS.Requery

Response.Write &quot;<br>ID before setting absolutePosition = &quot;
& objRS(&quot;ID&quot

objRS.absolutePosition = bookmark

Response.Write &quot;<P>Added ID = &quot; & objRS(&quot;ID&quot


2) It works fine for a while and then it &quot;stucks&quot;. Then the output of those three Response.Write statements is as follows: 0,34,35 on first ocassion and on the second ocassion 0,45,46. The method objRS.AddNew works fine (it does insert a separate record each time), but the objRS(&quot;ID&quot just keeps returning the wrong value.

3) I don't know if this matters at all, but in the development process I also change things in that table manually, I download it to local machine and then upload it back to the Web server after making changes.

4) One more point which may be relevant. Deleting the &quot;stuck&quot; record and all consequent values solves the problem. But, if I just delete the &quot;stuck&quot; record itself (e.g. with ID 35) then the output becomes 0,33,34.

---

http://www.geocities.com/rydel23/

---

 

[uladzik]

tlbroadbent, i'm alsou curious about this issue because i had same problem... ---

http://www.geocities.com/uladzik/

---