First 10 records?

just asking how i can select the first 10 records in VB?? and the 2nd 10, 3rd 10 and so on...

"Select * from Table where ???? 1st 10 ???"

i have no idea?? help me pls?

To select the Top 10 you could do this...

VB Code:

Select TOP 10 * from Table where

how about to the next 10 records??

Quote:

---

Originally Posted by Bluei2

how about to the next 10 records??

---

Maybe LIMIT 10

The ff. works, perhaps you could use such method also...

VB Code:

Public Sub X()
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
    With rs1
        .Open "Select TOP 3 ReFNum FROM SubConItinerary WHERE RefNum NOT IN (SELECT TOP 3 RefNum FROM SubConItinerary)", adoConn, adOpenStatic, adLockReadOnly
        Do While Not .EOF
            MsgBox .Fields("RefNum")
            .MoveNext
        Loop
        .Close
    End With
    Set rs1 = Nothing
End Sub

I've made a sample which seem to do what you want... It does display the next records but not the first top 3...

VB Code:

Option Explicit
Private Const intLimit  As Integer = 3
 
Private Sub Sample()
    Dim a As Integer
    Dim adoRecordset As ADODB.Recordset
    For a = 1 To 3
        Set adoRecordset = NextRecordset(a)
        With adoRecordset
            Do While Not .EOF
                MsgBox .Fields("RefNum")
                .MoveNext
            Loop
            .Close
        End With
    Next
    Set adoRecordset = Nothing
End Sub
 
'Sample of getting the next Top 3
Public Function NextRecordset(ByVal i As Integer) As ADODB.Recordset
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
    rs1.Open "Select TOP 3 RefNum FROM SubConItinerary WHERE RefNum NOT IN (SELECT TOP " & (intLimit * i) & " RefNum FROM SubConItinerary)", adoConn, adOpenStatic, adLockReadOnly
    Set NextRecordset = rs1
End Function
 
Private Sub Command1_Click()
    Sample
End Sub

I tidied it up...

VB Code:

Option Explicit
Private Const intLimit  As Integer = 3
 
Private Sub Sample()
    Dim a As Integer
    Dim adoRecordset As ADODB.Recordset
    For a = 1 To 3
        NextTop a, adoRecordset
        With adoRecordset
            Do While Not .EOF
                MsgBox .Fields("RefNum")
                .MoveNext
            Loop
            .Close
        End With
    Next
    Set adoRecordset = Nothing
End Sub
 
'Sample of getting the next Top 3
Public Sub NextTop(ByVal i As Integer, ByRef adoRecordset As ADODB.Recordset)
    Set adoRecordset = New ADODB.Recordset
    adoRecordset.Open "Select TOP 3 RefNum FROM SubConItinerary WHERE RefNum NOT IN (SELECT TOP " & (intLimit * i) & " RefNum FROM SubConItinerary)", adoConn, adOpenStatic, adLockReadOnly
End Sub
 
Private Sub Command1_Click()
    Sample
End Sub

or you can use the PAGE stuff of ADO... its easier...

i have this in one of my projects... it displays 20 records per page (in a grid)

VB Code:

Const RecordsPerPage = 20           
 
Sub RefreshGrid(nPage As Integer)
On Error GoTo HELL
    Dim RecsDisplayed As Integer, Qty As Integer
    Dim rsStocks As New ADODB.Recordset
 
    'RS initz here...
   
    'clear grid
    Grid.Rows = 1
    
    With rsStocks 
        If .RecordCount > 0 Then
                  
            .AbsolutePage = nPage          'sets where (in what page) it will extract records      
            .PageSize = RecordsPerPage   'sets how many records to be returned       
        End If
 
        Do While Not .EOF And RecsDisplayed < RecordsPerPage
            Grid.AddItem !StkName 
 
            RecsDisplayed = RecsDisplayed + 1
            .MoveNext
        Loop
   End With
 
    lblPage = "Page " & nPage & " of " & rsStocks.PageCount
 
    Exit Sub
HELL:
    MsgBox Err.Description, vbCritical
End Sub
 
'to call it
RefreshGrid 3

Quote:

---

Originally Posted by eimroda

or you can use the PAGE stuff of ADO... its easier...

i have this in one of my projects... it displays 20 records per page (in a grid)

VB Code:

Const RecordsPerPage = 20           
 
Sub RefreshGrid(nPage As Integer)
On Error GoTo HELL
    Dim RecsDisplayed As Integer, Qty As Integer
    Dim rsStocks As New ADODB.Recordset
 
    'RS initz here...
   
    'clear grid
    Grid.Rows = 1
    
    With rsStocks 
        If .RecordCount > 0 Then
                  
            .AbsolutePage = nPage          'sets where (in what page) it will extract records      
            .PageSize = RecordsPerPage   'sets how many records to be returned       
        End If
 
        Do While Not .EOF And RecsDisplayed < RecordsPerPage
            Grid.AddItem !StkName 
 
            RecsDisplayed = RecsDisplayed + 1
            .MoveNext
        Loop
   End With
 
    lblPage = "Page " & nPage & " of " & rsStocks.PageCount
 
    Exit Sub
HELL:
    MsgBox Err.Description, vbCritical
End Sub
 
'to call it
RefreshGrid 3

---

Nice one there eimroda, didn't know about that method... :wave:

its like those we see in the web like <previous> 1 2 3 4 <next>, in ASP that PAGE thingy is also used...

BTW, kabsat napankan idiay www.ilocano.org? adu't agur-uray kaniam idiay a pada a saluyot :)