Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: display last autonumber

    Hi all,

    I have a form which relies on a table that has the primary key as the autonumber for the field (ID).

    I understand that an new number is generated when a new record is entered on the table, but how can i see this?

    This form is a submission form in which the user fills out the details and submits the details to the table.

    I am trying to build a confirmation screen after submission which pulls up the last (ID) so that the user can confirm the submission.

    However with the (ID) field being an auto number field i cannot see the number untill the details has been committed to the table.

    I have tried the MAX function but with som many records this is stupidly slow!

    How can i see the number?

  2. #2
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51

    Re: display last autonumber

    Have you tried running a query against the table using a Desending sort on the auto number field and grab the first record?
    Bob

  3. #3
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    A "select Max(AutonumerField) from SomeTable" should be very fast even with millions of rows if it is the primary key. If it's not you might consider making it the primary key or at least index the field (use a unique index). This will greatly improve performance on this query and possibly others.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    on the confirmation screen, if you are pulling the data out of the table, does this mean that the data is entered into the table before confirmation? what if the user does not confirm? don't you then have to do a delete? or are new records put into a temp table and then moved to a live table? or perhaps a status is set to "declined" or something?

    in any case, max(autonumber) is not the way to do it, unless you can guarantee that only ever one person will use your application

    as soon as multiple users can use the app, if you use max() you run the risk of presenting somebody else's row to the wrong user

    what you should do is pass the form fields to the confirmation page, and use those values to query the record

    after all, the autonumber is just a surrogate key, there should be other columns which you can use to uniquely fetch back the row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •