Solved

Get the last inserted value

Posted on 2004-08-21
14
232 Views
Last Modified: 2012-06-22
I would like to get the autonumber that was generated by an insert statement returned to my web application (asp.net C#)

For example, in MS SQL I have a stored procedure that inserts the record and returns @@Identity as the output parameter.

In this way I am guaranteed that I am getting the identity generasted for the last inserted record - and not the value of another record that may have come in at the same time (such as would be the case if I did something like a SELECT MAX)

How is this done in Access?

Thanks
0
Comment
Question by:mrichmon
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
In my opinion, the closest you will get is to submit a second SQL statement to the MSAccess database, looking for the autonumber (key field) value for a unique combination of data values you inserted.

For instance, if the first three columns (Name, DOB, Gender) constitutes a unique identifier for a row, then submit the following:
Select ID
From Tablename
Where Name = "namevalueyouinserted"
And DOB = #date/value/youinserted#
And Gender = "gendervalueyouinserted"
0
 
LVL 35

Author Comment

by:mrichmon
Comment Utility
that won't work.

I could come up with many examples where that would fail.

Additionally in the case I am working on there are few fields in one of the tables and therefore it is HIGHLY probable that there would be two inserted at the same time that had the same values.

In general I would not use access ... in fact I would tell most people to consider upgrading to a "real database".
I am working on that, but in the meantime I was hoping to find a workable solution....
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
use an AutoNumber field

Open your table in design view, add a field call ID and set it to autonumber datatype


you can then use

SELECT Max(ID), otherField, someOtherField FROM tblMyTable
Groupby otherField, someOtherFiled;

Dave
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
First, Access (or more precisely: Jet) is a real database but it is not "the" universal and unlimited database that fits every purpose.

Then, you can add a record and retrieve the ID using code:

<code>

Public Function InsertRecord(ByVal strTable As String) As Long

  Dim dbs   As DAO.Database
  Dim rst   As DAO.Recordset
  Dim lngID As Long
 
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strTable)
  With rst
    .AddNew
      lngID = !ID
    .Update
    .Close
  End With
  Set rst = Nothing
  Set dbs = Nothing
 
  InsertRecord = lngID

End Function

</code>

Of course, change dbs (and even rst) to Static if the creation of records is intense.

If you are not capable of running anything else than a query from your app, wrap the function into a select query:

<SQL>

  SELECT
    *
  FROM
    tblTable
  WHERE
    ID=InsertRecord("tblTable");

</SQL>

This will create and retrieve one new record when run.

/gustav
0
 
LVL 35

Author Comment

by:mrichmon
Comment Utility
flavo - Very WRONG.  

The field is autonumber,but that is the value I am trying to get - select MAX does NOT work - unless you are guaranteed to have ONLY one person ever using the database with only one connection .


cactus_data, I am not sure I see how that works.  Where does each portion of code go and how does the data get passed to the insert function?

ANd can that be called froma web page.  Access has a lot of things you can do in Access, that you cannot not do when connecting from a web page.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The closest thing to a stored procedure in MSAccess is a stored query.  Other than replacing MSAccess with the MSDE or the SQL Server desktop edition (newly announced), I'm not sure you can do what you are asking in a simple manner.

Suggestions:
1. Use a variant of the cactus_data code.
with rst
  .addnew
    !Name = "value"
    !DOB = #value"
    !Gender = "value"
  .update
end with
rst.Bookmark = rst.LastModified

Note: if using ADO instead of DAO, the newly inserted record becomes the current row, so you do not need to position to the newly inserted row (using the LastModified property as shown above).

~~~~~~~~~~~~~~
2. Create a function within MSAccess to accept field value parameters, insert the row, and return the ID of the newly inserted row.  Create a select query that invokes the function.
Exammple:
Select fcnInsert([parmNameValue], [parmDOBValue] , [parmGenderValue])
From OneRowTable

Note: OneRowTable is a single-row table that facilitates the above query.

