[Gambas-user] Show records on a WebTable

[Tobias Boege]

On Fri, 17 Mar 2017, herberth guzman wrote:
> Regards, I need help, something I am doing wrong.
> 
> I need to display the records of a Table from a DB in a WebTable
> The following code does not work for me in a WebForm and WebTable
> 
> 
> Public Sub WebTable1_Data(Row As Integer, Column As Integer, Data As
> WebTableData)
> 
>   Dim i As Integer
>   cx = M_DB.Connect()
>   rs = cx.Exec("SELECT * FROM tbcategory")
> 
>   For i = 0 To rs.count - 1
>     Data.Text = rs!Name
>     rs.MoveNext
>   Next
> 
> End
> 
> But in a WebComboBox1 if it works the way I need it
> 
> 
> Public Sub WebButton8_Click()
> 
>   Dim i As Integer
>   cx = M_DB.Connect()
>   rs = cx.Exec("SELECT * FROM tbcategory")
> 
>   For i = 0 To rs.count - 1
>     WebComboBox1.Add(rs!Name)
>     rs.MoveNext
>   Next
> 
> End
> 

The WebTable works differently from a WebComboBox. The difference is much
like gb.gui.base's GridView vs. ComboBox -- but not quite. I think comparing
gb.web.form's controls with the graphical components' helps to understand
and memorise the "new" gb.web.form interface, so I'll talk first about
GridView and ComboBox in the graphical components. If you just want an
answer to your question, skip to the TL;DR.

The ComboBox stores all its elements in its memory, thus you can use
ComboBox.Add() to add /all/ its items beforehand and call it a day.
The GridView allows this "store all" mode of operation, too, by using
the array accessors GridView[Row, Column] to fill it.

But, GridView also supports an "on-the-fly" mode, where it stores only the
data that is currently displayed (I don't know if that is technically
correct (it may not store anything but immediately forward your input to
the display or something) but you can think about it this way). When the
set of rows/columns to be displayed changes (e.g. the user scrolls in the
GridView), it raises its Data event for each cell which is now to be
displayed. The Data event handler receives Row and Column parameters to
tell you which cell content it needs and you provide that data in the
GridView.Data property.

This on-the-fly mode takes a little to get accustomed to and may appear
more difficult in the beginning. It has an advantage over the "store all"
mode though, for *huge* tables, because the "on-the-fly" mode always stores
a constant, and very tiny, amount of data. Especially if you want to display
big databases, you want to avoid having /all/ the data in the database *and*
your Gambas process (which might not even be possible, as the size of the DB
might very well exceed your RAM).

Now, this applies in almost the same way in gb.web.form, but the WebTable
*only* supports the "on-the-fly" mode -- it doesn't have the array accessors
of GridView. And instead of a WebTable.Data property to store the result of
the Data event, you get a Data As WebTableData variable passed to the
WebTable_Data event.

              |  store all  |  on-the-fly
  ------------+-------------+--------------
    ComboBox  |      y      |      n
    GridView  |      y      |      y (GridView.Data property)
    WebTable  |      n      |      y (Data event handler argument)

TL;DR: So, to finally answer your question. What you did wrong was

  For i = 0 To rs.count - 1
    Data.Text = rs!Name
    rs.MoveNext
  Next

Here you iterate through all your result records and fill the Data variable
with everything you encounter. You are supposed to only search the (Row, Column)
cell in your result set and put this single data value into the Data object.
I didn't test it, but the following is approximately how it goes:

  Public Sub WebTable1_Data(Row As Integer, Column As Integer, Data As WebTableData)
    Dim i As Integer
    Dim f As ResultField

    cx = M_DB.Connect()
    rs = cx.Exec("SELECT * FROM tbcategory")

    ' Go to the Row-th record in your result
    For i = 0 To Row - 1
      rs.MoveNext
    Next

    ' I'll assume that the Column-th column is just the Column-th one
    ' in rs.Fields, i.e. you have no special ordering on the columns
    i = 0
    For Each f In rs.Fields
      If i = Column Then Break
      Inc i
    Next

    Data.Text = rs[f.Name]
  End

You will be better off not doing the "M_DB.Connect()" and "cx.Exec()" bits
in every invocation of the Data event. Do it once at the beginning.
How did you set WebTable1.Count without a Result in the first place?
If you did that, a further improvement would be to convert the rs.Fields
collection into an array beforehand, too, so that you can just index this
array with the Column parameter you receive in the Data event to avoid the
For Each loop I did above in order to find the Column-th field name.

Regards,
Tobi

-- 
"There's an old saying: Don't change anything... ever!" -- Mr. Monk