Hi,

Below is a SP that I am using to poulate a temp table with selected records for paging through within an asp page.

I have tried to use @@ROWCOUNT AND COUNT but I can't seem to get the syntax correct I have tried

select count("row_id") as totalrecords but this just returns the value of one.

Every thing else I have tried just produces the "Item cannot be found in the collection corresponding to the requested name or ordinal" error on the asp page. Can anybody help with getting the number of rows returned from this temp table?

There is already a COUNT torwards the bottom of the SP but I can't seem to access it's value and pass it into the asp page

Thanks for any help
peter




CREATE PROCEDURE dbo.employee_cil_page
    (
     @Page INT,
     @RecsPerPage INT,
     @NID VARCHAR(3),
     @CID VARCHAR(10),
     @EMP VARCHAR(10)
    )
AS
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #CILTempItems
(
    myID INT IDENTITY,
    row_id INT,
    NETWORK_ID VARCHAR(3),
    NODE_ID VARCHAR(5),
    CORP_ID VARCHAR(10),
    EMP_ID VARCHAR(10),
    CIL_ID VARCHAR(20),
    CIL_TYPE VARCHAR(1),
    CIL_KEY VARCHAR(10),
    CIL_DESC VARCHAR(20),
    CIL_BILL_FLAG VARCHAR(1),
    RENTAL VARCHAR(1),
    START_DATE datetime,
    END_DATE datetime,
    username VARCHAR(50),
    action_date datetime,
    user_action CHAR(1)

)


-- Insert the rows from tblItems into the temp. table


INSERT INTO #CILTempItems (row_id,NETWORK_ID,NODE_ID,CORP_ID,EMP_ID,CIL_ID,C IL_TYPE,CIL_KEY,CIL_DESC,CIL_BILL_FLAG,RENTAL,STAR T_DATE,END_DATE,username,action_date,user_action)

SELECT row_id,NETWORK_ID,NODE_ID,CORP_ID,EMP_ID,CIL_ID,CI L_TYPE,CIL_KEY,CIL_DESC,CIL_BILL_FLAG,RENTAL,START _DATE,END_DATE,username,action_date,user_action

FROM Employee_cil

WHERE NETWORK_ID = @NID AND CORP_ID= @CID AND EMP_ID = @EMP


-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)


-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords =
    (
     SELECT COUNT(*)
    FROM #CILTempItems TI
    WHERE TI.myID >= @LastRec

    )
FROM #CILTempItems
WHERE myID > @FirstRec AND myID < @LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

When you run SQL that returns more than one set of rows, the ADODB recordset object in ASP will actually contain multiple recordsets. I think you can call it like this:

Set objRecordSet = objRecordSet.NextRecordset()

This will advance and return to the next recordset in the collection. You can then access the fields in the second part of your SPROC.

Peter
------------------------------------------------------
Work smarter, not harder.