For someone this is probably really simple. Me, not so much…
I am trying to fill in the column header from a database I open, so it can change dependant on the database.
Is there a command in Databases to do this? I can’t seem to find it, even in documentation… but I might be searching wrong.
Pointing me to the right place in documentation would be great too…
Regards.
You can get the columns of a table with the TableColumns function. You get a RowSet that contains the column name and data type. The last time I used it though (for ARGen) certain database types needed to be handled manually instead.
And remember that some characters cannot be used as Column names…
if you made a query and have a rowset you can read the columns names from there to put them into a listbox.
' Add the DB columns as the heades for the ListBox
dataList.ColumnCount = rs.ColumnCount
dataList.ColumnAt(-1).WidthExpression = "100"
For i As Integer = 0 To rs.LastColumnIndex
dataList.Heading(i) = rs.ColumnAt(i).Name
Next
https://documentation.xojo.com/api/databases/rowset.html#rowset
I am trying to fill in the column header from a database I open,
you mean table, a database have tables, a table have fields.
example:
Var columns As RowSet = db.TableColumns("team")
var htext as string
var w As integer
var hh as integer = 0
var hwidth As string
For Each c As DatabaseRow In columns
htext = c.Column("ColumnName").StringValue
listbox1.HeaderAt(hh)=htext
w = htext.Length * 30. // ?
hwidth = hwidth + w.ToString+" , "
hh=hh+1
Next
listbox1.ColumnWidths = hwidth
In the Listbox’s Paint event, use g.TextWidth to get the width of the text.
Hi Rudolph and all.
Sorry for taking so long to get back but a) have been dealing with life issues… the wonderful stuff that sneaks up and hits you on the back of the head when you aren’t looking or prepared, and
b) tryinng to figure this out on my own.
I cannot get my table column headers no matter what I try. Maybe someone can explain to me what I am doing wrong.
What I am attaching is my current “attempt” to get this working. I don’t even attempt to fill in the headers, just get to them. I always, no matter what I try get a nil object exception for my database.
//for testing only
MessageBox "Got to column header subroutine"
var theColumnHeaders as Rowset
var thedatabase as New SQLiteDatabase
thedatabase = app.splashWindowDB
thedatabase.EncryptionKey = app.whatIsTheEncryptionKey
the app.splashWindowDB is a variable where I store the sqlite database I open.
The encryption key for the database is … the encryption key (duh).
When I debug, no matter what I try I get NilObjectException.
Help?
This must be only a portion of your code. Can we see all the code that opens the database, does a SELECT from some table and then tries to get the column names from that RowSet ?
Hi Tim.
Let me did this up. I have multiple versions to dig through.
Stay tuned 
Regards
Hi Tim.
Here is the current code I have from my program (and yes, if it looks like something from examples, you are right )
//for testing only
MessageBox "Got to column header subroutine"
var theColumnHeaders as Rowset
var thedatabase as New SQLiteDatabase
var htext as String
var hh as integer = 0
thedatabase = app.splashWindowDB
thedatabase.EncryptionKey = app.whatIsTheEncryptionKey
theColumnHeaders = thedatabase.TableColumns("passwords")
For Each theColumnHeaders As DatabaseRow In columns
htext = theColumnHeaders.Column("ColumnName").StringValue
mainWindow.listbox1.HeaderAt(hh)=htext
hh=hh+1
next
No matter what I try I get a NilObject exception at line 13
theColumnHeaders = thedatabase.TableColumns(“passwords”)
It has also filled in the first row of data, in this case the site names.
Regards
What does the debugger tell you about thedatabase? Is it Nil? Is any other variable Nil? is app.splashWindowDB Nil?
I don’t think you need “new” in the declaration of thedatabase, you are setting it in line 10. Does the “passwords” table exist in your app database?
Hi Tim.
Ok… one at a time.
What does the debugger tell you about thedatabase?
a) when I initially connect I see the database (SQLiteDatabase) I have initially Selected with the Open Dialog.
b) The DatabaseFile name is the correct name.
Is it Nil? Is any other variable Nil? is app.splashWindowDB Nil?
a) the app.splashWindowDB (the one I selected NOW shows as NIL), which would explain my error.
But I have no idea why.
I think I might have to step-by-step through every line!
Regards
Then what is the code that sets app.splashWindowDB ??
And make sure that code does not contain a line that says
var splashWindowDB as SqliteDatabase
Hi Tim.
Here is how the splashWindowDB is set up as a property under App
Name: splashWindowDB
Type: SQLiteDatabase
Scope: Public
Here is the code that shows the “setup” of the app.splashWindow
//disable the timer... you clicked the pic, so you are heading onward already
splashWindow.Timer1.Enabled = FALSE
//set a variable so we can first get the db we want to look at before asking for the key
//we can also then make the encryption key window a little more ... accurate..
app.splashWindowDBFile = FolderItem.ShowOpenFileDialog("")
if app.splashWindowDBFile = NIL then
MessageBox ("You must select a database file (sqlite) to open. Please try again.")
Exit
else
end if
//close the splash window
splashWindow.close
// show the encryption key window.
// in the method we will fill in the name of the selected database for clarity's sake
encryptionKeyWindow.Label2.text = app.splashWindowDBFile.Name
//show the window
showTheEncryptionKeyWindow()
Hi Tim Hare… (two Tim’s). You don’t have shares in TIm Horton’s do you (I’m Canadian… so if you don’t get the joke, it’s ok).
As stated to Tim Streater, here is how I have declared the splashWindowDB
Name: splashWindowDB
Type: SQLiteDatabase
Scope: Public
When I do a Find and look for var splashWindowDB nothing is shown.
Regards
I only wish I had shares in Tim Horton’s. Not Canadian, but I’ve heard about it.
I asked about using a local var because that is a common mistake. You create a local variable with the same name as the global variable and that leaves the global one uninitialized.
What I didn’t see in the code you posted is where you have
app.splashWindowDB = new SQLiteDatabase
You have to explicitly create a database object or the app property will remain nil.
Neither do I see any code to point that variable at an actual database.
Maybe I am wrong, but I don’t see any attempt to connect to the database - without connecting nothing will happen.
See SQLite Connect: SQLiteDatabase — Xojo documentation
Ok, Tim S… and Tim H.
I will try these things, and advise.
The silly thing? I created modules I reuse to make functions I use a great deal easier.
Stay tuned.
Regards