When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, General


Question:

**UPDATED 1 April 2002** How do I get the record number of a just added record?


[Print this FAQ]

Answer:

*********************************************************
If you are looking for an answer for SQL Server,
check out this page on 4GuysFromRolla.
*********************************************************


*********************************************************
If you use the JET OLEDB version 4.0 (or later) driver to
connect to your database (that is, do not connect using the
Access driver), then you can use nearly the same technique with an
Access (".mdb") database as you can with SQL Server.
*********************************************************


Briefly, you do something like this:

...
Conn.Open "Provider=MICROSOFT.JET.OLEDB.4.0; " _
† † & "DATA SOURCE=" & Server.MapPath("somedir\somedb.mdb"
SQL = "INSERT INTO someTable (fld1, fld2) VALUES(123,'xyz');"
conn.Execute SQL ' do the insert, and then...
Set RS = Conn.Execute( "SELECT @@IDENTITY" )
idOfNewlyAddedRecord = RS(0)
RS.Close
...

If you wish to use SQL to add data to your Access DB, then this is undoubtedly the best way to do it. (And thanks to Xanderno for correcting my March 5th post.)

UPDATE: TorbjÝrn Laukvik reports that this technique works for him, even though he uses the Access driver instead of the JET OLE DB driver. He is using Win2000 and IIS 5. What can I say? Obviously, whether this works depends on the version of the driver you use. So try it with your current driver; if it works, fine. If not, use the recommended driver, instead.




However, if you want to use ADO-style coding to add records (in particular, you want to use ADODB.RecordSet.AddNew), then read on...


First, let me warn you away from some of the answers you might see.

Most of them are variations on this theme:

' *** BAD CODE ***
someConnection.Execute( "INSERT INTO table (fld1,fld2) VALUES(value1, value2)" )
RS = someConnection.Execute("SELECT Max(RecordID) FROM table")
newID = RS(0)


What is wrong with that, you ask? Suppose that, just after you execute the INSERT above, some other user comes along and also does just such an INSERT. The record you inserted got (say) a RecordID of 37. That user got RecordID 38. You get the Max(RecordID) and you get back...38!!

Oops.

Are there ways around this problem? Yes, but they involve locking the table (or, perhaps, entire database?) or the Application (via Application.Lock for a time that encompasses both the insert and the select. That works. It's not a horrible solution for the kind of lightly loaded site one would expect an Access-based Web site to be.

But perhaps the cleanest solution, especially when using Access, is the following:

(1) Be sure to have an Autonumber field in your table. Set to indexed (of course) and "no duplicates allowed". It may or may not be your Primary Key, your choice.

(2) Follow this code pattern:

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


That's all there is to it! It works. You may substitute adLockPessimistic for adLockOptimistic, as you wish. But the adOpenKeyset is critical.

Naturally, you must #include the adovbs.inc file in order to get those adXXX names defined.




For those who might be a little more curious, I present here an ASP page that you can run on your own system to demonstrate the truth of the above. To use it, you need an Access database (mine is named AddNewDemo.mdb) which contains a table (mine is also named AddNewDemo) that has the following fields:
††† (1) RecordID -- Autonumber
††† (2) UserName -- Text
††† (3) lockMode -- Text
††† (4) openMode -- Text

Put the ASP code here and the database into the same directory somewhere in your ASP virtual directories. And then invoke the page from your browser. Look at the output. Look at the source code. Put in a (dummy) name in the input field and push the button. See it all happen again. Note how only the two records added via adOpenKeyset behave the way we want. Enough said?

<HTML>
<HEAD>
<TITLE>Demo of using AddNew with Access</TITLE>
</HEAD>

<BODY>
<CENTER>
<H2>Demo of using AddNew with Access</H2>
</CENTER>

<%
' Constants, et al.
'
' Important values from "adovbs.inc"
'
' CursorTypeEnum
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
' LockTypeEnum
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
' CommandTypeEnum
Const adCmdText = 1
Const adCmdTable = 2
'
' Names for cursor and lock types:
'
OpenNames = Array( "ForwardOnly", "Keyset", "Dynamic", "Static" )
LockNames = Array( "??", "ReadOnly", "Pessimistic", "Optimistic" )

'
' Get value from last time on the page:
'
userName = Trim( " " & Request("UserName") )
If userName = "" Then userName = "*** demonstration startup ***"

' set up connection to the db...
'
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);" _
††††††††††††& "DBQ=" & Server.MapPath("AddnewDemo.mdb")

Set RS = Server.CreateObject("ADODB.RecordSet")

For openMode = 0 To 3
††††For lockMode = 2 To 3
††††††††RS.Open "AddNewDemo",Conn, openMode, lockMode, adCmdTable
††††††††RS.AddNew
††††††††preid = RS("RecordID") ' get ID *before* the update call...
††††††††RS("UserName") = userName & "-" & openMode & "-" & lockMode
††††††††RS("lockMode") = LockNames(lockMode)
††††††††RS("openMode") = OpenNames(openMode)
††††††††RS.Update
††††††††postid = RS("RecordID")
††††††††RS.Close
††††††††Response.Write "open mode " & OpenNames(openMode) _
††††††††††††††††††††& ", lock mode " & LockNames(lockMode) _
††††††††††††††††††††& ": before update id = [" & preid _
††††††††††††††††††††& "], after update id = [" & postid _
††††††††††††††††††††& "]<BR>" & vbNewLine
††††Next
Next

Set top10 = Conn.Execute( _
††††††††"SELECT TOP 10 * FROM AddNewDemo ORDER BY RecordID DESC" )
Response.Write "<P>Last 10 records added to DB:<UL>" & vbNewLine
Do Until top10.EOF
††††Response.Write "<LI>" & top10("RecordID") & ": " _
†††††††††††††††† & top10("openMode") & ", " & top10("lockMode") _
†††††††††††††††† & " [" & top10("userName") & "]" & vbNewLine
††††top10.MoveNext
Loop
Response.Write "</UL><P>" & vbNewLIne

top10.Close
conn.Close

%>

<H3>Please enter a name which we will construct a new record:</H3>

<P>

<FORM>
Name: <INPUT Name="UserName" Size=60><P>
<INPUT Type=Submit Value="Push to add to database">
</FORM>

</BODY>
</HTML>




*** ADDENDUM ***

Some of you have probably heard or read that using AddNew incurs both a performance penalty and a possible bug. I have yet to see hard evidence of the bug, but it is true that the nature of AddNew can cause a (relatively minor, but important on some sites) penalty.

Except...

Except that there is a little used and little discussed variant on AddNew that offers exactly the same performance and safety as INSERT. To wit: ADODB.RecordSet.AddNew arrayOfFieldNames, arrayOfFieldValues

So, you could alter the above code something like this:

<%
... this comes after the creation of the ADODB.RecordSet object ...
' the array of field names is the same throughout,
' so create it here, ahead of the loops...
Fields = Array("UserName","lockMode","openMode")

For openMode = 0 To 3
††††For lockMode = 2 To 3
††††††††' create the (dynamic) array of values...
††††††††Values = Array( userName & "-" & openMode & "-" & lockMode, _
†††††††††††††††††††††††† LockNames(lockMode), OpenNames(openMode) )

††††††††RS.Open "AddNewDemo",Conn, openMode, lockMode, adCmdTable
††††††††' now use the "better" form of AddNew...
††††††††RS.AddNew Fields, Values
††††††††postid = RS("RecordID")
††††††††RS.Close
††††††††Response.Write "open mode " & OpenNames(openMode) & ", lock mode " _
††††††††††††††††††††& LockNames(lockMode) & ": after update id = [" _
††††††††††††††††††††& postid & "]<BR>" & vbNewLine
††††Next
Next
...
%>


It works well! Give it a try!


FAQ posted by Bill Wilkinson at 11/3/2000 9:07:09 PM to the Databases, General category. This FAQ has been viewed 97,514 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article