Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Auto ID

Status
Not open for further replies.

anon47

Programmer
Nov 28, 2006
80
US
IN access when you start typing a value in say the Name Field the system generates an auto id while you’re in the record. In SQL however it makes you wait until the editing is done.

Is there any way to change the way SQL handles this so that you know what the auto id is before you finish the record? I need this so to provide it to the user on signup.
 
Hey Philhedg

How do I insert a record before accepting input?
 
Hi there, Anon.

One method of handling this involves inserting a record with default values. Grab the identity value and store it. Validate and accept the user input, and do an UPDATE against the record you inserted. Cancellation would trigger a DELETE.

--
INSERT INTO Mytable
(field,field,etc....)
VALUES
(Accepteddefaultvalue, etc...)

RETURN @@SCOPE_IDENTITY()

[Store the return value]

...UI actions ...
...validation...
User hits submit, then
UPDATE Mytable
Set field=form.value,
etc.
WHERE ID = [saved identity value]

User cancels, then
DELETE FROM Mytable
WHERE ID = [saved identity value]

This can get messy with dropped and abandoned sessions. You'd have to clean the table of default-value records.

You may want to re-think why the user needs the identity value BEFORE the information is committed to the database. Or why the user needs the identity value at all.
 
Phil makes all good points.

And note it's Scope_Identity() without the @ signs.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top