-
Apr 30th, 2018, 07:37 AM
#1
Thread Starter
Fanatic Member
[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.
-
Apr 30th, 2018, 07:40 AM
#2
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
-
Apr 30th, 2018, 10:20 AM
#3
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
-
Apr 30th, 2018, 12:43 PM
#4
Thread Starter
Fanatic Member
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?
-
Apr 30th, 2018, 12:52 PM
#5
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
-
Apr 30th, 2018, 01:31 PM
#6
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.
-
Apr 30th, 2018, 04:26 PM
#7
Re: MoveNext & MovePrevious in SQLite
Originally Posted by Krool
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
-
Apr 30th, 2018, 04:38 PM
#8
Re: MoveNext & MovePrevious in SQLite
Maybe he's using ADO (not RC5) with SQLite?
-
Apr 30th, 2018, 05:05 PM
#9
Thread Starter
Fanatic Member
Re: MoveNext & MovePrevious in SQLite
Originally Posted by ColinE66
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.
-
May 1st, 2018, 12:25 AM
#10
Re: MoveNext & MovePrevious in SQLite
Just stick with the methods Olav provided instead trying to mimic ADO.
The .ValueMatrix array holds all data
-
May 1st, 2018, 07:53 AM
#11
Thread Starter
Fanatic Member
Re: MoveNext & MovePrevious in SQLite
Originally Posted by Arnoutdv
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?
-
May 1st, 2018, 08:36 AM
#12
Re: MoveNext & MovePrevious in SQLite
-
May 1st, 2018, 11:28 AM
#13
Thread Starter
Fanatic Member
Re: MoveNext & MovePrevious in SQLite
Originally Posted by Arnoutdv
Post #2...
could you please show me how to use that code to move forward and backward?
-
May 1st, 2018, 12:21 PM
#14
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
-
May 1st, 2018, 01:52 PM
#15
Re: MoveNext & MovePrevious in SQLite
Originally Posted by samer22
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.
-
May 1st, 2018, 03:46 PM
#16
Thread Starter
Fanatic Member
Re: MoveNext & MovePrevious in SQLite
Originally Posted by Arnoutdv
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
-
May 1st, 2018, 04:14 PM
#17
Re: MoveNext & MovePrevious in SQLite
Originally Posted by Arnoutdv
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.
-
May 1st, 2018, 05:30 PM
#18
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|