Returning n rows, setting start row, total number of rows

Hi,

I'm try to find out how ado.net can perform some of the things i am able to do in ado. Here is the scenario:
I will be searching a table that may have 1000's of rows. I want to be able to dictate how many rows (page size) to return and what the starting row number is and be able to find out how many total rows would be returned by the query.
Using old ado recordsets I was able to get the recordset and get the count or all the rows (.recordcount - ormaybe it was .rowcount) and then jump to the starting position (.absoluteposition).
How can I do the equivalent in .net? From looking at the sqldatareader it is a forward only cursor which is not going to help and the DataSet while it can start at a partilcular row how and tell it the number of rows to return how do I find out what the total number of rows that actually match the search criteria?
Thanks for any help.
eg
0
eg
11/8/2002 4:04:46 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

6 Replies
1240 Views

Similar Articles

[PageSpeed] 27

Here is an article that might help...

http://www.codeproject.com/aspnet/dspaging.asp

http://www.heliosfx.com
0
mxmissile
11/8/2002 3:29:44 PM

I have read the article and have an issue with it.
The code reads all the records into the dataset table and then implements paging. This may be ok for say 500 records but if there are 1000's then probably not. I have not been able to find any information on how the dataset manages database cursors. I assume it is client side as the ds is a standalone data store so reading 1000's records at a time is not a good idea.
It would appear that the most efficient way when using the DataSet would be to specify a start row and a page size in the .fill method (assuming the size of resultset can't be contolled via sql) and you get the number of records back that will be on a page
da.fill(ds, nStart, nPageSize)
but it still leaves the issue of how many total rows match the criteria. It looks like I would have to do a separate "count" sql statement before or after to get the total records.
0
eg
11/8/2002 4:54:19 PM
Hi,

I read through this issue, and the article posted as a help, but my problem seems slightly different (or maybe not).
I have a recordset of thousands of records, in which I want to allow a search for a particular Key value. (In the current case : in the users table, one particular UserID). I have a datagrid control that got a set pagelength of 10 records. Now, I want to find out what my pageindex should be, in order for the searched record to be displayed.
Public Function FindRec(ByVal MyTable As String, ByVal myKeyColumns As String, ByVal
MyFindString As String, ByVal MyConnectionString As String) As Long
Dim DS As DataSet
Dim sSQL As String
Dim r As DataRow
Dim i As Integer
Dim Keys() As String = Split(myKeyColumns, ";")
sSQL = "select * from " & MyTable
DS = GetData(MyConnectionString, sSQL)
With DS.Tables(0)
Dim arrPrimaryKey(UBound(Keys)) As DataColumn
For i = 0 To UBound(Keys) - 1
Dim myDataColumn As DataColumn = .Columns(Keys(i))
arrPrimaryKey(i) = myDataColumn
Next i
.PrimaryKey = arrPrimaryKey
r = .Rows.Find(MyFindString)
'Findrec= this should be the "absoluteposition" of the found row
End With
End Function

This Findrec value would then enable me to set the pageindex of my grid...
However, I can't seem to find the appropriate syntax to find the position of row r within Table 0 of my DataSet object
NOTE : GetData is another public function I use constantly
Public Function GetData(ByVal sConn As String, ByVal sSQL As String) As DataSet
Dim mySQLConnection As New SqlConnection(sConn)
Dim MySQLCommand As New SqlCommand(sSQL, mySQLConnection)
Dim MyDataAdapter As New SqlDataAdapter(MySQLCommand)
Dim MyDataSet As New DataSet()
DefaultErrorMsg = "No Error"
Try
mySQLConnection.Open()
MyDataAdapter.Fill(MyDataSet)
Return MyDataSet
Catch ex As Exception
DefaultErrorMsg = ex.ToString
End Try
mySQLConnection.Close()
MyDataSet = Nothing
MyDataAdapter = Nothing
MySQLCommand = Nothing
mySQLConnection = Nothing
End Function
0
rodeo
9/1/2003 1:36:15 PM
U can do this by SQL too.

implement a SQL query like this:
sql="Select *, (Select Count(*) from table B where B.id<A.id) as RowNumber from table A where Total between (" + MinValue + " and " + MaxValue + ")"
simply it does a numeration of each row and then you ask just the rows between MinValue and MaxValue
Does it helps?
Alexandre MP Gomes
A programmer never dies, only gosub without return!
0
Alexmipego
9/1/2003 4:37:39 PM
I forgotten... I think SQLServer2k have a function that returns u the number of that row... its quicker and save lots of processor...
Alexandre MP Gomes
A programmer never dies, only gosub without return!
0
Alexmipego
9/1/2003 4:39:24 PM
0
McMurdoStation
9/1/2003 9:56:59 PM
Reply:

Similar Artilces:

How to set the row number and get the row number for ("selected row) in Datagrid
I face a problem that i can't get the selected row of the datagrid in both server side script (VB.net) and client side script (javascript) As i can't use the button column / hyperlink column in the datagrid. Thus , i need to handle the change the background color of the selected row and also to get the selected row number for Update/Delete. Thanks in advance. This depends on how do you perform the selection. Do you want to implement chaning of the SelectedRow at the client side without any postback? If you have no issues with postback, then you can do s little trick here: 1. ...

The row number and offset of each row in the page should have a matching entry in row number table
Is the proper correction for the following to recreate the table BCPout/IN or table to table insert, or just drop and recreate the index? Row number is 0. Is there a way to correct just the data page? Table Corrupt: The row number and offset of each row in the page should have a matching entry in row number table; check this page (page#=772121 row#=0 offset in row number table=0) Table Corrupt: The row number and offset of each row in the page should have a matching entry in row number table; check this page (page#=772121 row#=0 offset in row number table=0) Table Corrupt: The row...

First Row, Next Row, Prior Row, Last Row
I have a SQL Anywhere 7.0 Database, have a table Product_detail which contains Column Name Datatype width Null Default batch_id char(10) No None prod_id Integer No None employee_id Integer No None quantity Integer No None price Numeric (14, 2) No None Its primary key is batch_id I have a Freeform Datawindow with this table I want to provide the first row, next row, prior row and last row Ho...

$sth->rows, doesn't return the correct number of rows return
------_=_NextPart_001_01C5518A.365E711F Content-Type: text/plain Hi Guys, When i do select quiery and then try to get count of rows like this in perl script from Oracle database and Informix: $rowCnt = $sth->rows; above line is being executed after the $sth->execute; This problem I am facing in Informix and Oracle both. Any suggestions or solution would be great help. Thanks Ravish ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of t...

Total row and row range
I posted an email in the repserver newsgroup regarding rs_subcmp and its inabililty to handle large tables. My question is this: can I get the number of rows in a table AND select a specific number of rows. I know that sounds basic, but if I could do this, I could build the rs_subcmp config files on the file. The tables continue to grow daily and most have composite keys. I would like to do this: select * from price order by hotel_id, capacity_type, price_id, dt, hotel_unit_type where "row" between 1 and 1000 select * from price order by hotel_id, capacity_type, pri...

retrieving from row # to row #.
I have over 750 000 rows to retrieve from a table using a datawindow, but my system can only handle approx 30 000 rows and I would like to start from the end of the table. Is it possible to set the row numbers to retrieve? For example, start the retrieve from row 750 000 and stop at row 720 000. There must be a way. thanks, Vern what backend are you using? <Vern> wrote in message news:6BA241541AAD7F2F0063357385256C40.0063358685256C40@webforums... > I have over 750 000 rows to retrieve from a table using a datawindow, but > my system can only handle approx 30 ...

adding a blank header row /row above the header row
is it possible to add a blank row above the header row in gridview for adding some links and buttons the links will respond to thae data in the grid and the buttons are for login / regisration etc....  if yes how it can be done or how do i achieve it. Its better that u create a table with a single row with columns matching the GridView columns. Set the table width same as the GridView width. This will make the GridView Look as if there is a row above its header.Please do not forget to click Mark As Answer on the post that helped you. Regards,Iam Srikanth Reddy....

accessing row by row
first of all sorry if i posted this in a wrong place but i'm new to powerbuilder and i have to develop a program quite quickly ( i only worked with acces until now) i have been provided a excel spreadsheet that contains all coloms and rows from all tables wich i have to insert in a database. The difficulty is that i can't use a datapipeline because i need to use a string function to check in wich table i have to put it: TABLENAME_COLUMNNAME -> i thought about using the string function to find out the right column in the right database. however i don't know how you ...

Get the Row Number or Row ID
Hi, I am using Sybase Central v4.3 with Interactive SQL to query the SQL Commands. My concern is how to get the rownumber or row id in select queries. Is there any in built commands in the ASE. If not please suggest me how to get the rownumber. Thanks.... Unless i'm mistaken, ASE does not use a rowid... it can have a timestamp, but it is updated with every modification to the row. http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.blocks/html/blocks/blocks34.htm -- Cory Sane [TeamSybase] Certified Sybase Associate DBA for ASE 15.0 "Santhanam P" wrote...

gridview row numbers or row count
In my gridview I want to display the number for each row, so this column doesn't have any relation with any datasource. I want it to display the number of row that it represents, so if I delete a row it still has count and represents the right number of rows that I have in my gridview. Any suggestions are welcome also. I think this thread might have what you're looking for: http://forums.asp.net/p/992655/1292440.aspxMike Banavige~~~~~~~~~~~~Need a site code sample in a different language? Try converting it with: http://converter.telerik.com/...

I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row.
 Hi, Let say I've 4 rows in a table. I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row. Thanks in advance Asghar Ali Mohammed http://www.aliwebdev.com(Web Designer and Developer)Do not forget to "MARK AS ANSWER" on the post that helped you. If you can use Linq, you can use Skip. http://msdn.microsoft.com/en-us/vcsharp/aa336757.aspxThanks, EdMicrosoft MVP - ASP/ASP.NET Hii dear..U can fire the Query select Top(3) from Table1 order by id desc.. "A conclusion is whe...

How can I count number of rows in each datawindow in composit datawindow then access and set item in each row in each datawindow!
Hi friends, I created a composite datawindow includes three datawindow. How can I count the number of rows in each datawindow? (Rowcount() function can not works). Also, I need access in each row then reset the value that I want in each row in each datawindow. I use the property to access to get data in each row in each datawindow, but the right value only happens in the first row of each datawindow. It seems I used the loop access the value it doesn't work. Indeed, It has the all data for me but just right for the first row in each data window. Could you have any ideas, please!...

How can I count number of rows in each datawindow in composit datawindow then access and set item in each row in each datawindow!
This is a multi-part message in MIME format. ------=_NextPart_000_004F_01C308EA.58A195E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi friends, I created a composite datawindow includes three datawindow. How can I count the number of rows in each datawindow? (Rowcount() function can = not works). Also, I need access in each row then reset the value that I want = in each row in each datawindow. I use the property to access to get data in each row in each datawindow, but the right value only happens in the = fi...

How can I count number of rows in each datawindow in composit datawindow then access and set item in each row in each datawindow! #2
This is a multi-part message in MIME format. ------=_NextPart_000_0043_01C308EA.0E4861E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi friends, I created a composite datawindow includes three datawindow. How can I count the number of rows in each datawindow? (Rowcount() function can = not works). Also, I need access in each row then reset the value that I want = in each row in each datawindow. I use the property to access to get data in each row in each datawindow, but the right value only happens in the = fi...

Web resources about - Returning n rows, setting start row, total number of rows - asp.net.object-datasource

Returning - Wikipedia, the free encyclopedia
In retail , returning is the process of a customer taking previously purchased merchandise back to the retailer, and in turn, receiving a cash ...

Mark Zuckerberg Returning To TechCrunch Disrupt
Facebook Co-Founder and CEO Mark Zuckerberg took the stage at last year’s TechCrunch Disrupt conference in San Francisco, where he discussed ...

How-to: Improve the Experience for Returning Users
... – your inbox, your favorite news sources, or your friends’ activity. If you’ve integrated Facebook Login on your website, you can give returning ...

Search Twitter - emilia-clarke-not-returning
Sign in Sign up Search Refresh K. @ kikeurbina 1h Muy malas noticias: 'GAME OF THRONES' STAR EMILIA CLARKE (DAENERYS) NOT RETURNING FOR SEASON ...

Is the Returning CEO a Trend?
... well be wondering if it is a new trend for boards to reach back into their alumni ranks to bring back a CEO when a company is in Is the Returning ...

Returning fire - Flickr - Photo Sharing!
A U.S. Army soldier with the 101st Airborne Division returns fire with a M249 light machine gun during combat operations in the valley of Barawala ...

Marines Celebrate Returning Home With a Huge Pillow Fight Aboard the Airplane - YouTube
Visit http://WelcomeHomeBlog.com for more surprise military homecomings! In this clip, a group of marines celebrate their safe return home from ...

WWE’s Daniel Bryan on WWE 2K16, facing Brock Lesnar at WrestleMania, returning to the ring and life on ...
The four-time WWE world champion discusses whether it is right for Stone Cold Steve Austin to be the cover star of WWE 2K16, as well as who he ...

Damn Fine News of the Day: Kyle MacLachlan is Returning to ‘Twin Peaks’
In case you missed it, David Lynch announced on Twitter Monday that Kyle MacLachlan will reprise his role as Special Agent Dale Cooper in the ...

Injury not enough to keep official caught in crossfire from returning to action
The NRL match official caught in the crossfire of angry Bulldogs fans says he has no intention of stepping down from his role on the sidelines. ...

Resources last updated: 12/14/2015 8:08:04 AM