Thread

insert/select returned table

Richard Lynch <lynch@lscorp.com> — 1998-06-10T23:01:27Z

Okay, so this is actually in Access and ASP, but that's not my fault!
[The company I work for is really messed up in some ways.]

Anyway, it's an SQL question, and I don't want to actually join an
Access/ASP list, if such exist...

So, I've got a table of records with an "autonumber" unique ID.

When a web user inserts a new record, I need the ID back from *that* record
in an atomic operation.  Since it is on the web, the potential for a race
condition is high.

insert into story (story_id, title) select Max(story_id) + 1, 'Title' from story

worked fine in Access.  Well, it always gave one higher than the actual
value inserted for the ID, but I can subtract 1.  Alas, when I send it over
the web, it inserts, but when attempting to get the resulting ID, I get a
message about a closed object being inaccessible.  Presumably, Access is
trashing the temp table from this query. :-(

So, how do I go about doing this the right way?...

NOTE:  There *ARE* records whose only distinct field is ID, so I can't just
follow up with a select for the data input.

If your answer is Access/ASP specific, please reply direct only.  No sense
cluttering this list more than I have.

PS  I guess I'd better join an Access/ASP list... Anybody know of one?

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com