Having problems returning total rows to asp
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
CREATE PROCEDURE dbo.employee_cil_page
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #CILTempItems
myID INT IDENTITY,
-- 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
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!
FROM #CILTempItems TI
WHERE TI.myID >= @LastRec
WHERE myID > @FirstRec AND myID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF