SELECTing only the first n records from a database

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.
 
S

sloan

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
 
G

Guest

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.
 
M

Miha Markic

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.
 
P

Paul Clement

¤ 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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top