[code] sSQL = “SELECT * FROM Accounts WHERE Type LIKE ‘I%’ ORDER BY Account”
rsIncomeAccts = dbSQL.SQLSelect(sSQL)
iRows = rsIncomeAccts.RecordCount
labelRecordCountIncome.Text = str(iRows)
for i = 0 to iRows - 1
lbIncomeAccounts.addRow str(i)
lbIncomeAccounts.cell( i, 1 ) = rsIncomeAccts.Field( “Account” ).StringValue
lbIncomeAccounts.cell( i, 2 ) = rsIncomeAccts.Field( “SubAcctOf” ).StringValue
lbIncomeAccounts.cell( i, 3 ) = rsIncomeAccts.Field( “Type” ).StringValue
lbIncomeAccounts.cell( i, 4 ) = rsIncomeAccts.Field( “Description” ).StringValue
rsIncomeAccts.MoveNext
Next
rsIncomeAccts.MoveFirst
lbExpenseAccounts.DeleteAllRows
for i = 0 to iRows - 1
lbExpenseAccounts.addRow str(i)
lbExpenseAccounts.cell( i, 1 ) = rsIncomeAccts.Field( “Account” ).StringValue
lbExpenseAccounts.cell( i, 2 ) = rsIncomeAccts.Field( “SubAcctOf” ).StringValue
lbExpenseAccounts.cell( i, 3 ) = rsIncomeAccts.Field( “Type” ).StringValue
lbExpenseAccounts.cell( i, 4 ) = rsIncomeAccts.Field( “Description” ).StringValue
rsIncomeAccts.MoveNext
next[/code]
I have a PagePanel control with several pages. I have RecordSets for different account types and a corresponding ListBox that contains the appropriate account type. The Methods and the ListBoxes are clones of the first one which works. I have been trying to isolate the problem and have gotten to the above code which is making me want to become a farmer instead of a coder. In the method that selects income accounts I added code that should put the same information in the Expense listbox.
The 1st set of instructions properly populates the lbIncomeAccounts ListBox. The CLONED code right under it with the only change being the name of the ListBox doesn’t work. Note that I display on the screen, the record counts for all of the RecordSets and they are fine. They select the proper number of rows. In this test that I am using there are 7 Income Accounts and 52 Expense Accounts. The lbIncomeAccounts displays 7 rows of information. The lbExpenseAccounts ListBox shows NOTHING.
The above test should show the same 7 rows in both ListBoxes but again, the lbExpenseAccounts shows nothing.
I have examined the two ListBox controls until my head hurts and can’t see any difference. I even thought maybe the rows were there but for some reason just weren’t visible so I added a double click event handler and tried to double click on the listbox with no luck.
Could someone PLEASE suggest what I need to look at or test or do.
I really need a second set of eyes looking at this. Thanks a bunch in advance.
I’d suggest putting a breakpoint in the code right before the second rsIncomeAccts.MoveFirst and having a look at rsIncomeAccts in the debugger to check its EOF property, which should be TRUE. Then step through the rsIncomeAccts.MoveFirst statement and see if EOF has become FALSE which it should if the moveFirst worked as you think it did. Click on the Contents link of the recordset to verify that it once again is pointing to the first record, and has the expected data in it.
In that case, you compute many times iRows - 1. Better do iRows - 1 before the loop (or use a different variable if you have to keep the original iRows value), or compute it at asking time:
iRows = rsIncomeAccts.RecordCount - 1
A check after that line on the iRows value: if it is 0 or less, stop there.
You do not test rsIncomeAccts too. You have to check what dbSQL.SQLSelect(sSQL) returns.
As for the “shortcomings in the underlying engines” it’s not true even. PostgreSQL, for example, have a complete set of recordset walking functions and Xojo sadly opted for not implementing. There is a Xojo layer above the DB layer, and that layer rules the DB Layer.
I seem to remember a report from some time ago where if you went beyond the end of a recordset it became unusable, even for sqlite. I wonder if that has crept back in (or if it was ever fixed)?
