a variable database name

Databases
1

Hi All.

Yet again I need some help, though I have made it quite a way through my program.

I am trying to allow a user to choose which database they want by typing the name into a textfield.

Everything works fine if I do this:

dbFile = SpecialFolder.Documents.Child("theNewPPP").Child("MyDatabase.sqlite")

but if I use a variable, which is declared when the app opens (I want to fill in the listbox immediately), it fails.

dbFile = SpecialFolder.Documents.Child("theNewPPP").Child(App.theChosenDatabase)

Says it wants a string. Change the variable to string, then it wants a database. It is making me nuts… well more nuts.

Any ideas?

Regards

2

dbFile is folderItem variable?
And theChosenDatabase is really string variable?

3

Hi Christian.

dbFile is a folder item so that we can get the database the user wants (now by the line):

dbFile = Folderitem.ShowOpenFileDialog("")
If I set the “theChosenDatabase” as a folder item, a string, anything it calfs.

Regards

4

Without seeing more of your code it’s hard to say what the issue is. There are many things which could be the issue, but I don’t have time to list them all. Seeing your code would whittle it down.

When you say it fails, what exactly is failing? Compiling? Getting an error/exception during run? Just not doing what you want? It always helps to be concise rather than a vague “it fails”.

5

Writing like the above opens for errors.

Use an intermediate FolderItem reference to the “theNewPPP” folder, then create a reference to the database file.

What OS/Version are-you using ?

6

Hi Emile

I’m running Mac Catalina OS
My version of Xojo is: 2019 r3.1

Jay. Here is my code that works:

[code]// set the headers here.

windowMain.passwordListbox.HasHeader = TRUE
windowMain.passwordListbox.HeaderAt (0) = “Site Name”
windowMain.passwordListbox.HeaderAt (1) = “Username”
windowMain.passwordListbox.HeaderAt (2) = “Password”

passwordListbox.ColumnAlignmentAt(0) = ListBox.Alignments.Center
passwordListbox.ColumnAlignmentAt(1) = Listbox.Alignments.Center
passwordListbox.ColumnAlignmentAt(2) = Listbox.Alignments.Center

//fill in the lisbox with the current passwords

Var dbFile As FolderItem //set database variable
Var sql as String
Var rs as RowSet // used to be recordset, but …

//this selection of the database works without flaw
dbFile = Folderitem.ShowOpenFileDialog("") // let the person choose the database. I have filtered what could be chosen

If dbFile <> Nil Then // if it exists then do the following
Var db As New SQLiteDatabase //create an instance of the database
db.DatabaseFile = dbFile
db.EncryptionKey = App.keyholder
sql = “select siteName, userName, sitePassword from passwords order by siteName asc”

Try
db.Connect //try to connect
rs = db.SelectSQL(sql)

MessageBox("Connected to " + dbFile.Name)
MessageBox ("The select sring is : " + sql)

Try
  If rs <> Nil Then
    For Each row As DatabaseRow In rs
      windowMain.passwordListbox.AddRow
      windowMain.passwordListbox.CellValueAt(windowMain.passwordListbox.LastAddedRowIndex, 0) = rs.ColumnAt(0).StringValue
      windowMain.passwordListbox.CellValueAt(windowMain.passwordListbox.LastAddedRowIndex, 1) = rs.ColumnAt(1).StringValue
      windowMain.passwordListbox.CellValueAt(windowMain.passwordListbox.LastAddedRowIndex, 2) = rs.ColumnAt(2).StringValue
    Next 
    rs.Close
  End If
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Catch error As DatabaseException
MessageBox("Error: " + error.Message) //what is the error
End Try
End If[/code]

/ set the headers here.

windowMain.passwordListbox.HasHeader = TRUE
windowMain.passwordListbox.HeaderAt (0) = “Site Name”
windowMain.passwordListbox.HeaderAt (1) = “Username”
windowMain.passwordListbox.HeaderAt (2) = “Password”

