I have a page that when a user submits a form the user's information is 
inserted into a database. When the new record is inserted the database 
creates an ID number for that record using an Autonumber data type (MS 
Access). 

After the user submits their information I would like to redirect them to 
a "thank you page" that uses their name and gives them their unique ID 
number generated by the database. For example: Thank you Paul, for listing 
with us. Your reference number is: 123.

How do I do this?

there's a way to do this in SQl in a stored procedure, but in access a
sloppy way to do it is build a select statement using  max(ID)

Select max(id) from thetable where datetime = thedatetimeofinsert

Don't know if you can use a view or query for this sort of thing in access
instead of this yucking Select statement.

Karri



-----Original Message-----
From: Paul Forman [mailto:pwog@m...]
Sent: Monday, May 20, 2002 3:52 PM
To: Access ASP
Subject: [access_asp] retrieving autonumber from database


I have a page that when a user submits a form the user's information is 
inserted into a database. When the new record is inserted the database 
creates an ID number for that record using an Autonumber data type (MS 
Access). 

After the user submits their information I would like to redirect them to 
a "thank you page" that uses their name and gives them their unique ID 
number generated by the database. For example: Thank you Paul, for listing 
with us. Your reference number is: 123.

How do I do this?

hi,

could you create a temp session or cookie that went with the pages and
stored the info?

for example

1st page, prompt user for details. set a temp session/cookie that stores the
username they enter. you can get this from the text box you use for the
username, eg:

Response.Cookies("cookieName")("txtUserName") = Request.Form("txtUserName")

2nd page, make a record set search that produces the results based on the
information held in the cookie, ie the username they had entered. so the
search would basically say 'show user info from the users table where the
user name equals the username held in the cookie data'.

you can then delete the cookie, set the delete time on it to be very short
or add to it to store useful info for when the user logs back into the site.

i'm not sure whether this is the best way of doing this, but i've used it on
a site and it seems to work!!

hope this helps,

jake



on 20/5/02 8:51 pm, Paul Forman at pwog@m... wrote:

> I have a page that when a user submits a form the user's information is
> inserted into a database. When the new record is inserted the database
> creates an ID number for that record using an Autonumber data type (MS
> Access). 
> 
> After the user submits their information I would like to redirect them to
> a "thank you page" that uses their name and gives them their unique ID
> number generated by the database. For example: Thank you Paul, for listing
> with us. Your reference number is: 123.
> 
> How do I do this?

No,

This is a bad way of doing it since you can't control concurrency issues. If
you do an INSERT for User A, then try to get MAX(ID) after USER B has also
done an insert, then you will get the wrong autonumber.

Instead, use SELECT @@Identity as per the code here:
www.adopenstatic.com/experiments/fastestautonumber.asp

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: retrieving autonumber from database


: there's a way to do this in SQl in a stored procedure, but in access a
: sloppy way to do it is build a select statement using  max(ID)
:
: Select max(id) from thetable where datetime = thedatetimeofinsert
:
: Don't know if you can use a view or query for this sort of thing in access
: instead of this yucking Select statement.
:
: Karri
:
:
:
: -----Original Message-----
: From: Paul Forman [mailto:pwog@m...]
: Sent: Monday, May 20, 2002 3:52 PM
: To: Access ASP
: Subject: [access_asp] retrieving autonumber from database
:
:
: I have a page that when a user submits a form the user's information is
: inserted into a database. When the new record is inserted the database
: creates an ID number for that record using an Autonumber data type (MS
: Access).
:
: After the user submits their information I would like to redirect them to
: a "thank you page" that uses their name and gives them their unique ID
: number generated by the database. For example: Thank you Paul, for listing
: with us. Your reference number is: 123.
:
: How do I do this?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I know about the @@Identity in SQL Server--can you do that in Access?

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Monday, May 20, 2002 8:47 PM
To: Access ASP
Subject: [access_asp] RE: retrieving autonumber from database


No,

This is a bad way of doing it since you can't control concurrency issues. If
you do an INSERT for User A, then try to get MAX(ID) after USER B has also
done an insert, then you will get the wrong autonumber.

