Populating Listbox with Recordset

General
1

I have this Code:

Dim rs As RecordSet
rs = app.mDb.SQLSelect(“SELECT XML RFCEmisor FROM Recibidas”)

// Populate the specified listbox with the data in the recordset
// that is provided. This will loop through the records in the
// recordset and add rows to the listbox that contain the data
// from the recordset.

dim i as integer

// Clear the order list
listafacturas.deleteAllRows

// Loop until we reach the end of the recordset
while not rs.eof
listafacturas.addRow // add a new row to the listbox

// Loop through all of the fields in the recordset and add the data to the correct
// column in the listbox
for i = 1 to rs.fieldCount
  // The listbox Cell property is 0-based so we need to subtract 1 from the database field
  // number to get the correct correct column number.  This means field 1 is in column 0 of
  // the listbox.
  listafacturas.cell(listafacturas.lastIndex, i-1 ) = rs.idxField( i ).stringValue
next

rs.moveNext// move to the next record

wend

// If the listbox is set to be sorted by a particular column then we want to
// sort the listbox contents after we populate it, so that they appear in the
// correct order.
if listafacturas.sortedColumn > -1 then// the listbox is sorted by a column
listafacturas.sort// sort the listbox data using the current sort settings
end

But Only gives me the first column, Am I wrong?

Regards

2

your query
rs = app.mDb.SQLSelect(“SELECT XML RFCEmisor FROM Recibidas”)

selects ONE column (XML) and refers to it by another name (RFCEmisor)

see http://sqlite.org/lang_select.html

I suspect you wanted
rs = app.mDb.SQLSelect(“SELECT XML, RFCEmisor FROM Recibidas”)

3

[quote=190090:@Norman Palardy]your query
rs = app.mDb.SQLSelect(“SELECT XML RFCEmisor FROM Recibidas”)

selects ONE column (XML) and refers to it by another name (RFCEmisor)

see http://sqlite.org/lang_select.html

I suspect you wanted
rs = app.mDb.SQLSelect(“SELECT XML, RFCEmisor FROM Recibidas”)[/quote]
Yes ok, You are wright, ok.
I forgot to separate values by a comma in order to get several value from the table.

I’ve done it:

Dim rs As RecordSet
rs = app.mDb.SQLSelect(“SELECT * FROM Recibidas”)

// Populate the specified listbox with the data in the recordset
// that is provided. This will loop through the records in the
// recordset and add rows to the listbox that contain the data
// from the recordset.

dim i as integer

// Clear the order list
listafacturas.deleteAllRows

// Loop until we reach the end of the recordset
while not rs.eof
listafacturas.addRow // add a new row to the listbox

// Loop through all of the fields in the recordset and add the data to the correct
// column in the listbox
for i = 1 to rs.fieldCount
  // The listbox Cell property is 0-based so we need to subtract 1 from the database field
  // number to get the correct correct column number.  This means field 1 is in column 0 of
  // the listbox.
  listafacturas.cell(listafacturas.lastIndex, i-1 ) = rs.idxField( i ).stringValue
next

rs.moveNext// move to the next record

wend

// If the listbox is set to be sorted by a particular column then we want to
// sort the listbox contents after we populate it, so that they appear in the
// correct order.
if listafacturas.sortedColumn > -1 then// the listbox is sorted by a column
listafacturas.sort// sort the listbox data using the current sort settings
end

I know when you say “SELECT * FROM Recibidas” to mySQL you are asking all the values on the table.
Curiously when I set this instruction I only get a list from 1 to 37, the Items existing on the table.

Any Suggestions?

4

I saw what exactly is happening, when I set ColumnCount in the Xojo’s Inspector It shows me more columns.

Now I gonna investigaste how can I do this with code in base of the number of columns in recordset.

5

Yeahhhhhhh! Finally I got the Code for Populate a Listbox with a Recordset, I placed into an Action Button:
Dim sql As String
sql = “SELECT * FROM Recibidas”

Dim rs As RecordSet
rs = app.mDb.SQLSelect(sql)

If app.mDB.Error Then
MsgBox("DB Error: " + app.mDB.ErrorMessage)
Return
End If

