Forums

This topic is locked

Knowing ID after record insertion

Posted 16 May 2002 14:52:32
1
has voted
16 May 2002 14:52:32 Germán Iurato posted:
Hi!
I'm making a postcard application, and after the user chooses the card and fills the fields, I insert the information in the database. Each record has a unique identifier set as an autonumeric field. Now, I have to send an email to the postcard's recipient with the URL of the postcard, and I have to know the card's unique identifier which was given automatically by the database. How can I get that number?
Thank you


Replies

Replied 16 May 2002 17:21:13
16 May 2002 17:21:13 Julio Taylor replied:
you cant try this:

when you submit the form, you can use one or more user-specified items (name, e-mail address and maybe a timestamp) to uniquely identify the record. Then you can retreive the record using the criteria and find out the ID of the particular record. If you use GET URL variables you should be able to do this.

i hope it helps

- Julio

P.S. i killed kenny
Replied 17 May 2002 02:28:26
17 May 2002 02:28:26 David Behan replied:
If you are using the standard insert record method...

Just after the following code:

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

And before the following code:

If (MM_editRedirectUrl <> "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If


Filter the recordset on the table that you inserted in based on Request.Form("email" and timestamp. Then extract the variable before the redirect script.

Regards

_________________________
David Behan - www.site-manager.com/af.asp?a=11&l=1tds9p6x2
Replied 17 May 2002 14:00:02
17 May 2002 14:00:02 Germán Iurato replied:
Thank you all,
Now I have another problem: I have a date/time field which is also auto generated by the database. I'm about to change it for me to assign the timestamp and and after that I will be able to filter the database on it. My question, now, is: When and How to assign a timestamp value to a field, I mean, where in the code to write it, and what to write. I'm using ASP pages / javascript.
Thank you again!

Replied 17 May 2002 14:09:08
17 May 2002 14:09:08 Julio Taylor replied:
the best way usually to assign timestamps is to have a TIMESTAMP field (MySQL) or the equivalent of it in the database you are using. the reason why i am saying this is because i find it tends to be much more useful than front-end date settings. Using access, you can set the default value to DATE() or NOW(). the database will automatically assign the current server time to that field upon insertion and update.

i hope this helps.

--J

P.S. i killed kenny
Replied 17 May 2002 16:52:06
17 May 2002 16:52:06 Germán Iurato replied:
Hi J!
The problem at setting automatically the default value to NOW() in an Access database is that I don't know it, so I won't be able to filter the database to get the auto ID...

Replied 17 May 2002 17:12:10
17 May 2002 17:12:10 Julio Taylor replied:
i see what you're saying.

the point of all this is to uniquely identify a record and pass its information accross so that you can retrieve its unique record_id....

why don't you use other criteria which is user-input (such as e-mail, name) to form a combination of distinctly different records, alongside a timestamp? You don't need to know the timestamp because it is used simply to sort that user's records chronologically.

example:

pass the variables 'user_name' and 'user_email' to the e-mail form.

using a GET variable filter, write an SQL query that looks something like this:

<pre id=code><font face=courier size=2 id=code> SELECT rec_id, user_name, user_email FROM table WHERE user_name = GET_NAME AND user_email = GET_EMAIL ORDER BY timestamp DESC </font id=code></pre id=code>

where GET_NAME and GET_EMAIL are the GET variables that you sent from the other form. This query, in theory, will show you a specific user's chosen cards, newest first, essentially giving you the newest one (i.e. the one with the latest timestamp) first, at record position 1.

Alternatively you could try changing the ORDER BY from timestamp to rec_id, showing the user's latest unique record ID, or even a combination of the two.

that's all i can think of for now, i'nm not sure how you would implement this in ASP as i am a PHP user.


i hope this helps.

--J

P.S. i killed kenny
Replied 17 May 2002 17:21:35
17 May 2002 17:21:35 Germán Iurato replied:
I didn't think it that way, now I understand and I'm sure it'll work. Thanks you a lot!

Reply to this topic