SELECTing only the first n records from a database

Discussion in 'ASP .Net' started by Nathan Sokalski, May 10, 2007.

  1. 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.
    --
    Nathan Sokalski

    http://www.nathansokalski.com/
     
    Nathan Sokalski, May 10, 2007
    #1
    1. Advertising

  2. Nathan Sokalski

    sloan Guest

    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




    "Nathan Sokalski" <> wrote in message
    news:...
    > 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.
    > --
    > Nathan Sokalski
    >
    > http://www.nathansokalski.com/
    >
    >
     
    sloan, May 10, 2007
    #2
    1. Advertising

  3. Nathan Sokalski

    Earl Guest

    SELECT Top n FROM ...

    SELECT Top n FROM ... ORDER BY DESC

    "Nathan Sokalski" <> wrote in message
    news:...
    >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.
    > --
    > Nathan Sokalski
    >
    > http://www.nathansokalski.com/
    >
     
    Earl, May 10, 2007
    #3
  4. 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.

    "Nathan Sokalski" wrote:

    > 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.
    > --
    > Nathan Sokalski
    >
    > http://www.nathansokalski.com/
    >
    >
    >
     
    =?Utf-8?B?U2l2YSBN?=, May 10, 2007
    #4
  5. Nathan Sokalski

    Miha Markic Guest

    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.

    --
    Miha Markic [MVP C#, INETA Country Leader for Slovenia]
    RightHand .NET consulting & development www.rthand.com
    Blog: http://cs.rthand.com/blogs/blog_with_righthand/

    "Nathan Sokalski" <> wrote in message
    news:...
    >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.
    > --
    > Nathan Sokalski
    >
    > http://www.nathansokalski.com/
    >
     
    Miha Markic, May 10, 2007
    #5
  6. Nathan Sokalski

    Paul Clement Guest

    On Wed, 9 May 2007 21:00:12 -0400, "Nathan Sokalski" <> wrote:

    ¤ 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)
     
    Paul Clement, May 10, 2007
    #6
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. VB Programmer
    Replies:
    2
    Views:
    439
    Alan Lambert
    Nov 4, 2004
  2. =?Utf-8?B?cm9kY2hhcg==?=

    Selecting Dataset records

    =?Utf-8?B?cm9kY2hhcg==?=, Mar 22, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    3,153
    =?Utf-8?B?cm9kY2hhcg==?=
    Mar 23, 2005
  3. AlecL
    Replies:
    1
    Views:
    361
    Peter Bradley
    Mar 8, 2007
  4. David C
    Replies:
    0
    Views:
    334
    David C
    Aug 12, 2008
  5. Mark
    Replies:
    0
    Views:
    129
Loading...

Share This Page