SELECTing only the first n records from a database

  • Thread starter Thread starter Nathan Sokalski
  • Start date Start date
N

Nathan Sokalski

I want to select only the first n records from a database using VB.NET. I
have declared a DataTable and OleDB.OleDbDataAdapter as follows:

Dim linkstable As New DataTable
Dim linksadapter As New OleDb.OleDbDataAdapter("SELECT * FROM worldnews
ORDER BY updated",
System.Configuration.ConfigurationManager.AppSettings("connectionstring"))

I want to use the Fill method, and I thought the following overload looked
like it might work:

Fill(startRecord As Integer,maxRecords As Integer,ParamArray dataTables As
DataTable())

The documentation describes the parameters as follows:

Parameters
startRecord
A DataTable to fill with records and, if necessary, schema.

maxRecords
The maximum number of records to retrieve.

dataTables
One of the CommandBehavior values.



The maxRecords parameter is obvious, and I assumed I would just use the
DataTable I wanted to fill for the dataTables parameter (in my case,
linkstable). However, I was confused by the startRecord parameter because it
is an Integer, but the description says that it is a DataTable. What should
I do to Fill the DataTable with only the first n records? I will also need a
way to Fill the DataTable with all the records other than the first n
records. Any ideas? Thanks.
 
Dim linksadapter As New OleDb.OleDbDataAdapter("SELECT 10 f1, f2, f3 FROM
worldnews
ORDER BY updated",

Select * is kind of a bad habit.

Use the field names. ex: Select EmpID, LastName, FirstName From Emp
 
It appears to be a documentation error. Anyway, to get top N records you can
use SELECT TOP n syntax of SQL Server as others have suggested.
 
Hi Nathan,

What database are you using? As others said, check if the database supports
some sort of TOP (as in sql server) sql statement. That is by far the most
effective way to limit the rows number.
 
¤ I want to select only the first n records from a database using VB.NET. I
¤ have declared a DataTable and OleDB.OleDbDataAdapter as follows:
¤
¤ Dim linkstable As New DataTable
¤ Dim linksadapter As New OleDb.OleDbDataAdapter("SELECT * FROM worldnews
¤ ORDER BY updated",
¤ System.Configuration.ConfigurationManager.AppSettings("connectionstring"))
¤
¤ I want to use the Fill method, and I thought the following overload looked
¤ like it might work:
¤
¤ Fill(startRecord As Integer,maxRecords As Integer,ParamArray dataTables As
¤ DataTable())
¤
¤ The documentation describes the parameters as follows:
¤
¤ Parameters
¤ startRecord
¤ A DataTable to fill with records and, if necessary, schema.
¤
¤ maxRecords
¤ The maximum number of records to retrieve.
¤
¤ dataTables
¤ One of the CommandBehavior values.
¤
¤
¤
¤ The maxRecords parameter is obvious, and I assumed I would just use the
¤ DataTable I wanted to fill for the dataTables parameter (in my case,
¤ linkstable). However, I was confused by the startRecord parameter because it
¤ is an Integer, but the description says that it is a DataTable. What should
¤ I do to Fill the DataTable with only the first n records? I will also need a
¤ way to Fill the DataTable with all the records other than the first n
¤ records. Any ideas? Thanks.

It depends upon the database you are working with. Although the SQL keyword TOP is the most common
method supported by SQL Server, Access, etc. the keyword is not supported by all databases (such as
Oracle).


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi,

i've also seen that and did the following: ignored the tooltip and placed a 0 for retrieving the first n records... and it works! :-)

I guess it realy is an int and the tooltip is just plain wrong.

Best regards
 
Back
Top