Database Opening & Error Checking

Hi all,

I just wanted to do a quick sanity check on the following code I currently use (DB + field names changed, code is otherwise identical).

  Dim MyDatabase As New SQLiteDatabase
  MyDatabase.DatabaseFile = GetFolderItem("MyDatabase.sqlite")
  
  If MyDatabase.Connect Then
    
    Dim MyRecordSet As RecordSet
    MyRecordSet = MyDatabase.SQLSelect("SELECT * FROM <TableName> ORDER BY <FieldName>")
    
    If MyDatabase.Error Then
      MsgBox("Database error :" + Chr(13) + Chr(13) + MyDatabase.ErrorMessage)
      MyDatabase.Close
      Exit
    End If
    
    If MyRecordSet <> Nil Then
      While Not MyRecordSet.EOF
        listbox.AddRow(MyRecordSet.IdxField(1).StringValue)
        MyRecordSet.MoveNext
      Wend
    Else
      MsgBox("MyRecordSet = Nil")
    End If
    MyDatabase.Close
  Else
    MsgBox("Unable to connect to database")
  End If

Does this look reasonable or could it be improved ?

Only thing I can think of right now is to possibly change the If… Then to something like this :

    If MyDatabase.Error Then
      MsgBox("Database error :" + Chr(13) + Chr(13) + MyDatabase.ErrorMessage)
      MyDatabase.Close
      Exit
    ElseIf MyRecordSet <> Nil Then
      While Not MyRecordSet.EOF
        listbox.AddRow(MyRecordSet.IdxField(1).StringValue)
        MyRecordSet.MoveNext
      Wend
    Else
      MsgBox("MyRecordSet = Nil")
    End If
    MyDatabase.Close

Any thoughts folks ?

Thanks.

You could probably clean up the code a little, since the only time MyRecordSet will be nil is if MyDatabase.Error is true.

If MyDatabase.Error Then MsgBox("Database error :" + Chr(13) + Chr(13) + MyDatabase.ErrorMessage) MyDatabase.Close Exit Else While Not MyRecordSet.EOF listbox.AddRow(MyRecordSet.IdxField(1).StringValue) MyRecordSet.MoveNext Wend End If MyDatabase.Close

Like this maybe ?

What is the Exit for? Is that an Exit Sub? aka Return? If so

If MyDatabase.Error Then
      MsgBox("Database error :" + Chr(13) + Chr(13) + MyDatabase.ErrorMessage)
      MyDatabase.Close
      Return
End If
While Not MyRecordSet.EOF
      listbox.AddRow(MyRecordSet.IdxField(1).StringValue)
      MyRecordSet.MoveNext
Wend
MyDatabase.Close

Or you could factor the MyDatabase.Close out like

If MyDatabase.Error Then
      MsgBox("Database error :" + Chr(13) + Chr(13) + MyDatabase.ErrorMessage)
Else 
      While Not MyRecordSet.EOF
        listbox.AddRow(MyRecordSet.IdxField(1).StringValue)
        MyRecordSet.MoveNext
      Wend
End If
MyDatabase.Close

Thanks Tim,

Your second example looks good. I just want to get the code optimized as much as possible.