Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 3rd, 2010, 01:41 PM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default Operation is not allowed when the object is closed.

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 June 3rd, 2010, 03:22 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old June 3rd, 2010, 03:31 PM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default sproc

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
 
Old June 3rd, 2010, 04:39 PM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by adamhw View Post
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 June 3rd, 2010, 05:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
The Following User Says Thank You to Old Pedant For This Useful Post:
adamhw (June 4th, 2010)
 
Old June 4th, 2010, 05:57 AM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default thanks very much

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

Much appreciated.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Operation is not allowed when the object is closed. Hannibal Classic ASP Basics 0 April 7th, 2010 10:45 AM
Operation is not allowed when the object is closed. davfergus Classic ASP Databases 2 January 29th, 2010 05:29 PM
Operation is not allowed when the object is closed kingroon Classic ASP Databases 2 February 5th, 2008 10:29 AM
Operation is not allowed when the object is closed kah Javascript How-To 2 February 16th, 2005 07:20 AM
operation is not allowed when object is closed shoakat Classic ASP Databases 1 November 26th, 2004 12:17 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.