We help IT Professionals succeed at work.

Recordset Paging using Stored Procedure

pappala
pappala asked
on
Medium Priority
336 Views
Last Modified: 2012-05-04
Hi,

I have created a stored procedure to search data. It is pretty simple one too. I wanted to get a recordset by executing the stored procedure and show 100 records for page. But It is showint the provider not able to support the operation when i try setting absolute page value, Also I am getting recordcount as -1.

The stoered proc is

CREATE PROCEDURE GetStocklist

@partno varchar(24),
@searchtype varchar(10)
 AS

begin
declare @cmd varchar(500);
set @cmd = ''
print @cmd

set @cmd = "select part_no,dc,mf,qty,price,dateofferred from Availables where part_no"

if @searchtype = "full"
     begin
     set @cmd = @cmd + "= " + @partno;
     end
else
     begin
     set @cmd = @cmd +  " like '%" + @partno + "%'";
     end
print @cmd
execute (@cmd)
end

the Active server Page page uses

cmd.commandtype= adcmdstoredproc
rs.cursortype = 1 'adopenstatic (I tried 3 also)
rs.locktype = 4 'adlockReadonly (I tried 1 also)
rs.pageSize = 100
set rs = cmd.execute
.
.
.
.
rs.AbsolutePage = pageno (here I am getting error)



however when i print the cursortype and locktype after the cmd.execute statement, I am getting values 0 and 1 respectively.

Can you throw some light on it.
Comment
Watch Question

Commented:
Just for idea:
create proc paging (@rec_per_page int, @page int, @partno varchar(24)) as
set nocount on
--assume table.id is identity
select identity(int, 1,1) as id_tmp, cast(id as int) as id into #tmp_paging from table where part_no = @partno
select * from #tmp_paging as tmp
inner join table as t on tmp.id = t.id
where tmp.id_tmp BETWEEN (@rec_per_page * @page) AND (@rec_per_page * (@page + 1) - 1)
drop table #tmp_paging
go

In ASP:
intRecPerPage = 100
intPage = 0 'First page
objRS = objConn.Execute("exec paging " & intRecPerPage & "," & intPage & "," & part_no)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Simply change this statment:
set rs = cmd.execute
into:
rs.open = cmd

The problem is that cmd.execute will create a completely new recordset object, erasing the one you created and configured on the lines above

Cheers
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
same question in ASP:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20185243

(and same answer :-)

Author

Commented:
Thank you all for the help. I think the problem I got is because it is not accepting values like adocmdstoredproc etc. I have included it in the page and now. It was working fine. Any way I want to award points to angelll, because i know it is correct answer, I tried his solution even before placing the question.

Thank you all.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.