Split it
-
Hi all, I have a table with 50 rows I need to split the resoults of the select to pages. page 1 will have the 1st 15 rows page 2 will have the 2nd 15 rows page 3 will have the 3rd 15 rows page 4 will have the remaining 5 rows is there a query that can make my life easy somthing like
SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)thanks -
Hi all, I have a table with 50 rows I need to split the resoults of the select to pages. page 1 will have the 1st 15 rows page 2 will have the 2nd 15 rows page 3 will have the 3rd 15 rows page 4 will have the remaining 5 rows is there a query that can make my life easy somthing like
SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)thanksyou can use a datagrid........or a grid like control... yes it is possible to split your record in a stored procedure...
Tirtha Do not go where the path may lead, go instead where there is no path and leave a trail. Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist
-
Hi all, I have a table with 50 rows I need to split the resoults of the select to pages. page 1 will have the 1st 15 rows page 2 will have the 2nd 15 rows page 3 will have the 3rd 15 rows page 4 will have the remaining 5 rows is there a query that can make my life easy somthing like
SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)thanksSqlDataAdapter.Fill(DataSet, Start, TotalRows, TableName)
**
"The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call did not execute," said Internet Explorer, when I tried to access a deceased [window] object using JavaScript.
::..:.:..:: KiRtAN GoR ::..:.:..::** -
SqlDataAdapter.Fill(DataSet, Start, TotalRows, TableName)
**
"The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call did not execute," said Internet Explorer, when I tried to access a deceased [window] object using JavaScript.
::..:.:..:: KiRtAN GoR ::..:.:..::**Thank you for your answer I think SqlDataAdapter is in use by C# or VB but I'm using Visual Studio to create reports, so I need a query to do that. I thought about somthing like that
**DECLARE @page_num int DECLARE @rows_in_page int DECLARE @temp1 TABLE ( aaa nVarchar(100), bbb nVarchar(200), ccc float, ddd int) DECLARE @SQL nvarchar(4000) SET @page_num = 3 SET @rows_in_page = 15 INSERT INTO Temp1 = SELECT * FROM myTable** [[[ now @temp has all records ]]] **[--> some code to delete the first 30 records ((page_num-1)*rows_in_page) <--]** [[[ now @temp1 has pages 3,4,5,6,.... ]]] **SET @SQL = 'SELECT TOP ' + @rows_in_page + ' * from @temp1'** [[[ now the variable @SQL = "SELECT TOP 15 * from @temp1" ]]] **EXECUTE (@SQL)** [[[ the EXECUTE will give me the first 15. That means only page 3 ]]]Can anyone post the missing code ? -
Hi all, I have a table with 50 rows I need to split the resoults of the select to pages. page 1 will have the 1st 15 rows page 2 will have the 2nd 15 rows page 3 will have the 3rd 15 rows page 4 will have the remaining 5 rows is there a query that can make my life easy somthing like
SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)thanksHi It can be done. Calculate startpage and endpage using the parameters @rows & @currentpage. You need to pass two values from the front-end that is current page and number of rows per page. See below example with paging condition and I used temporary tables with identity column for filtering pages. Hope you got it.
use northwind go --create temporary table to store all rows with identity column or serial number for page filter CREATE TABLE #OrdersPage ([Iden] [int] IDENTITY(1,1) NOT NULL, OrderId int,CustomerID nchar(10)) declare @startpage int declare @endpage int declare @rows int declare @currentpage int set @rows = 10 -- number of rows per page from the front end set @currentpage = 1 -- pass current page from the front end set @endpage = (@rows * @currentpage) + 1 set @startpage = @endpage - @rows print @startpage print @endpage insert into #OrdersPage select top 50 OrderID, CustomerID from Orders select * from #OrdersPage where Iden >= @startpage and Iden < @endpage -- set paging condition here using the column 'Iden' from the temporary table drop table #OrdersPageHarini
-
Hi It can be done. Calculate startpage and endpage using the parameters @rows & @currentpage. You need to pass two values from the front-end that is current page and number of rows per page. See below example with paging condition and I used temporary tables with identity column for filtering pages. Hope you got it.
use northwind go --create temporary table to store all rows with identity column or serial number for page filter CREATE TABLE #OrdersPage ([Iden] [int] IDENTITY(1,1) NOT NULL, OrderId int,CustomerID nchar(10)) declare @startpage int declare @endpage int declare @rows int declare @currentpage int set @rows = 10 -- number of rows per page from the front end set @currentpage = 1 -- pass current page from the front end set @endpage = (@rows * @currentpage) + 1 set @startpage = @endpage - @rows print @startpage print @endpage insert into #OrdersPage select top 50 OrderID, CustomerID from Orders select * from #OrdersPage where Iden >= @startpage and Iden < @endpage -- set paging condition here using the column 'Iden' from the temporary table drop table #OrdersPageHarini
Thanks... That helped me alot :)