SQL SELECT INSERTed data from Table

Question

its in ASP Classic. MS-Access DB.

i do: INSERT INTO Orders (userId) VALUES (123)"

what i want to retrieve is orderNumber from that row. its an auto-increment number.

so i did: SELECT orderNumber FROM Orders WHERE userId=123

but since it is on the same page, the SELECT returns: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

i've seen somewhere RETURNING orderNumber as variable but it was for oracle and i dont know how to implement it into my asp :(

set addOrder = Server.CreateObject("ADODB.Command")
        addOrder.ActiveConnection = MM_KerenDB_STRING
        addOrder.CommandText = "INSERT INTO Orders (userId) VALUES ("&userId&")"
        addOrder.CommandType = 1
        addOrder.CommandTimeout = 0
        addOrder.Prepared = true
        addOrder.Execute()

Dim getOrderNumber
Set getOrderNumber = Server.CreateObject("ADODB.Recordset")
        getOrderNumber.ActiveConnection = MM_KerenDB_STRING
        getOrderNumber.Source = "SELECT orderNumber FROM Orders WHERE userId=" & userId
        getOrderNumber.CursorType = 0
        getOrderNumber.CursorLocation = 2
        getOrderNumber.LockType = 1
        getOrderNumber.Open()

                    session("orderNumber") = getOrderNumber.Fields.Item("orderNumber").value

Answer 1

The following might be of help if you are using their method of adding records using ADO objects.

http://support.microsoft.com/kb/221931

But without actually seeing you Classic ASP code, it is hard to give you a proper answer.