SQL SELECT INSERTed data from Table
- by Noam Smadja
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
