pappala
asked on
Recordset Paging using Stored Procedure
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,da
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.
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,da
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.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thank you all.
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)