Getting data from database text column in text field question

So I am now at the point where I need to get the textfield.text value filled in with the database column values. The connections between the database table and the listbox works as wanted, all the data is being filled in. Now I need to get the next columns from the database being read into the textfields of the same record, but all the records in the Listbox are getting the value that comes from the last database record. I hope I am making myself clear. According to the Message box info, every record is getting the correct Character_Age column, the problem is now to get them with every record in the Listbox.

This is the code ( with comments for myself, still learning…) and a screenshot from the listbox:

'Place the database data (which at this point is placed in the rs variable) in the Listbox:
'But first let's see if the rs variable indeed has some data to display:
if rs <> Nil then // If rs is not empty...
  'If there is data, then then each database Column and its data is placed in the appropriate Listbox TextColumn.
  
  'There is more than one record in this RowSet, and a do-until loop (For Each ... In) is used to start from the first RowSet and stop at the last RowSet:
  for Each row as DatabaseRow in rs //with every row read from the database table...
    //do this:
    ListBoxCharacters.AddRow //add a row in the Listbox
    ListBoxCharacters.CellValueAt(ListBoxCharacters.LastAddedRowIndex,0) = rs.Column("ID").StringValue //The IntegerValue ‘ID’ is placed in the cell (0) of this new row.
    ListBoxCharacters.CellValueAt(ListBoxCharacters.LastAddedRowIndex,1) = rs.Column("Character_Name").StringValue // The string value "Name" is placed in the cell (1) of this new row.
    txtCharacter_Age.Text  = rs.Column("Character_Age").StringValue // The text value "Character_Age" is placed in the field txtCharacter_Age.text property
    'MessageBox(rs.Column("Character_Name").StringValue + " is " + txtCharacter_Age.Text + " years old.")
    
  next
  rs.Close
end If

The rows of your listbox should only contain a couple of values, such as the name shown in the row, and (e.g. in the rowtag) an id for the corresponding row in the database. Only when the user selects a given row should you populate the other fields of the window, using the id from the rowtag to fetch the correct row.

So when your app opens and you populate the listbox, you are doing such as:

rs = db.SelectSQL ("select id, character_name from sometable")

Later when the user selects a row in the listbox, you will do something like:

id = mylistbox.rowtag(mylistbox.selectedIndex)
rs = db.SelectSQL ("select field1, field2, etc from sometable where id=?", id)

and then use that one row to fill in all the other fields.

There are only some simple steps to fill a textfield from a rowset:
first check if the resulting rowset is not empty, then put the column data into the fields.

if rs <> Nil Then
TextField1.Text = rs.Column("<name of database field 1>").StringValue
TextField2.Text = rs.Column("<name of database field 2>").StringValue
End If
rs.Close

Its a good idea to put that into a try/catch, to avoid problems with empty data.

its better to split tasks in separate methods and maybe useing a class.
search
populate list
row click
load data by id
clear ui
db row to ui
ui to db row
save data

you can also shorten some long names as
var lb As DesktopListBox = ListBoxCharacters
or PopulateList(ListBoxCharacters) with definition PopulateList(lb As DesktopListBox)

without error the SelectSQL will return a RowSet object.
no rows means .AfterLastRow= True or .RowCount =0 (if it is supported for this db)

i would also use the else part in case a id is not found there is something wrong or if you get more rows as imagined.
if rs.RowCount =1 then
else
endif

Thanks everybody for the help. I was however not able to get the row tag function to work, I spend the whole afternoon yesterday on this. I feel though it’s a very powerful option to use, so I will study this more closely. For now I came up with another way that works great as far as I can see. I loaded all the table columns in the Listbox and set the columns width values to 0%. After that I used their values to populate the text fields. Maybe this isn’t very official, but it seems to work.

I still have to figure out how to do the same with a popupmenu object. Using the same way as I describe above, I am sending the DB column values first to the Listbox, now I need to transfer this from the Listbox Column to the Popup Menu object so it shows the correct value.

Here is what I have at this moment:
popAgeRange.Index = ListBoxCharacters.CellValueAt(ListBoxCharacters.SelectedRowIndex,5)

You use DatabaseRow and read from RowSet.
Instead of a for-each loop and a DatabaseRow, I mostly use a while loop to iterate over the records.

while not rs.AfterLastRow
    ListBoxCharacters.AddRow
    ListBoxCharacters.CellValueAt(ListBoxCharacters.LastAddedRowIndex,0) = rs.Column("ID").StringValue 
    // and so on
    
    rs.MoveToNextRow
wend

What does this mean? Why were you not asking about that during the time you were trying to get it to work?

It’s also a dopey way of doing it.

That is a bad idea, as you poll unneccessary data from the database. In a local environment that may be fast enough, but the first user working in a (slow) network will have to have a lot of patience…

To populate your listbox wirh name in Cell and ID in RowTag:

Var rs As RowSet
Var LB As DesktopListBox // use ListBox, if using API 1
LB = ListBoxCharacters // the name of your listbox
LB.RemoveAllRows // clear the listbox before populating with data

Try
	rs = App.DB.SelectSQL("SELECT ID, Character_Name FROM characters")

Catch e As DatabaseException
  MessageBox("Error loading data: " + e.Message)
  Return
End Try

If rs <> Nil Then

// hide ListBox for faster loading
LB.visible = False

For i As Integer = 1 To rs.RowCount
  LB.AddRow(rs.Column("Character_Name").StringValue )
  LB.RowTagAt(LB.LastAddedRowIndex) = rs.Column("ID").StringValue

rs.MoveToNextRow
Next

// show ListBox again
LB.visible = True

// close Rowset
rs.Close

End If

To populate your popupmenu (and all the other fields), please make a new query to the database, when the user clicks on a row in the listbox:

Var rs As RowSet
Try
  rs = App.DB.SelectSQL("SELECT * FROM characters WHERE ID = ?", Me.RowTagAt(Me.SelectedRowIndex).StringValue)
  
Catch e As DatabaseException
  MessageBox("Error loading data: " + e.Message)
  Return
End Try


If rs <> Nil Then
  
 TextField1.Text = rs.Column("Character_Name").StringValue
  popup1.SelectRowWithValue(rs.Column("age").StringValue)
  
End If

// close RowSet
rs.Close

Yes. Basically the listbox only needs one column.

Actually I read about doing it this way somewhere, that is why I tried it when I could not get the Rowtag way to work. But it does feel a little bit messy, I agree. I deleted that code and will redo it to right way. Thanks! :slight_smile:

Sorry to keep on asking about this, but I still cannot get the Rowtags to work. When selecting a row in the ListBoxCharacters, I get an OutOfBoundsException error, which I assume means Xojo is looking for data that doesn’t exist?

//To populate popupmenu and all the other fields:
Var rs As RowSet
Try
  rs = DB.SelectSQL("SELECT * FROM characters WHERE ID = ?", ListBoxCharacters.RowTagAt(ListBoxCharacters.SelectedRowIndex).StringValue)
  
Catch e As DatabaseException
  MessageBox("Error loading data: " + e.Message)
  Return
  
End Try

If rs <> Nil Then
  
  txtCharacterInfo.Text = rs.Column("Character_Information").StringValue
  popArch.SelectRowWithValue(rs.Column("Character_Type").StringValue)
  
End If

// close RowSet
rs.Close

End Sub

It means the event/method/whatever where you have this code is being called when you don’t expect it. In particular, it’s being called when you don’t have a row selected. Wrap then entire thing in

if ListBoxCharacters.SelectedRowIndex >= 0 then
   // all your code
end if

Thanks, that worked!