ASP & INSERT statements

artsapimp posted this at 15:58 — 13th March 2002.

Joined: Apr 2000

Instead of using rst.AddNew.... I would like to use an INSERT statement to add records into an access database. I have the INSERT statement working on SQL server but am having problems with Access 2000. Here's what I have...

<?php
strSQL = \"SET NOCOUNT ON; \" & _
            \"INSERT INTO [MainTable] \" & _
            \"(\" & _
                                                 ... Fields go here...
            \"); \" & _
            \"SELECT @@IDENTITY AS RecordID;\"
?>

Like I said earlier it works fine with SQL server but generates an error with Access 2000. The error is referring to SELECT @@IDENTITY AS RecordID. Is there a way to get the RecordID if doing this type of INSERT statement and using Access?

Free Math Test
Fun Math Games

Peter J. Boettcher posted this at 16:46 — 13th March 2002.

They have: 812 posts

Joined: Feb 2000

If you're using the Jet 4.0 OLEDB provider you should be able to use "SELECT @@IDENTITY"

If not there really is no reliable way to get the ID of the last record inserted. If there is always only going to be one person creating records then you could do something like "SELECT MAX(id) FROM table)" after you do your insert.

To see a VB example of using the Jet 4.0 OLEDB and @@IDENTITY check out this link: http://support.microsoft.com/default.aspx?id=kb;;Q232144

PJ | Are we there yet?
pjboettcher.com