Results 1 to 18 of 18

Thread: [RESOLVED] MoveNext & MovePrevious in SQLite

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Resolved [RESOLVED] MoveNext & MovePrevious in SQLite

    Hello everyone
    This is how I was moving to the next and previous record with ADO and access database.
    Code:
    RS.MoveNext
    
    If RS.EOF Then
        RS.MoveLast
         End If
     Text1.text = RS.Fields("Nom").Value 
     Text2.text = RS.Fields("Prenom").Value
    Code:
    RS.MovePrevious
    If RS.BOF Then
        RS.MoveFirst
      End If
    Text1.text = RS.Fields("Nom").Value 
     Text2.text = RS.Fields("Prenom").Value
    But this doesn't seem to work in SQLite
    Thank you
    Last edited by samer22; Apr 30th, 2018 at 12:46 PM.

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: MoveNext & MovePrevious in SQLite

    No...
    It works completely different.

    Did you study the SQLite samples?

    From the SimpleSQL demo:
    Code:
      Dim Rs As cRecordset, SQL As String
    
      'the usual string, to select "everything" (all Columns
      'and all records) - resulting in the complete table-
      'content-representation in our "Rs-ResultMatrix"
      SQL = "Select * From T"
    
      Set Rs = Cnn.OpenRecordset(SQL) 'perform the query
    
      'after returning from a query, the Recordset-implementation
      'of my wrapper always contains the complete data as a
      'copy - there is no dynamic cursor which points to the
      'original table-records - everything the select-query
      'describes is always "completely contained" inside of the Rs
    
      '...so we can always rely on that - and therefore an
      'SQLite-Recordset can be treated also as a two-dimensional
      'array, accessible with Zerobased Row- and Column-indexes over
      'a special Property called: Rs.ValueMatrix(i, j)... This
      'property is not available on ADO or DAO, but very useful,
      'since it does not shift the Rs-internal RecordCursor - but
      'read further...
    
      'Ok, first ...even if a Rs does not contain any Data-Records
      '(testable with Rs.RecordCount = 0) - it contains at least
      'the Field- or Column-Infos, we make use of that fact - and
      'list one of the Field-Infos here (the FieldName)
      Dim i As Long, j As Long
      For j = 0 To Rs.Fields.Count - 1
        Debug.Print Rs.Fields(j).Name,
      Next j
      Debug.Print vbCrLf; "--------------------------------------"
    
      'now the content, dumped in an "array-like" way over Rs.ValueMatrix
      For i = 0 To Rs.RecordCount - 1
        For j = 0 To Rs.Fields.Count - 1
          Debug.Print Rs.ValueMatrix(i, j),
        Next j
        Debug.Print
      Next i

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: MoveNext & MovePrevious in SQLite

    What i don't understand is why if you reach RS.EOF you MoveLast (and BOF MoveFirst)
    i would have expected at EOF to MoveFirst and when BOF to MoveLast (circular Navigation)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: MoveNext & MovePrevious in SQLite

    Arnoutdv thanks
    but I couldn't find a way to make your code work
    could you please explain further how to use the code above to move Next Or Previous?

  5. #5
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    Re: MoveNext & MovePrevious in SQLite

    All of the methods in the OP (as well as MoveNext, MovePrevious, etc.) work with Olaf's Sqlite wrapper. When you say it doesn't work, what exactly do you mean?
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  6. #6
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: MoveNext & MovePrevious in SQLite

    SQLite cannot handle true cursor movement. It can internally only step forward until end.
    Olaf's Sqlite wrapper just caches all the data and thus can provide "cursor" movements.

  7. #7
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by Krool View Post
    SQLite cannot handle true cursor movement. It can internally only step forward until end.
    Olaf's Sqlite wrapper just caches all the data and thus can provide "cursor" movements.
    Interesting. But, regardless, all of the ADO methods are replicated, even if (behaviourally), they are different. So, I'm not sure that the OP even has the problem he describes. For all we know, there is some not-yet-seen On Error Resume Next line of code that is masking the (apparent?) perception that the methods are simply 'not working'.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  8. #8
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: MoveNext & MovePrevious in SQLite

    Maybe he's using ADO (not RC5) with SQLite?

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by ColinE66 View Post
    All of the methods in the OP (as well as MoveNext, MovePrevious, etc.) work with Olaf's Sqlite wrapper. When you say it doesn't work, what exactly do you mean?
    well I was running a query in Form Load event

    Code:
    SQL = "Select * From MyTable"
      Set Rs = Cnn.OpenRecordset(SQL)
    Then under MoveNext button I run the following code:
    Code:
    RS.MoveNext
    
    If RS.EOF Then
        RS.MoveLast
         End If
     Text1.text = RS.Fields("Nom").Value 
     Text2.text = RS.Fields("Prenom").Value
    It didn't work.
    I moved the first query to Form Activate Event and now it's working.

    For the code proposed by Arnoutdv,it is working forward but I didn't find a way backward.

  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: MoveNext & MovePrevious in SQLite

    Just stick with the methods Olav provided instead trying to mimic ADO.
    The .ValueMatrix array holds all data

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by Arnoutdv View Post
    Just stick with the methods Olav provided instead trying to mimic ADO.
    The .ValueMatrix array holds all data
    Where can I find Olaf's method please?

  12. #12
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: MoveNext & MovePrevious in SQLite

    Post #2...

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by Arnoutdv View Post
    Post #2...
    could you please show me how to use that code to move forward and backward?

  14. #14
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    Re: MoveNext & MovePrevious in SQLite

    Like I said before, support is provided for .MoveNext and .MovePrevious. Regardless of the 'true cursor movement' limitations that Krool mentioned, Olaf has implemented something that mimics this behaviour, presumably for backward compatibility and smooth migration paths from ADO. Something else is at play if it is not working for you.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  15. #15
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by samer22 View Post
    could you please show me how to use that code to move forward and backward?
    Why move forward and backwards?
    You have access to a 2 dimensional array, just grab any element you need at any time.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by Arnoutdv View Post
    Why move forward and backwards?
    This is what I was doing with ADO to loop through my records.
    Opening recordset
    Code:
    Call connect
    Dim sSQL As String
    sSQL = "SELECT * FROM Person_Tbl"
    RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
    Text1.Text = RS.Fields("Nom").Value
     Text2.Text = RS.Fields("Prenom").Value
    Move Forwards
    Code:
    On Error Resume Next
    RS.MoveNext
    If RS.EOF Then
        RS.MoveLast
         End If
     Text1.Text = RS.Fields("Nom").Value
     Text2.Text = RS.Fields("Prenom").Value
    Move backwards
    Code:
    On Error Resume Next
    RS.MovePrevious
    If RS.BOF Then
        RS.MoveFirst
      End If
    Text1.Text = RS.Fields("Nom").Value
     Text2.Text = RS.Fields("Prenom").Value
    Now I want to know to implement Olaf's Method to do the same thing.
    Thanks

  17. #17
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: MoveNext & MovePrevious in SQLite

    Quote Originally Posted by Arnoutdv View Post
    Why move forward and backwards?
    You have access to a 2 dimensional array, just grab any element you need at any time.
    The Rs.ValueMatrix is useful, when you do not want to change the internal Position of the "current Record within the Rs"
    (e.g. when filling DataGrid-Cells with the Rs-Values, without the need for "buffering/restoring the current Rs-Pointer").

    That the Rs is also capable of internally managing such a "current Record" (just like an ADO-Rs),
    is a useful feature of course (e.g. when you want navigation with your own version of an "ADODC-like" Control).

    The RC5-cRecordset also has an Event-Interface you can use for "full databinding"-scenarios,
    but also for "partially bound" scenarios like the one shown below (which only uses the Rs_Move-Event):

    Open a new Form-Project (with a reference to vbRichClient5) and put 4 Controls on it:
    Command1, Command2, Text1, Text2

    Code:
    Option Explicit
    
    Private Cnn As cConnection, WithEvents Rs As cRecordset, i As Long
    
    Private Sub Form_Load()
      Command1.Caption = "<": Command2.Caption = ">" 'Prev- and Next-Button-Captions
      
      'create an InMem-DB, along with a little Test-table
      Set Cnn = New_c.Connection(, DBCreateInMemory)
          Cnn.Execute "Create Table T(ID Integer Primary Key, FirstName Text, LastName Text)"
     
      With Cnn.OpenRecordset("Select * From T") 'add 5 test-records into table T
        For i = 1 To 5
         .AddNew: !FirstName = "First " & i: !LastName = "Last " & i
        Next
        .UpdateBatch
      End With
      
      Set Rs = Cnn.OpenRecordset("Select * From T")
      SetButtonStates Rs, Command1, Command2
      UpdateControls Rs, Text1, Text2
    End Sub
    
    Private Sub Rs_Move(ByVal NewRowIdxZeroBased As Long)
      SetButtonStates Rs, Command1, Command2
      UpdateControls Rs, Text1, Text2
    End Sub
    
    Private Sub UpdateControls(Rs As cRecordset, txtFirstName, txtLastName)
      If Rs.AbsolutePosition < 1 Then
        txtFirstName.Text = ""
        txtLastName.Text = ""
      Else
        txtFirstName.Text = Rs!FirstName
        txtLastName.Text = Rs!LastName
      End If
    End Sub
    
    Private Sub SetButtonStates(Rs As cRecordset, btnPrev, btnNext)
      Me.Caption = "Rs-Pos: " & Rs.AbsolutePosition 'visualize the current Pos of the Rs
      btnPrev.Enabled = Rs.AbsolutePosition > 1
      btnNext.Enabled = Rs.AbsolutePosition > 0 And Rs.AbsolutePosition < Rs.RecordCount
    End Sub
    
    Private Sub Command1_Click()
      Rs.MovePrevious
    End Sub
    Private Sub Command2_Click()
      Rs.MoveNext
    End Sub
    @Samer22
    The InMemory-feature of SQLite makes it very easy, to post "complete little examples" like the above
    (which fully work and demonstrate a problem).
    I'd appreciate, when you'd try to replicate your "In-App-Problems" within such an isolated little Project first,
    and then post the complete code of your Test-Form (like I just did with the example above).

    It is often really hard to guess from your short "In-App-snippets", what it is you're trying to attempt
    (since we're missing the larger "context" - and writing a small, isolated demo will deliver such a context -
    "re-instating the problem" in a simplified, isolated setting will always help, to understand a problem better -
    potential mistakes you made, will often become more apparent when you try to run a little isolated demo
    instead of "the big thing"...).

    Olaf
    Last edited by Schmidt; May 1st, 2018 at 04:24 PM.

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: MoveNext & MovePrevious in SQLite

    Very Much appreciated your Demo Mr Schmidt
    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width