Auto ID

[anon47]

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.

 

[anon47]

Hey Philhedg

How do I insert a record before accepting input?

 

[philhege]

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.

 

[ESquared]

Phil makes all good points.

And note it's Scope_Identity() without the @ signs.

 

[philhege]

Thanks, E^2. I was getting tired when I posted.