passwordListbox.ColumnAlignmentAt(0) = ListBox.Alignments.Center
passwordListbox.ColumnAlignmentAt(1) = Listbox.Alignments.Center
passwordListbox.ColumnAlignmentAt(2) = Listbox.Alignments.Center

//fill in the lisbox with the current passwords

Var dbFile As FolderItem //set database variable
Var sql as String
Var rs as RowSet // used to be recordset, but …

//this selection of the database works without flaw
dbFile = Folderitem.ShowOpenFileDialog("") // let the person choose the database. I have filtered what could be chosen

If dbFile <> Nil Then // if it exists then do the following
Var db As New SQLiteDatabase //create an instance of the database
db.DatabaseFile = dbFile
db.EncryptionKey = App.keyholder
sql = “select siteName, userName, sitePassword from passwords order by siteName asc”

Try
db.Connect //try to connect
rs = db.SelectSQL(sql)

MessageBox("Connected to " + dbFile.Name)
MessageBox ("The select sring is : " + sql)

Try
  If rs <> Nil Then
    For Each row As DatabaseRow In rs
      windowMain.passwordListbox.AddRow
      windowMain.passwordListbox.CellValueAt(windowMain.passwordListbox.LastAddedRowIndex, 0) = rs.ColumnAt(0).StringValue
      windowMain.passwordListbox.CellValueAt(windowMain.passwordListbox.LastAddedRowIndex, 1) = rs.ColumnAt(1).StringValue
      windowMain.passwordListbox.CellValueAt(windowMain.passwordListbox.LastAddedRowIndex, 2) = rs.ColumnAt(2).StringValue
    Next 
    rs.Close
  End If
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Catch error As DatabaseException
MessageBox("Error: " + error.Message) //what is the error
End Try
End If[/code]

Change the following line to:

dbFile = SpecialFolder.Documents.Child("theNewPPP").Child(App.theChosenDatabase)

where App.theChosenDatabase is a property of the application.
I’m thinking it is where I have the Child(App.theChosenDatabase. The file is considered a child in this, correct?

Regards

7

If that’s all you changed, then you aren’t checking that file actually exists. The “If dbFile <> Nil Then” does not mean the file exists, only that the FolderItem object was initialized. You need to check the dbFile.Exists property to ensure the file is actually there. I’m sure you’ll find that either the filename in App.theChosenDatabase is incorrect or the path to it is incorrect (documents/theNewPPP).

Not sure how you’re obtaining the value in App.theChosenDatabase, but I suggest you store the NativePath property instead of just the filename. Then you open with “dbFile = New FolderItem(App.theChosenDatabase)”. That way there’s no question about where the file actually is.

One other item. Though it works, your record (row) iteration is incorrect. The iterator is “row”, but you’re still refer to “rs” in the loop. This has been noted elsewhere as an unexpected anomaly, but there’s no guarantee that it will continue to work in future versions. I suggest changing all the rs.ColumnAt… statements to row.ColumnAt…

8

Hi guys.

Jay thanks for the information. I will correct accordingly.

Fixed the issue with the variable database name.
When the program opens I allow the user to select the database they want, and the encryption key.

I pass the database name to the app.theChosenDatabase. Then they can create their new database, and from then on access it normally.

Maybe not the prettiest, but it does work.

Regard

9

Why are-you using a ListBox to set / get the User Name / Password ?

I would use Labels and TextFields instead in a small window with a nice icon / asking sentence (but this is just me).

10

Hi Emile.

The listbox in my program displays (after the person logs in with the correct key, naturally), the passwords they have entered.
To enter a username / password they get another window (no pretty picture, but I am thinking of adding a pic of one of my cats that passed away as a memorial) to enter the username / password.

Sorry for the confusion. My initial issue was since most people don’t want to have work / home passwords in the same file, I needed a way to get them to have more than one. For some reason I didn’t realize that the database name was never being passed to the app variable.

Me am are dum… :smiley:

Regards

11

OK. :wink: