How To get a reference to the current Row?

Databases
1

the documentation says:

MoveToNextRow

Moves the current row to the next row in the RowSet.

Same goes for the other three MoveTo.

I need a DatabaseRow to pass to my fill the UI Method. How do I get a reference to the current Row ?

2

Can your UI method access the RowSet? If so, the column values in the RowSet are the values from the current Row.

3

The Update ui is called on Opening as this:



For Each row As DatabaseRow In rowsFound
  // Fill the window
  Update_UI(row)
Next

following documentation example(s). I have WHERE Unique ID = value in the SelectSQL line, there is only one Row.

4

So you are passing it a reference to the current row already.

5

Are you kidding ? If so, why the code refuse it ?

Where do you see a MoveTo in the For Each block I shared ?

What I want to do is to pass a Row (the current one) to a Method who accept a Row.
And I do not know how to pass the current row.

I even tried to pass rowsFound to the Update UI method, changing bits here and there and this does not works.

7

The current row is what your rs points to. If you have:

Var rs as RowSet

and you have loaded rs by doing a SELECT, then rs is what you pass to your method. Each time you do rs.MoveToNextRow, rs is updated and points to next/previous row etc. Then inside your method you do such as

id = rs.Column("id").IntegerValue

It has always worked like this (OK, in API1 it was Field instead of Column, but that’s the only change).

8

Yes, thank you.

What do you do when you ask a RecordSet with a WHERE clause ?

Or

How do you set the current row to a unique ID (so you can use MveTo from there).

I use the code below from the documentation:



' db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Instead of the ListBox line, I call a Method: “Update_UI(Row)”

But this goes only for one Row in the db.

Now I want to (Code from the documentation too):



  If rs Is Nil Then Return

    ' set up listbox state for population
    dataList.RemoveAllRows

    ' Add the DB columns as the headers for the ListBox
    dataList.ColumnCount = rs.ColumnCount
    dataList.ColumnAttributesAt(-1).WidthExpression = "100"
    For i As Integer = 0 To rs.LastColumnIndex
      dataList.HeaderAt(i) = rs.ColumnAt(i).Name
    Next

    ' Add the data from the table
    While Not rs.AfterLastRow
      dataList.AddRow("")

      For i As Integer = 0 To rs.LastColumnIndex
        dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
      Next

      rs.MoveToNextRow
    Wend
    rs.Close
End Sub

To be adapted to the situation; it needs a RecordSet.

What I want to do ?

Navigation starting from the current Row of the first code (the one I ask a Row by its Unique ID).

Is it clear now ?

9

You can think of a RowSet as an array of database rows (or records). CurrentRow initially points to the first record in the array. MoveToNextRow moves the pointer to the next row in the array. MoveToPreviousRow would move it back a row. It doesn’t matter what your query is. One with ID=1 just returns a single row. Using MoveToNextRow does nothing.

All the column based methods return data from the currentRow, never any other.

For each is an iterator loop which initially moves the currentRow to 1. The body of the loop is then executed, the record is then MovedToNextRecord and the body is executed again. This continues until the last row is reached. That’s basically the point of an Iterator. You don’t need a MoveToNextRow as it is done for you within the iterator.

10

Nothing at all. The WHERE just changes the number of result rows.

Personally I never use DatabaseRow so can’t judge this.

As far as I know, it is not possible to MoveTo a row based on the content of a column (such as id) - but I could be wrong. I store the id as part of the RowTag as each row is added to the ListBox. Then I have methods to find the row based on id (this is not necessarily the most efficient way but so far it seems fast enough).

11

You can’t jump to a certain row by value of a column. You can’t even do it by number within the set, for example you can’t go to the 10th record.

The most you could do it loop through until a value met your criteria. However, I wouldn’t recommend that, it will be slow.

12

In your code row is the current row. As long as your Update_UI method is

Update_UI(row as DatabaseRow)

The code should work fine. You can then use all the DatabaseRow methods to retrieve the values from the database and load your UI controls.

I think you’re making this more complicated than it needs to be. If you had this working in API1, there is very little you need to do to make it API2. The code is practically identical.

13

Reading closer, you should drop the for each and just pass the RowSet itself to Update_UI. It is already loaded with the “current row” values.