pcfountain
Posts: 35
|
| Posted: 06/08/2004, 9:26 AM |
|
Hi,
I am having problems reading/writing data in the After Insert event of a record form. My basic setup is this: I have a record form that inserts into table nusers. In the After Insert event I want to retrieve the userid I just inserted, and use that to insert data into a second table, registration. The problem is, I am not getting the latest record I just inserted into the nusers table. If I pull the record using max(userid), it pulls the previous record. If I pull it using the username I just inserted, it says the record does not exist. But I know the record exists, because when I pull down the database it is there. Do I need to insert a delay, or reset my database connection somehow?? This is very confusing. I am using ASP/Access. I can't post my exact code now but I will this afternoon when I get home, if needed.
_________________
Paul Fountain
http://www.pcfountain.com |
 |
 |
peterr
Posts: 5971
|
| Posted: 06/09/2004, 12:55 AM |
|
There is a small chance that some delay is needed but it would help to see your code to check if there are no other potential issues.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
pcfountain
Posts: 35
|
| Posted: 06/09/2004, 6:53 AM |
|
I have now fixed this by using the default DBnanny connection which CCS already has open. I was creating my own connection and record source in the After Insert event, and this appeared to be the problem.
_________________
Paul Fountain
http://www.pcfountain.com |
|
|
|
ghtracey
Posts: 23
|
| Posted: 06/09/2004, 10:05 AM |
|
Instead of retrieving the ID as you are now, why not use MySQL's LAST_INSERT_ID() function (select last_insert_id) which returns the ID of the last record inserted into an auto-incremented field. Just a thought.
On a side note... I hope this is a very low traffic site. Imagine two users creating accounts, and submitting at virtually the same time. Each session runs the SQL insert, creating a new user. Then each session retrieves the max (or last created) userID from the users table and creates a registration entry. Your database has just become corrupt, since one of those two users is now missing a registration entry, and the other one has a 50% chance that his data is wrong.
If the registration table has a 1 to 1 relationship with the users table (ie: every user has exactly one record in registrations, and vice versa) then the registrations table should be incorporated into the user table. If not, then you should manage key generation on your own, so that you know for certain which ID has been created by an insert.
Just my 2 cents that may help prevent you from having corrupt data.
_________________
Graham Tracey
Council of Yukon First Nations |
|
|
|
pcfountain
Posts: 35
|
| Posted: 06/09/2004, 11:19 AM |
|
As I stated before, I am using ASP/Access, so the LAST_INSERT_ID() function is not an option. This is a low traffic site, so I'm not overly concerned about simultaneous use. But I still add some additional safety to the lookup by checking the username, which should be unique.
_________________
Paul Fountain
http://www.pcfountain.com |
|
|
|
|