// set up listbox state for population
listafacturas.DeleteAllRows
listafacturas.Columncount = rs.Fieldcount

// Add the DB columns as the headers for the ListBox
listafacturas.HasHeading = True
listafacturas.ColumnCount = rs.FieldCount
listafacturas.Column(-1).WidthExpression = “100”
For i As Integer = 0 To rs.FieldCount-1
listafacturas.Heading(i) = rs.IdxField(i+1).Name
Next

If rs <> Nil Then
While Not rs.EOF
listafacturas.AddRow("")

  For i As Integer = 0 To rs.FieldCount-1
    listafacturas.Cell(listafacturas.LastIndex, i) = rs.IdxField(i+1).StringValue
  Next
  
  rs.MoveNext
Wend
rs.Close

End If

6

And Now I was reading on Xojo’s Documentation / Wiki.

And I found this Method to populate a Listbox:

Sub PopulateListBox(dataList As Listbox, rs As RecordSet)
If rs Is Nil Then Return

// set up listbox state for population
dataList.DeleteAllRows
dataList.Columncount = rs.Fieldcount

// Add the DB columns as the headers for the ListBox
dataList.ColumnCount = rs.FieldCount
dataList.Column(-1).WidthExpression = “100”
For i As Integer = 0 To rs.FieldCount-1
dataList.Heading(i) = rs.IdxField(i+1).Name
Next

// Add the data from the table
While Not rs.EOF
dataList.AddRow("")

For i As Integer = 0 To rs.FieldCount-1
  dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i+1).StringValue
Next

rs.MoveNext

Wend
End Sub

Now my doubt is, How Can I Use it?.

I understand this:

I paste the code without the Sub and End Sub.
In the Inspector Panel.
I need to fill this fields:

  • Method Name: PopulateListbox
  • Parameters: datalist as Listbox, rs As Recordset
  • Return Type: ??? (I don’t know what to put here)
  • Scope: Public

I have a slight idea of Use it.

If the method is well made.

In an action button, put this:

populatelistbox(listafacturas,data)

Where:
Listafacturas = listbox to be populated
data = the recordset

Is this true or I omitting something?

7

[quote=190201:@Gerardo García]And Now I was reading on Xojo’s Documentation / Wiki.

And I found this Method to populate a Listbox:

Sub PopulateListBox(dataList As Listbox, rs As RecordSet)
If rs Is Nil Then Return

// set up listbox state for population
dataList.DeleteAllRows
dataList.Columncount = rs.Fieldcount

// Add the DB columns as the headers for the ListBox
dataList.ColumnCount = rs.FieldCount
dataList.Column(-1).WidthExpression = “100”
For i As Integer = 0 To rs.FieldCount-1
dataList.Heading(i) = rs.IdxField(i+1).Name
Next

// Add the data from the table
While Not rs.EOF
dataList.AddRow("")

For i As Integer = 0 To rs.FieldCount-1
  dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i+1).StringValue
Next

rs.MoveNext

Wend
End Sub

Now my doubt is, How Can I Use it?.

I understand this:

I paste the code without the Sub and End Sub.
In the Inspector Panel.
I need to fill this fields:

  • Method Name: PopulateListbox
  • Parameters: datalist as Listbox, rs As Recordset
  • Return Type: ??? (I don’t know what to put here)
  • Scope: Public

I have a slight idea of Use it.

If the method is well made.

In an action button, put this:

populatelistbox(listafacturas,data)

Where:
Listafacturas = listbox to be populated
data = the recordset

Is this true or I omitting something?[/quote]
Yeahhhhh, Ot Works at this mode

8

Sorry, I’m to happy to learn this.

Now I want to know if is possible to make this with a SQL instruction:
Select all columns from a table, except one column.

Is this possible?

9

[quote=190207:@Gerardo García]Now I want to know if is possible to make this with a SQL instruction:
Select all columns from a table, except one column.

Is this possible?[/quote]
You would have to name the columns you want. You could query the db for the column names and create a string of all but the one you want to skip.

10

Ok I Gonna Study it,Thanks