Received: from lscorp.com (poohbah.lscorp.com [207.152.64.2]) by hub.org (8.8.8/8.7.5) with ESMTP id TAA24966 for ; Wed, 10 Jun 1998 19:03:28 -0400 (EDT) Received: from [207.152.64.133] by lscorp.com with SMTP (Eudora Internet Mail Server 1.2); Wed, 10 Jun 1998 18:01:27 -0500 X-Sender: lynch@poohbah.lscorp.com Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" To: pgsql-sql@postgresql.org From: lynch@lscorp.com (Richard Lynch) Subject: insert/select returned table Date: Wed, 10 Jun 1998 18:01:27 -0500 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