SQLite to DataGrid

[Proent]

I am trying to load the contents of a SQLite database into the datagrid. I have been all over the forums and can't find any examples here or at IcyNorth. Can someone show me how to do this? I need the grid to update anytime the database updates.

[bule]

LOL, dude, there are no database events in SQLite that you could hook to. Even if there were, you wouldn't have a component in APMS to connect to that hook (like the one that comes with Direct Oracle Access package for Delphi).

If you want to load a SQLite.QueryToTable result to a grid or datagrid, each time you enter a page, you have to:
1. prepare a grid/datagrid from scratch (column names, sizes, etc...) using actions, since it's a jerky component that doesn't keep it's state when you change pages,
2. run a SQLite.QueryToTable action to fill the grid,
3. eventually select the last item that was selected in the grid before you left the current page (if you make some mechanism to remember this).

Here is an example for the query:

local count, row
local cus = SQLite.QueryToTable(db, "SELECT * FROM customers ORDER BY name LIMIT 1000;")
DataGrid.DeleteNonFixedRows("dg1", true);
if cus and cus.Rows>0 then
	for count=1, izm.Rows do
		row = DataGrid.InsertRow("dg1", false);
		DataGrid.SetCellText("dg1", row, 0, id, false)
		DataGrid.SetCellText("dg1", row, 1, name, false)
		-- other columns you need
	end
end
DataGrid.Refresh("dg1")

P.S. "dg1" is a name od the grid/datagrid. Of course, you have to prepare grid before you fill it.

[Proent]

Wow, it's almost not worth it..............
I guess it's going to be more economical to use a couple listboxes instead and make it look "gridlike". I guess theres no way to load it to a HTML file that the user can make selections in and edit any of the fields is there?

[bule]

Well, the grid is still better than a one column limited listbox or multiple listboxes simulating a grid. The grid looks like an old Buick, but what can do.

Editable HTML? LOL, it's doable and you could do some beautiful editable tables with it, but your HTML, JavaScript and Lua code would be as long as Mount Everest. Actually, longer!!!

[Proent]

Yeah I agree. I went over the other options and came back to the grid. Though it's pain it's still the shortest distance between two points and looks the cleanest. Thanks Bule.

[RizlaUK]

The grid looks like an old Buick

ha ha ha, yeah it dose that

iv just been playing with an ocx i made, just a listicon on a borderless page, set the ams page to non movable and set the listview window position and i have a usable multi column listbox embedded into/on top of the page, can even catch mouse clicks, i may release it later

[Worm]

I made this example when Brett and I colaborated on the DataGrid. Here it is, modified to work with the IR Grid. The project allows you to open a SQLite database. The tables are listed on one side, once you select a table, the data is bound to the second grid. You can edit the data in the grid, and the results will be saved once you leave the cell.

[RizlaUK]

i was toying with the idea of making a generic grid on my projects main page that would contain various info from sections of the app at the click of a button, originaly i decided not to bother just yet as i couldent be botherd to reset the grid every time (hopeing for a replacment )

but your "BindGrid" function takes care of that.

a very useful/helpful example worm, thanks

btw, i dident know you had a hand in on the grid, i should have gussed really tho, some of the functionms have a *worm* feel to them, lol

[Worm]

in that post, colaborating's definition would be...

- Brett being a nice guy and letting me ride on his coat-tail

[Proent]

Thanks for saving the day again Worm. That was exactly what I needed. Not only does it fix the problem at hand it opens up a huge array of other ideas.............so much programming so little time...............

[mwreyf1]

Great example Worm, Thanks!

[Worm]

Sure thing...

I'm sittin' in a hotel in Dallas, it was nice to have something to kill some of the down time with.

[Proent]

So along the same lines, stemming off of this. Could the data be loaded into pre-existing columns without changing the heading text in the fixed rows? And could the listbox be removed so the database with a single table could be opened and edited in the grid? Just in case you needed to kill more time.

That may work better in my current project. I can't seem to get it to keep the column header text in place without screwing up the order of everything.

[Worm]

You can pass any query you want to the BindGrid function, just be sure to add RowID as the first column. SQLite has a "hidden" column named that that is more a less a rowid.

If you want different column names, use an alias in your select statement.

Here's an example... you'll need to have the database (Northwind) opened first.

		nCursor = SetCursor(32514)
		BindGrid(dbHandle, "grdQueryResult", "Select RowID, OrderID as OrderNum, CustomerID as CustomCustomerID, RequiredDate as DifferentName from Orders");
		ReturnCursor(nCursor)
		Grid.AutoSizeColumns("grdQueryResult", GVS_BOTH, true)
		Grid.SetGridLines("grdQueryResult", true, true)

[mwreyf1]

Dallas!...My neighborhood.

Try the mexican food while your here.

We have the BEST in the country.