Can't get a field from a database in combo box

Databases
1

Hi All.

I am having a little issue here, where I want to read in records and fill in a combobox with one of the columns information.

I can connect to the database, and see I have the correct number of records, but can’t get the combo box filled in. Yes, this is probably easy but it evades me. I have gone through other threads, and followed what I found there, but it just won’t work. Here is my code (when you select a menu item):

// first check for the database and then make sure you can connect
// and before that you will need to make sure the database exists, no?

var sqlcommandToReadAccountsFromDatabase as String
var rs as  RecordSet

var j as integer


Dim dbFile As FolderItem
dbFile = FolderItem.ShowOpenFileDialog("") // Choose a SQLite DB

Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile

If db.Connect Then
  MessageBox "Connected"
  
  // now let's read the accounts in the database and fill in the comboAccountName
  
  sqlcommandToReadAccountsFromDatabase = ("select accountName from theAccounts")
  rs=db.SQLSelect(sqlcommandToReadAccountsFromDatabase)
  
  While Not rs.EOF
    
    winAccountTransactions.comboAccountName.AddRow ""
    
    j=j+1
    
    rs.MoveNext
    
  Wend
  
End If

MessageBox "There are : "+j.ToString+" records"

db.Close

If db.Error Then
  MsgBox("Error: " + db.ErrorMessage)
  Return
End If

winAddAnAccount.close

What am I doing wrong???

2

So you’ve added a bunch of blank lines to the box, with AddRow. Why not add the accountname instead?

3

is adding a blank Row to the comboBox.

winAccountTransactions.comboAccountName.AddRow rs.Field("accountName").StringValue

would add the accountName for the current record.

A few other things to consider. Is the accountName a good reference into the record when it is selected? For example if someone selects one of the account names from the list what are you going to do with it. If you are going to refer back to the database and select something is this the value you need to select on? If not it would be worth adding the table key (what ever the record key is) to the tag property of the row:

While Not rs.EOF
    winAccountTransactions.comboAccountName.AddRow rs.Field("accountName").StringValue
    winAccountTransactions.comboAccountName.RowTagAt( winAccountTransactions.comboAccountName.LastAddedRowIndex ) = rs.Field("accountid")
    j=j+1
    rs.MoveNext
Wend
  • Another thing generally. Where is this code located. If it is on the winAccountTransactions window then you don’t need to refer to it, you can just use comboAccountName.AddRow
  • Secondly, is winAccountTransactions the class name of the window? If it is then you going to have some trouble if you ever have more than one of the windows open. If it is an instance of the class then that is fine.
4

Thanks for the response.

Believe it or not, I had a line like the one you showed:

winAccountTransactions.comboAccountName.AddRow rs.Field("accountName").StringValue

but I always got a weird error. I must have made a typo, because yours works, and mine didn’t.

What I am going to be using the accountName for is to pull information out of the database, so that a user can select the account they want, and add / delete transactions, etc.

Your other information is very helpful, and will take it into account as I proceed.

Once again thank you for showing me the error of my ways…

Regards

5

It would have been better to have reported this error. Anyway that’s fixed now, so good.

I would suggest using RowSet and SelectSQL rather than RecordSet and SQLSelect. That way you get prepared statements for nothing which will be handy later when you need to sanitise data input.

6

If you miss off the .StringValue you will get an error, perhaps it was that.

7

Maybe it’s an encoding issue? But I don’t think so, because we use an SQLite DB.
Still not knowing what went wrong, I took your Code and made a few recommended changes. Just to help you avoid a few possible issues most of us ran into with our first File/Database Projects.

Var rs As RowSet
Var dbFile As FolderItem
dbFile = FolderItem.ShowOpenFileDialog("") // Choose a SQLite DB

If dbFile <> Nil And dbFile.Exists Then // Always be prepared ;-)
  
  Dim db As New SQLiteDatabase
  db.DatabaseFile = dbFile
  
  Try
    
    If db.Connect Then
      
      MessageBox "Connected"
      
      // now let's read the accounts in the database and fill in the comboAccountName
      
      // Var sqlcommandToReadAccountsFromDatabase As String = "select id,accountName from theAccounts" // Save Mem if you can...
      rs=db.SelectSQL("select id,accountName from theAccounts")
      
      If rs <> Nil Then // Again, always be prepared...:-D
        
        While Not rs.AfterLastRow
          
          winAccountTransactions.comboAccountName.AddRow rs.Column("accountName").StringValue.DefineEncoding(Encodings.UTF8)
          winAccountTransactions.comboAccountName.RowTagAt(winAccountTransactions.comboAccountName.LastAddedRowIndex) =  rs.Column("id").IntegerValue
          
          // j=j+1
          
          rs.MoveToNextRow
          
        Wend
        
        MessageBox "There are : "+ Str(rs.RowCount) +" records"
        // or
        MessageBox "There are : "+ Str(winAccountTransactions.comboAccountName.RowCount) +" records"
        
      Else
        
        // Wrong File?
        // Missing Table?
        // Something else changed?
        
      End If
      
    End If
    
    db.Close
    
  Catch err As DatabaseException
    
    // There's so much that can go wrong between the Connect and the Close
    // Better catch possible exceptions
    
    MessageBox("Error: " + err.Message)
    
  End Try
  
  'If db.Error Then
  'MessageBox("Error: " + err.Message)
  'Return
  'End If
  
  winAddAnAccount.close
  
End If