ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. classic ASP

Question

I know this is probably a dupe of another question but i really have no idea, i've searched all over and tried everything but it still seems to be giving me the same error of;

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

If someone could help me with this it would be greatly appreciated, I'm using classic ASP, SQL and HTML;

My classic ASP code is;

If(Request.Form("submitBtn"))<>""Then
  'Initialise the db connection
  Set objDBConn = Server.CreateObject("ADODB.Connection")
  objDBConn.Open "Provider=sqloledb;Data Source=OLIVERBEELEY;Initial Catalog=HolidayEmployeeBooker;User ID=HBA;Password=HBA;"

  'Initialise the command object
  Set objDBCommand = Server.CreateObject("ADODB.Command")

  objDBCommand.ActiveConnection = objDBConn
  objDBCommand.CommandText = "spNewHoliday"
  objDBCommand.CommandType = adCmdStoredProc

  'Set the parameters             
  objDBCommand.Parameters.Append objDBCommand.CreateParameter("@StartDate", adDate, adParamInput,200)
  objDBCommand.Parameters.Append objDBCommand.CreateParameter("@EndDate", adDate, adParamInput,200)
  objDBCommand.Parameters.Append objDBCommand.CreateParameter("@EmployeeID", adVarChar, adParamInput,200)            
  objDBCommand.Parameters.Append objDBCommand.CreateParameter("@Reason", adVarChar, adParamInput,200)

  objDBCommand("@StartDate") = Request.Form("from")
  objDBCommand("@EndDate") = Request.Form("to")
  objDBCommand("@EmployeeID") = Session("UserID")
  objDBCommand("@Reason") = Request.Form("comments")

  'Initialise the Recordset
  Set objDBRS = Server.CreateObject("ADODB.RecordSet")

  'Execute 
  objDBRS.open objDBCommand,,adOpenForwardOnly

  if not objDBRS.EOF then
    Session("BookingValid") = objDBRS(0)
  end if

  if Session("BookingValid") = "ErrorBookingHoliday" then
     response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert(""Holiday CANNOT be booked."");</SCRIPT>")
  end if
  if Session("BookingValid") = "ErrorBookingHoliday" then
     response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert(""Holiday has been requested."");</SCRIPT>")
  end if

  'Close and Destroy Objects - Start*******************************************************
  Set objDBCommand=nothing
  objDBConn.Close
  Set objDBConn=nothing
  'Close and Destroy Objects - End*********************************************************

  response.redirect("newbooking.asp")

end if 


%>

My Stored procedure its calling are;

PROCEDURE [dbo].[spNewHoliday]
(
@StartDate DATE,
@EndDate DATE,
@EmployeeID INT,
@Reason NVARCHAR (45)
)


AS
SET NOCOUNT ON
BEGIN



IF NOT EXISTS ( SELECT 1 
                FROM Holidays
                WHERE @StartDate = StartDate
                AND @EndDate = EndDate
                AND @EmployeeID = EmployeeID)

                BEGIN
                DECLARE @Validation INT
                    EXEC spBusinessRuleValidation @StartDate, @EndDate, @EmployeeID, @Validation OUTPUT


                    IF @Validation = 0
                        BEGIN
                            DECLARE @DaysOff AS INT
                            SELECT @DaysOff = (DATEDIFF(Day,@StartDate,@EndDate) +1)

                            UPDATE Employees
                            SET AnnualDaysHolidayAllowance = AnnualDaysHolidayAllowance - @DaysOff
                            WHERE @EmployeeID = Employees.ID                

                            INSERT INTO Holidays(EmployeeID, StartDate, EndDate, Duration, Status, Reason)
                            VALUES(@EmployeeID, @StartDate, @EndDate, @DaysOff,'Pending', @Reason)

                            SELECT Employees.AnnualDaysHolidayAllowance AS Daysleft FROM Employees WHERE @EmployeeID = Employees.ID
                        END
                        SET NOCOUNT ON
                    IF @Validation = 1
                        BEGIN
                            SELECT 'ErrorBookingHoliday' AS FailedBooking
                        END
                END

END

And the stored procedure that, that procedure is calling is;

PROCEDURE [dbo].[spBusinessRuleValidation]
(
@StartDate DATE,
@EndDate DATE,
@EmployeeID INT,
@Validation INT output
)

AS
SET NOCOUNT ON
    BEGIN
        DECLARE @JobRoleID INT
        DECLARE @MaxJobTypeAllowedOff INT       
        DECLARE @MaxEmployeesAllowedOff INT
        DECLARE @DateCount INT
        DECLARE @SameJobRoleOff INT
        DECLARE @DateCheck DATE

        SET @JobRoleID = (SELECT JobRoleID FROM Employees WHERE @EmployeeID = Employees.ID)
        SET @MaxJobTypeAllowedOff = (SELECT MaxEmployeesAllowedOff FROM JobRole WHERE @JobRoleID = JobRole.ID)
        SET @MaxEmployeesAllowedOff = 20
        SET @DateCheck = @StartDate
        SET @EndDate = DATEADD(DAY, 1, @EndDate)
        SET @Validation = '0'

        WHILE (@DateCheck <> @EndDate)
        BEGIN
            SET @DateCount = (SELECT COUNT (*) FROM Holidays WHERE Holidays.Startdate >= @DateCheck AND Holidays.EndDate <= @EndDate )
            IF @DateCount > @MaxEmployeesAllowedOff
                BEGIN
                    SET @Validation = '1'
                    BREAK
                END

            SET @SameJobRoleOff = (SELECT COUNT (*) JobRoleID
            FROM Employees
            RIGHT JOIN Holidays
            ON Employees.ID = Holidays.EmployeeID
            WHERE Employees.ID = @EmployeeID AND Holidays.Startdate >= @DateCheck AND Holidays.EndDate <= @EndDate AND Holidays.Status <> 'Declined')

            IF @SameJobRoleOff > @MaxJobTypeAllowedOff
                BEGIN
                    SET @Validation = '1'
                    BREAK
                END

            SET @DateCheck = DATEADD(DAY, 1, @DateCheck)
        END
    END

I am very new to SQL and ASP (about 3-4 months of experience) and need some help with this problem, any help would be appreciated even if its just to do with code that isnt the cause of my error! Thanks! OH this is the code where it goes wrong!

if not objDBRS.EOF then
  Session("BookingValid") = objDBRS(0)
end if

Answer 1

I have a few SPs that return a one-row recordset at the end of their operation, using something like

      SELECT @some_symbol some_column_name;

If I omit SET NOCOUNT ON from those stored procedures, then my classic ASP adodb recordset isn't returned correctly, and I get the "object is closed" error.

If I include SET NOCOUNT ON in those stored procedures, things work correctly.

The same is true if I run an ordinary block of SQL, not a stored procedure, that works the same way.

As far as Classic ASP goes, I am definitely in the "why ask why?" camp. I just have to get my maintenance work done.