Operation is not allowed when the object is closed.

adamhw · June 3rd, 2010, 01:41 PM

Hi there

I'm getting this error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

and I don't know why - I've tried a hundred things to fix it but obviously not right - wondering if someone can help me - here's my code:

strSQL = "EXEC sp_Insert_tblCustomer '" & Trim(Replace(Request("txtSurname"), "'", "''")) & "' , '" & Trim(Replace(Request("txtFirstname"), "'", "''")) & "', '" & Trim(Replace(Request("txtPostalCode"), "'", "''")) & "' , '" & Trim(Replace(Request("txtAddress1"), "'", "''")) & "', '" & Trim(Replace(Request("txtAddress2"), "'", "''")) & "', '" & Trim(Replace(Request("txtAddress3"), "'", "''")) & "' , '" & Trim(Replace(Request("txtAddress4"), "'", "''")) & "' , '" & Trim(Replace(Request("txtAddress5"), "'", "''")) & "' , '" & Trim(Replace(Request("txtCountry"), "'", "''")) & "', '" & Trim(Replace(Request("txtTel"), "'", "''")) & "', '" & Trim(Replace(Request("txtEmail"), "'", "''")) & "', '" & Trim(Replace(Request("txtPassword"), "'", "''")) & "' , '" & Trim(Replace(Request("txtHint"), "'", "''")) & "' , '" & Trim(Replace(Request("txtGym1"), "'", "''")) & "' , '" & Trim(Replace(Request("txtGym2"), "'", "''")) & "' , '" & Trim(Replace(Request("txtWorkout"), "'", "''")) & "', '" & Trim(Replace(Request("txtWorkout2"), "'", "''")) & "'"

con.execute(strSQL)

set rsCustomer = con.execute(strSQL)

rsCustomer.MoveFirst

intTblCustomerid = CLng(rsCustomer("customerid"))

Set rsCustomer = Nothing

con.Close
set con = Nothing

Where it says:

rsCustomer.MoveFirst

I get the error and I don't know how to resolve it.

Would appreciate your help in this.

Many thanks

Adam

Old Pedant · June 3rd, 2010, 03:22 PM

If that stored procedure does *NOT* do a SELECT, then no records will be returned and the recordset will, indeed, be closed.

So without seeing the stored proc code we can't know.

Also, you are calling the procedure *TWICE*!!!

Code:

con.execute(strSQL)  ' FIRST CALL
set rsCustomer = con.execute(strSQL) ' SECOND CALL
rsCustomer.MoveFirst ' THIS CODE IS POINTLESS
intTblCustomerid = CLng(rsCustomer("customerid"))

Quite possibly the SP is getting an error when you call it the second time, so of course it returns nothing and of course the recordset is then closed.

adamhw · June 3rd, 2010, 03:31 PM

Thanks Old Pedant

Here's the sproc for you to look at:

CREATE PROCEDURE [dbo].[sp_Insert_tblCustomer]

@surname varchar(100),
@firstname varchar(100),
@addresscode varchar(10),
@address1 varchar(50),
@address2 varchar(50),
@address3 varchar(50),
@address4 varchar(50),
@address5 varchar(50),
@country varchar(50),
@tel varchar(50),
@email varchar(50),
@password varchar(50),
@passwordhint varchar (200),
@gym1 varchar (100),
@gym2 varchar (100),
@workout varchar (2),
@postworkout varchar (4)

AS

IF EXISTS(SELECT 1 FROM tblCustomer WHERE email = @email)
BEGIN
SELECT -1 As customerid

INSERT INTO tblCustomer(surname, firstname, addresscode, address1,
address2, address3, address4, address5,
country, tel, email, [password], passwordhint, gym1, gym2, workout, postworkout)
VALUES(@surname, @firstname, @addresscode, @address1,
@address2, @address3, @address4, @address5,
@country, @tel, @email, @password, @passwordhint, @gym1, @gym2, @workout, @postworkout)

SELECT @@IDENTITY As customerid
END
GO

adamhw · June 3rd, 2010, 04:39 PM

Quote:

Originally Posted by adamhw

Thanks Old Pedant

The reason I'm calling it twice, is once to execute the sproc and then I need to call it because I need the id that has just been inserted in order to set a cookie shortly afterwards.

Here's the sproc:

CREATE PROCEDURE [dbo].[sp_Insert_tblCustomer]

@surname varchar(100),
@firstname varchar(100),
@addresscode varchar(10),
@address1 varchar(50),
@address2 varchar(50),
@address3 varchar(50),
@address4 varchar(50),
@address5 varchar(50),
@country varchar(50),
@tel varchar(50),
@email varchar(50),
@password varchar(50),
@passwordhint varchar (200),
@gym1 varchar (100),
@gym2 varchar (100),
@workout varchar (2),
@postworkout varchar (4)

AS

IF EXISTS(SELECT 1 FROM tblCustomer WHERE email = @email)
BEGIN
SELECT -1 As customerid

INSERT INTO tblCustomer(surname, firstname, addresscode, address1,
address2, address3, address4, address5,
country, tel, email, [password], passwordhint, gym1, gym2, workout, postworkout)
VALUES(@surname, @firstname, @addresscode, @address1,
@address2, @address3, @address4, @address5,
@country, @tel, @email, @password, @passwordhint, @gym1, @gym2, @workout, @postworkout)

SELECT @@IDENTITY As customerid
END
GO

Many thanks

Adam

Old Pedant · June 3rd, 2010, 05:45 PM

I think that stored procedure is BROKEN!!!

I think it is missing an ELSE!!

As that SP is coded, if the given email address does *NOT* exist then the code inside the IF is *NOT* performed:

Code:

IF EXISTS(SELECT 1 FROM tblCustomer WHERE email = @email)

So nothing is performed and no SELECT is used!

I think that SP needs to be fixed:

Code:

ALTER PROCEDURE [dbo].[sp_Insert_tblCustomer] 
@surname varchar(100), 
@firstname varchar(100),
@addresscode varchar(10), 
@address1 varchar(50), 
@address2 varchar(50), 
@address3 varchar(50), 
@address4 varchar(50), 
@address5 varchar(50), 
@country varchar(50), 
@tel varchar(50), 
@email varchar(50), 
@password varchar(50),
@passwordhint varchar (200),
@gym1 varchar (100),
@gym2 varchar (100),
@workout varchar (2),
@postworkout varchar (4)
AS
SELECT NOCOUNT ON 
 
IF EXISTS(SELECT 1 FROM tblCustomer WHERE email = @email)
BEGIN
    SELECT -1 As customerid
END
ELSE
BEGIN
    INSERT INTO tblCustomer(surname, firstname, addresscode, address1, 
                  address2, address3, address4, address5,
                  country, tel, email, [password], passwordhint,
                  gym1, gym2, workout, postworkout)
    VALUES(@surname, @firstname, @addresscode, @address1, 
                 @address2, @address3, @address4, @address5, 
                 @country, @tel, @email, @password, @passwordhint, 
                 @gym1, @gym2, @workout, @postworkout)
 
    SELECT @@IDENTITY As customerid
END

adamhw · June 4th, 2010, 05:57 AM

Thanks very much Old Pedant - you've definitely sent me in the right direction!

Much appreciated.