~~~~~~~~~~~~~~
3. Create a data-layer between your program and the database.  Implement the same function as 2 (above) and invoke this data layer in place of direct SQL or direct ADO/DAO inserting.

~~~~~~~~~~~~~~
4. Create the same function as 2 (above), but use MSAccess automation to invoke the function directly.  I mention this last because I am not sure this idea can be implemented.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> Where does each portion of code go and how does the data get passed to the insert function?

> And can that be called froma web page.  
> Access has a lot of things you can do in Access, that you cannot not do when connecting from a web page.

That depends on how you connect to Access and/or the Jet engine.
As aikimark states, you have no stored procedures in Jet. You will need some kind of running application (your own or a supporting one) to run a function via DAO or ADO. I'm not a web-programmer and the options are to numerous to be more specific. Automation is certainly one option.

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
There is also one more SQL solution I should suggest...

1. Do your Insert (with a stored query or dynamic SQL)...however you like.
2. Execute the following query:

Select ID
From MyTable
Where ID = (Select Max(N.ID) From MyTable As N Where name = "INameValue" And DOB = #IDOBValue# And Gender = "IGenderValue")

Although your non-autonumber values aren't unique, it is unlikely that duplicate rows would be inserted in the same timeframe by another process/user.  If all the additions were caused by your ASP.Net code, then you might be able to serialize this process (Insert & Select) in a function.  This example assumes your autonumber field is named "ID".

Note: To retrieve the entire row just inserted, use:
Select *
0
 
LVL 35

Author Comment

by:mrichmon
Comment Utility
" it is unlikely that duplicate rows would be inserted in the same timeframe by another process/user"

Maybe in some cases - but definatley not in all and not in this case.  it IS likely that duplicate rows could be inserted by different users in the same timeframe - that is one reason we are trying to move this project away from access, but we have found limitations with MySQL which was the next options....
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The latest version of MySQL has stored procs. :-)

~~~~~~~~~~~~~~~~
1. How many IIS processes are simultaneously inserting data into this table?

2. Have you looked at your .Net serialization (locking) options?  This would allow you to do your Insert/Select without others' data.

3. Have you looked into database transactions or other database locking mechanisms that might provide your inserting process serialized access to the table during the update?

4. What other simultaneous updaters are there to this table?

5. How about adding a column, while you are still using MSAccess?  You can generate a GUID as one of your non-autonumber columns and then invoke:
Select ID From MyTable Where colGUID = "{...}"

That way you will be <absolutely certain> that you are not getting a row ID inserted by another process/user.

Note: For a faster lookup, you could use a double-word datatype column. Supply the current datetimestamp for both your Insert and your Select.
0
 
LVL 35

Author Comment

by:mrichmon
Comment Utility
" The latest version of MySQL has stored procs. :-)"

Only sort of....and we are still finding VAST limitations with even the alpha of version 5 which "supposedly supports" stored procs, but as I have said - only to a limted extent and tehre are other limitations.

Answers :

1) Could be a lot - which is why we are looking at upgrading the database.

2) looked, but was kindof confused

3) Yes - access support of them isn't that great that I have seen

4) don't know as of yet - this project will be expanding

5) Thought of that, but what is the function in access to create guid?  Or is tehre one in .Net C#?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
From http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=335

System.Guid.NewGuid().ToString() //= 86692da1-8ab4-48cb-9356-6ae153b85f05
System.Guid.NewGuid().ToString("N") //= c9065b65331f40bd89e3cb4a6c1018ca
System.Guid.NewGuid().ToString("D") //= b4477dcb-c5c8-43c5-972f-ae4e10854ae3
System.Guid.NewGuid().ToString("B") //= {a61dfdb8-59f0-434b-a93e-12638bdf1d71}
System.Guid.NewGuid().ToString("P") //= (5cfa5cff-b0b6-4349-85da-3192456faa37)
0
 
LVL 35

Author Comment

by:mrichmon
Comment Utility
Thanks!!!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That's a nice solution.

/gustav
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now