hierarchical listbox/SQLite problem

I’m working on a snippet library and I want to use a hierarchical listbox, populated from an sqlite database. Not a problem, except that when I add a new record to the db, and reload the listbox in the “main” window, I get multiple listings of the same category. This is only a problem when the “reload” is initiated from another window. My sql looks correct (works elsewhere), so I can’t figure out what’s wrong. If somebody could help me with this, I’d really appreciate it.
This code is in my “AddNew” window:

[code]dim querystr as string
dim rs as recordset

querystr = “SELECT DISTINCT id, cat FROM snips ORDER BY cat”
rs = dbget(querystr) // dbget is contained in a module

cats.DeleteAllRows // “cats” is the name of the category listbox
main.titles.DeleteAllRows // “main” = Window1, renamed
main.display.text = “” // textarea
main.sntitle.text = “” // text field

Do Until rs.EOF
cats.AddRow rs.Field(“cat”).StringValue
main.titles.AddFolder rs.Field(“cat”).StringValue
main.titles.RowTag(main.titles.ListCount - 1) = rs.Field(“id”).StringValue
rs.MoveNext
Loop[/code]
This is what I get in my main.titles listbox: (there should only be one “Misc” category listed)

@Bill Dawson — Are you sure that using “DISTINCT id, cat” is a good idea? I would understand it (but I’am not a specialist) that the same cat name but with a different id (which is always the case) would create a new entry.

Put the code to reload (the good one) in a Module and call it from both windows ?

Stephane: Thanks for the reply. The exact same sql is used in the main window to populate the main.category listbox in its “open” event, and it works. The code I posted above is in a different window named “AddNew”. This is where something breaks. You could be right, but since everything else I’ve tried with the sql fails, I’m thinking it’s the xojo code somehow…I’m just not seeing it.

Emile: Brilliant. Thanks for the idea. :slight_smile:
I’ll post back my results.

@Bill Dawson — Well I have checked on internet and my first feeling seems to be right. If you use “DISTINCT id, cat”, you will get a new value for each id, i.e. for each row since id is meant to be unique.

If it works somewhere else, then you did something special there so it works as intended.

DISTINCT works fine here to return the number of persons that comes from different states (countries) when the spelling is correct throughout the DB. :wink:

Thanks to this forum for the tip (around six years ago).

@Emile Schwarz — Are you sure you are answering the right thread?

His SQLite statement retrieves all rows which have a different id (i.e. row_id, which is always true by definition) or cat (probably for “category”). Unless you do something else to filter the results, you just end up with what the OP reported, i.e. each item has its own folder.

[quote=433387:@StphaneMons]@BillDawson Well I have checked on internet and my first feeling seems to be right. If you use “DISTINCT id, cat”, you will get a new value for each id, i.e. for each row since id is meant to be unique.

If it works somewhere else, then you did something special there so it works as intended.[/quote]
I’m so confused. I agree with you, based on my own searching. Yet it works in one instance. I really don’t get it. Below is a complete copy of both code chunks for comparison. Aside from obvious name-targeting in the xojo code, I don’t see a difference. I hope this helps somehow. Also, I’m at a total loss as to how to do this without using the record id as the rowtag .

main.titles listbox open event: (this one works)

[code]dim querystr as string
dim rs as recordset

querystr = “SELECT DISTINCT id, cat FROM snips ORDER BY cat”
rs = dbget(querystr)

Do Until rs.EOF
me.AddFolder rs.Field(“cat”).StringValue
me.RowTag(me.ListCount - 1) = rs.Field(“id”).StringValue
rs.MoveNext
Loop[/code]

AddNew window “Save” button action:

[code]dim querystr as string
dim rs as recordset

querystr = “SELECT DISTINCT id, cat FROM snips ORDER BY cat”
rs = dbget(querystr)

cats.DeleteAllRows // cats stands for categories, a listbox in this window
main.titles.DeleteAllRows // titles listbox in “main” window
main.display.text = “”
main.sntitle.text = “”

