In search of help please

daveyy · #1 (**permalink**) 04-22-08, 09:01

I am trying to construct an insert record statement on an ASP page to insert data into Oracle. I need to bring back the unique ID which is created in the database as the data goes in. This is not working. I have attached the relevant code below. I've created the session variable and dragged it onto the new page, but I'm getting error on line 115 (red text). Could someone have alook at this and offer some suggestions. Maybe I need to start again.

Code:

If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT @" & "@IDENTITY AS Ident"
    Set rsLastIdent = MM_editCmd.Execute
    if NOT rsLastIdent.EOF then
    strLastIdent = rsLastIdent.Fields.Item("Ident").Value
         ' place the new record ID in a session variable called "NewID"
    Session("NewID") = strLastIdent
    End If
    MM_editCmd.ActiveConnection.Close 
	End If
	End If

gvee · #2 (**permalink**) 04-22-08, 09:20

You cannot open a recordset on an execute connection object.

Code:

myRecordset.Open "SELECT field1 FROM table1", myOpenConnection

daveyy · #3 (**permalink**) 04-22-08, 10:26

Quote:

Originally Posted by georgev

You cannot open a recordset on an execute connection object.

Code:

myRecordset.Open "SELECT field1 FROM table1", myOpenConnection

Many thanks for a speedy reply georgev but I don't understand your reply. Are you saying that this is not possible?

gvee · #4 (**permalink**) 04-22-08, 11:37

It is entirely possible, but you are confusing your methodology.

To perform an insert, update or delete, you execute the SQL command against the connection.

Code: