mljonzs
Posts: 124
|
| Posted: 06/12/2007, 3:13 PM |
|
Okay, the title isn't too good but I'm not sure what wording to use.
Here's my problem. I insert a record into the database where the key of the table is an auto-increment field so I don't want to (nor can I) populate it in Before Insert. Once the record gets inserted on the submit of the page, I need to re-display the same page with the newly added record available for updating.
The problem? The page doesn't know what to display because it looks for the primary key field value in the URL to determine if it is in add/ or update mode and I don't have the primary key value to give to the page until after I insert the record and I don't know how to ensure that I get that value?? Make sense? I sure hope someone can understand what I'm trying to say and smack me with some sense on how to do this correctly.
I can return the user to the search/list page where I can get the primary key value to use as a link but that requires the user to find the record and click a link to go back to the maintenance page which I do not want them to have to do.
Any thoughts??
Thanks
mlj
_________________
What does not begin WITH God, will end in failure!
|
 |
 |
DeanCovey
Posts: 22
|
| Posted: 06/12/2007, 6:45 PM |
|
When you display the page, have a variable: Key=dmax(key,tbl)+1
then Mypage.asp?Record=Key
Instead of an auto increment, you could use your own variable.
On insert: Key=dmax(key,tbl)+1
Something like that.
Then when you redisplay the page, the link would become: Mypage.asp?Record=Key
|
|
|
|
mljonzs
Posts: 124
|
| Posted: 06/13/2007, 8:08 AM |
|
In regards to above response, I could be doing things wrong, but when I try doing what was described, I occasionally get primary key violations on insert due to more than one user inserting a record at the same time...
...in trying to figure out how to do this, I need to know how to check errors for the exact error. In other words, I want to be able to tell if the DB error that occurred did so because of a primary key violation so I can try again. All I seem to be able to do is check for any error but I don't know how to determine what the error is?
_________________
What does not begin WITH God, will end in failure!
|
|
|
|
Wkempees
|
| Posted: 06/13/2007, 8:47 AM |
|
Yes you would be getting duplicate keys, because you are interfering
with your autonum. Also you are not doing what you describe in the
initial post.
Think closely about what you intend to build.
First
a new record is created by a user.
that is submitted and inserted into the database
Next
you want to redisplay that last inserted record
for update ?
In plain english you need to:
Let user create a new record, insert that as normal.
Then (AfterInsert) you reget the last inserted record ID and call the
form with that ID which will present that to the user in editmode.
Correct?
Enough details?
If not search the forum for "last insert" and find the ways described to
do this.
Also search for redirecting in the docs, ASP flavour.
Also in the docs/help is described how to redirect to a page adding url
parms.
Walter
(If needed can supply code although ASP not my COT)
|
|
|
|
Slopey
Posts: 33
|
| Posted: 06/13/2007, 9:54 AM |
|
Depending on what database you're using, you should use the identity value within SQL to get the most recently added key for a given connection.
ie SELECT @@IDENTITY AS MyKey
which will return the unique key of the last added record.
Maintaining a separate variable, or even searching for the "last" or highest key value won't work on a high traffic site - you can't guarentee you're getting the right key unless you either use the identity method above, or write a unique identifier into the record and search for that to get the key (ie the session id for the current session). Otherwise, you'll end up with the wrong numbers.
|
|
|
|
mljonzs
Posts: 124
|
| Posted: 06/13/2007, 10:45 AM |
|
Thank you all for the responses. I know I was bouncing back and forth in my thought process and for that I apologize!
I have been able to get my pages to work as desired without changing the database structure - I added logic in AfterExecuteInsert to retreive my newly added key value and then set the Redirect variable including that new value in the URL to return me to the same page for editing.
Thanks again to all who offered information! I appreciate it VERY much.
_________________
What does not begin WITH God, will end in failure!
|
|
|
|
wkempees
|
| Posted: 06/13/2007, 2:45 PM |
|
Well done.
It is always better to get the idea than to get the solution.
Walter
"mljonzs" <mljonzs@forum.codecharge> schreef in bericht
news:646702d452b8a4@news.codecharge.com...
> Thank you all for the responses. I know I was bouncing back and forth in
> my
> thought process and for that I apologize!
>
> I have been able to get my pages to work as desired without changing the
> database structure - I added logic in AfterExecuteInsert to retreive my
> newly
> added key value and then set the Redirect variable including that new
> value in
> the URL to return me to the same page for editing.
>
> Thanks again to all who offereed information! I appreciate it VERY much.
>
>
> _________________
> Happy coding!
> mlj
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 06/13/2007, 4:14 PM |
|
Quote wkempees:It is always better to get the idea than to get the solution.
yeah and don't forget to stop and eat the roses :)
_________________
Walter Kempees...you are dearly missed. |
|
|
|