Do Until rs.EOF
cats.AddRow rs.Field(“cat”).StringValue // reload the cats listbox
// reload main.titles listbox
main.titles.AddFolder rs.Field(“cat”).StringValue
main.titles.RowTag(main.titles.ListCount - 1) = rs.Field(“id”).StringValue
rs.MoveNext
Loop[/code]

Apparently there is nothing wrong with selecting more than one item as distinct in sqlite.
This page: sqlite-select-distinct
shows that the following is a legit statement:

SELECT DISTINCT city, country FROM customers ORDER BY country

As compared to my sql:

SELECT DISTINCT id, cat FROM snips ORDER BY cat

So, as I originally suspected, my sql is not the problem. Also, Emile’s suggestion of using a module is a great idea, except that I just found out I have no idea what to do there.
Any thoughts?

a. DISTINCT and id
if the column you named id is the unique id, you will be in trouble: using DISTINCT there will select the whole RecordSet: Unique ID is… a unique id ! this value change for each Row (there is no two or more times a Row with the same id).

Of course, if id is the customer id or any other thing excepted Unique ID, then, you can use that code.

Edit: it will be a good idea to rename that column if it does not means Unique ID (the SQLite Column to identify a Row).
Same with cat (the column name you use, not Kitty): add something to tell what it really is: cat_name is an example.

b. How to add a Mondule to a Project ?
Very simple: in the IDE, press the Insert MenuItem and activate the correct entry (Module).

HTH.

Thanks Emile. I’m dense sometimes, but I get it now with regard to the unique id…and the id I’m using is unique. and auto-incremented. Still, what you’re saying means that my sql shouldn’t work at all, and yet it does if used/loaded within the same window. That’s very puzzling.
I understand how to add a module. What to put in it is where I’m lost. I did create one long ago for connecting to a db and running an sql query, but my old brain is just not making sense of this new one.
It looks to me like I have some restructuring to do. Thanks everyone for your time, much appreciated.

Correction: id is NOT unique. I thought I had set it up that way, but I didn’t. Am I correct in assuming that id is not an issue after all, and that explains why it works in one instance? If so, then why does it only work if the sql is called and its data loaded all within the same window? My listbox name-targeting between 2 windows is also correct. I have checked and re-checked so many times.
id is, however a primary key. Does that make a difference? Sorry for the bonehead questions, but I’m shooting in the dark here.

Everything works “as-is” if I just use 2 listboxes instead of a single hierarchical one. One LB for categories and one LB for titles does the trick. Not what I wanted, but it works, so I’ll use that.
Sorry for wasting everybody’s time. Fading back into the woodwork now…

Hi Bill,

There’s nothing wrong with your SQL and I think your Xojo code is doing what you’ve instructed it to do, i.e. adding a new folder for each record in the recordset.

It’s been a while since I used Xojo (I’m just a lurker these days, waiting for Android support to happen) but iirc when you collapse a node in a hierarchical listbox its child nodes get cleared. You’re supposed to populate the child nodes on the parent’s ExpandRow event.

Thanks, Steven. I do populate the child nodes on the parent’s ExpandRow event. That code just never came up, so it was never posted. Here it is:

[code]dim querystr as string
dim rs as recordset

querystr = “SELECT title FROM snips WHERE id = '” + me.RowTag(row) + “’”
rs = dbget(querystr)

Do Until rs.EOF
me.AddRow rs.Field(“title”).StringValue
rs.MoveNext
Loop[/code]

[quote=433472:@Bill Dawson]Thanks, Steven.[/quote]I’m only Steven when I’m naughty :wink:
.
.
If I understand what you want correctly your first SQL statement should be:

querystr = "SELECT DISTINCT cat FROM snips ORDER BY cat"

That gives you one folder for each category.

And in your ExpandRow event handler:

querystr = "SELECT title, id FROM snips WHERE cat = '" + me.Cell(Row,0) + "'"
// but you should really make the above a parameterised query ;-)

That will fetch all titles within the selected category.

Sorry for the late reply…life gets complicated sometimes.
Steve, you’re a God-send. That works GREAT! Such a simple solution, too. Thank you so much!!!