Instead, use SELECT @@Identity as per the code here:
www.adopenstatic.com/experiments/fastestautonumber.asp

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: retrieving autonumber from database


: there's a way to do this in SQl in a stored procedure, but in access a
: sloppy way to do it is build a select statement using  max(ID)
:
: Select max(id) from thetable where datetime = thedatetimeofinsert
:
: Don't know if you can use a view or query for this sort of thing in access
: instead of this yucking Select statement.
:
: Karri
:
:
:
: -----Original Message-----
: From: Paul Forman [mailto:pwog@m...]
: Sent: Monday, May 20, 2002 3:52 PM
: To: Access ASP
: Subject: [access_asp] retrieving autonumber from database
:
:
: I have a page that when a user submits a form the user's information is
: inserted into a database. When the new record is inserted the database
: creates an ID number for that record using an Autonumber data type (MS
: Access).
:
: After the user submits their information I would like to redirect them to
: a "thank you page" that uses their name and gives them their unique ID
: number generated by the database. For example: Thank you Paul, for listing
: with us. Your reference number is: 123.
:
: How do I do this?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ken, 

I am reading these while working and haven't been watching the conversation 
as closely as I should--

I agree that the Max(id) is sloppy as I said--really only best applied when
you know that only one user is going to be accessing the app/database at a
time.  
In reading the link below, it is clear that
@@Identity DOES work with Access--I will be honest and tell you that I do
more in
SQL Server than I do in Access.   But it looks like you have to watch which
version of MDAC you have -- 2.1 or greater.  Something to watch out for on
older machines
if you have a piece of shrink wrapped shareware or something.

This was really informative-- :-)

Thanks.

Karri

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Monday, May 20, 2002 8:47 PM
To: Access ASP
Subject: [access_asp] RE: retrieving autonumber from database


No,

This is a bad way of doing it since you can't control concurrency issues. If
you do an INSERT for User A, then try to get MAX(ID) after USER B has also
done an insert, then you will get the wrong autonumber.

Instead, use SELECT @@Identity as per the code here:
www.adopenstatic.com/experiments/fastestautonumber.asp

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: retrieving autonumber from database


: there's a way to do this in SQl in a stored procedure, but in access a
: sloppy way to do it is build a select statement using  max(ID)
:
: Select max(id) from thetable where datetime = thedatetimeofinsert
:
: Don't know if you can use a view or query for this sort of thing in access
: instead of this yucking Select statement.
:
: Karri
:
:
:
: -----Original Message-----
: From: Paul Forman [mailto:pwog@m...]
: Sent: Monday, May 20, 2002 3:52 PM
: To: Access ASP
: Subject: [access_asp] retrieving autonumber from database
:
:
: I have a page that when a user submits a form the user's information is
: inserted into a database. When the new record is inserted the database
: creates an ID number for that record using an Autonumber data type (MS
: Access).
:
: After the user submits their information I would like to redirect them to
: a "thank you page" that uses their name and gives them their unique ID
: number generated by the database. For example: Thank you Paul, for listing
: with us. Your reference number is: 123.
:
: How do I do this?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: retrieving autonumber from database


: In reading the link below, it is clear that
: @@Identity DOES work with Access--I will be honest and tell you that I do
: more in
: SQL Server than I do in Access.   But it looks like you have to watch
which
: version of MDAC you have -- 2.1 or greater.  Something to watch out for on
: older machines
: if you have a piece of shrink wrapped shareware or something.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You'd have to be using WindowsNT v4 to have anything older than MDAC 2.1.

Windows2000 shipped with MDAC 2.5 and WindowsXP ships with MDAC 2.6

If you're still on NT, and you're still using something older than MDAC v2.1
I suggest you upgrade because the older MDACs had both bugs, and
vulnerabilities that have been patched in newer MDACs.

Lastly, the @@Identity is a feature of the Jet.OLEDB.4.0 Provider, which you
can install separately to an MDAC, by downloading JetSP3:
www.microsoft.com/data/, so even if you have an old MDAC, you can still get
the @@Identity feature by install Jet SP3 separately.

HTH

Cheers
Ken