select top and next

  • Thread starter Thread starter Konrad
  • Start date Start date
K

Konrad

Hi

I'am using select top 10 clause to load DataSet
Ho to get next 10 rows (from 11 to 20) to DataSet?

Thanks
Konrad
 
Konrad, are you using Sql Server here? You'd need to tell it where to start
for the next set of rows. So for example if you are selecting rows from a
customer table with a customerId field and the select is ordered by customer
Id then you can do it like this:

select top 10 * from Customers order by customerId

then if the customerId of the last customer you got in this statement was
1509 for example then in the next statement you'd say:

select top 10 * from Customers where customerId > 1509 order by customerId

Of course you should actually put a column list in place of the * above -
using a * to select all columns is bad practise.

You might also want to look at the paging abilities of the datagrid object
in ASP.NET - I don't have enough info to be sure if this is related to your
issue or not though.
 
Thanks I found the answer:

SELECT TOP "+Count.ToString()+" * FROM Investments WHERE (Date NOT IN
(SELECT TOP "+r.ToString()+" date FROM investments ORDER BY date DESC))
ORDER BY Date DESC

it gives Count rows from r

Regards

Konrad
 
Hi Konrad:

I'm glad you found a solution but I thought I'd mention another approach.
You can hit the db repeatedly like you are doing but this may or may not
yeild the resulst you want. Depending on the number of rows you're dealing
with here, you may want to consider pulling over all 20 rows for instance,
and then using a dataView for instance to show the data. You can simply use
the DataView's RowFilter property (or you could use .Find or
DataTable.Select) for instance. You could set it as such
DataView dv = DataSetName.Tables[index].DefaultView;

now just set dv.RowFilter = "ColumnName > 0 AND ColumnName < 11'; the first
time through, then if the user hits the 'next' button or whatever mechanism
triggers the subsequent query, then just reset it to ColumnName < 10. (Or
if you want to page on multiples of 10, do the less than and greater than
thing until you get to the last record and use Greater than, which will be
the remainder of your records" There's a trade off between making round
trips to the db which causes overhead and the overhead associated w/ pulling
over a lot of data, but it doesn't look like you're dealing with a whole lot
of data here, and if you pull it all over at once and reuse it, you won't
have to run a query for data you already have (ie the user hits the next
button by accident and ghen goes back, or simply moves forward and back a
few times just to compare the data). Normally you want to work with
'reasonable' sizes of data but if you are talking about < 100 rows (looks
like 20 here If I understand the problem correctly) then you are well below
the poitn where you'd possibly notice the difference on the select side.
However, if you have a lot of people using the app and moving back and
forward, you could conceivably save yourself a ton of network round trips in
addition to allowing your app to still run in the case the db went down for
some reason (although if the db went down you would probably have some other
more pressing issues)

HTH,


Bill

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
Back
Top