List tables in database to listbox or combo box.

[gabrielfenwich]

Hello everyone,

I've been bed ridden for the last month and a half and in order to stay sane I have started to play with APMS again.

I came across the wonderful xMDB.dll in this post

[URL="http://www.indigorose.com/forums/showthread.php?t=19945"]

and have happily spent the last 2 days playing with the example.
Big thank-you to Dermot - you rock

I couldn't figure out how to list the tables in a database so i just opened the database in access and made a new table containing all the names of tables in the database and used that instead.
What I need to do is output database table data to a listbox or combobox so I can set up a easy select type of query system
eg
[combobox1] [input1] [combobox2] etc.
SELECT * IN TABLE1 WHERE ID = 6 etc
because i currently have the memory of a slug due to meds and i don't like retyping stuff all the time.
the code is below from dermots example with a little modification to make it clearer for me.

-
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
--Get the time,clear the datagrid and define local function Query and progress bar
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
StartTime = os.time()

DataGrid.DeleteAllItems("QueryResults")
DataGrid.SetRedraw("QueryResults", true)
--local function
local Query = Input.GetText("SelectQuery")

Progress.SetVisible("Prog", true)
Progress.SetText("Prog", "Querying Database....")


---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
-- set the password to "" and read the database query into a table called "tblTables"
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++

Password = ""
--query database table using string from query function above
tblTables = xMDBQueryToTable(DB, Query)


---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
--make sure tblTables exists go through putting rows and columns into the datagrid
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
if tblTables then

if tblTables ~= "Error" then

Progress.SetRange("Prog", 0, tblTables.Rows)

DataGrid.DeleteAllItems("QueryResults")
DataGrid.SetRedraw("QueryResults", true)
Row = DataGrid.InsertRow("QueryResults", -1, true)
DataGrid.SetFixedRowCount("QueryResults", 1)

-- Loop through the columns and add them to the grid
for nCol=1, tblTables.Columns do

if nCol > 1 then
Col = DataGrid.InsertColumn("QueryResults", -1, false)
end

DataGrid.SetCellText("QueryResults", Row, nCol-1, tblTables.ColumnNames[nCol], false)

end
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
-- here's where I am Stuck
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
-- Insert data
for nRow = 1, tblTables.Rows do
-- Insert a new row in the grid
Row = DataGrid.InsertRow("QueryResults", -1, false)
-- Loop through the fields and add them to the new row in the grid
for nCol = 1, tblTables.Columns do
DataGrid.SetCellText("QueryResults", Row, nCol-1, tblTables.Data[nRow][tblTables.ColumnNames[nCol]], false)
----------------------------------------------------------------
--as well as outputting to the datagrid I want to output into a listbox or combobox
----------------------------------------------------------------

end

-- Update progress bar
Progress.SetText("Prog", "Retrieving Row: "..nRow)
Progress.SetCurrentPos("Prog", nRow)

end

---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
-- format the datagrid to make it look good, update the progress bar
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
DataGrid.AutoSizeColumns("QueryResults", GVS_BOTH, false)
DataGrid.ExpandLastColumn("QueryResults", false)
DataGrid.SetRedraw("QueryResults", true)

Progress.SetText("Prog", "")
Progress.SetVisible("Prog", false)
Progress.SetCurrentPos("Prog", 0)
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
-- See how many seconds it took , update the label for feedback with
---++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++
EndTime = os.time()
Duration = EndTime-StartTime
if Duration == 0 then
Duration = "less than 1 second"
elseif Duration == 1 then
Duration = " 1 second"
else
Duration = Duration.." seconds"
end

Label.SetText("Info", tblTables.Rows.." records returned in "..Duration)

else
Dialog.Message("Failed", "Failed")
end
end
-

Thank-you for any assistance

[RizlaUK]

i dont know about access, but with sqlite you can access the "sqlite_master" table, this contains all the table names within the database, i have used this in the past to list the tables to listbox/combo


you can find more info here (look in TJ_Tiggers posts)
http://www.indigorose.com/forums/sho...=sqlite+master


or, to save changing your app just try

for nCol = 1, tblTables.Columns do
DataGrid.SetCellText("QueryResults", Row, nCol-1, tblTables.Data[nRow][tblTables.ColumnNames[nCol]], false)
----------------------------------------------------------------
--as well as outputting to the datagrid I want to output into a listbox or combobox
----------------------------------------------------------------
ListBox.AddItem("ListBox1", tblTables.Data[nRow][tblTables.ColumnNames[nCol], "");
end

[gabrielfenwich]

Thank-you for your reply RizlaUK.
I have done that with sqlite too.
Sqlite would be the better option, unfortunately it needs to be an access database and the only way I see to query it is with Dermots xMDB.dll which only has 3 functions:
Create_MDB, Query_Select_MDB and Query_Update_MDB.
I think I might just convert the lot to sqlite if i can't figure it out in the next week or so.....
besides, it stopping me going stir crazy.
thank-you for your time,
Gabby

[Dermot]

That information is stored in a system table and cannot be accessed by xMDB. You can only access system tables using Access. I tried lots of things but could not get it to work. If you have added the table names to a table, then you can just read them with a regular query.

[gabrielfenwich]

...sorry that was so obvious .. thankyou.
I'm just ra=ealy thick sometime