Code Review - Please :)

Databases
1

Hi,
in my window called NewSnippetWindow, I have the following code inside the save button’s action event, which should write to the database:

[code] if TitleField.text <> “” and CodeField.text <> “” then

dim insertRec as new DatabaseRecord
insertRec.Column( "Title" ) = TitleField.text
insertRec.Column( "Code" ) = CodeField.text
db.InsertRecord( "Snippets", insertRec )
db.commit
MainWindow.Timer2.mode=MainWindow.Timer2.ModeSingle
NewSnippetWindow.close

end if

if db.error then
MsgBox (“Error saving snippet to database!”)
self.close

end if[/code]

However, when I click on the save button, the window (NewSnippetWindow) does not close, and nothing gets written to the database??

In a Module I have a property:

db As SQLiteDatabase

Any ideas what could be wrong?

2

And you typed something into both the TitleField and CodeField, yes?

3

Yes :slight_smile:

4

The SQLite Database Table name is Snippets, with 2 columns - Title (TEXT) and Code (BLOB)

5

I’m not sure yet, but you have 2 places you are closing a window. I would rewrite it to be:

  If TitleField.text <> "" And CodeField.text <> "" Then
    Dim insertRec As New DatabaseRecord
    insertRec.Column("Title") = TitleField.text
    insertRec.Column("Code") = CodeField.text
    
    db.InsertRecord("Snippets", insertRec)
    If db.Error Then
      MsgBox("Could not save snippet to database: " + db.ErrorMessage)
      
      Return
    End If
    
    db.commit
    
    MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
    
    Self.Close
  End If

No need to check db error if you don’t try anything. No need to close the window in multiple places, once by calling NewSnippetWindow.Close and the other by Self.Close

Oh, also the DB error should be checked after db.InsertRecord, not db.Commit. It may be that you should check after db.Commit depending on other actions you may have performed.

I’m also assuming that if the database save fails, you don’t want to close the window, maybe give the user a chance to correct a problem.

6

Thanks Jeremy,
I used your code but have exactly the same problem.

7

Does setting a break point on the 1st line of your Save method and stepping through the code line by line in the debugger shed any light on what’s going on?

8

Jeremy, do you mean set a breakpoint on the first line of the save button’s action event?
I ask because I do not have a method called save?

9

Richard. Sorry about that. I meant whatever method it is that we are working on. i.e. set a break point on the opening If statement and walk through the code. See what’s up. It should shed light on the problem, report back with what you find.

10

I know this will sound dumb but I have never used breakpoints and stepped through code before.

I clicked to the right of the first line and a red dot appeared (I presume this is a breakpoint).
I have set it to BreakOnExceptions, but I do not know how to STEP THROUGH LINE BY LINE, and I also do not know what I would be looking for?

11

Richard, this will be a good experience for you then as it is a very, very, very valuable debugging technique. I believe once you do it once or twice you’ll wonder how you have lived without it.

So, you got the red dot. Great! Next step is to run your application. When you press the Save button (or whatever button triggers this method), Xojo should come forward in the debugger and show your code. There will be a new toolbar present with various actions such as Continue, Step Over, Step Into, etc… For this debugging, Step Over is probably all you need. Hover your mouse over the various buttons in the toolbar.

I think when you do it, you’ll see what is going on but basically it allows you to run the code one line at a time and watch what is happening.

12

OK - when I run the app from the IDE, enter text in both fields, and then click on the save button - I get returned to the IDE and I see the following info at the bottom:

Screenshot

13

Jeremy - I did as you said and I managed to be able to step through each line of code, but all I see is different information such as in the above screenshot - I have no idea what to look out for?

Sorry for being so dumb :frowning:

14

Did it enter the If statement? Also, not dumb just learning new things! Did it execute the db.InsertRecord statement and the Self.Close or did it skip right by those?

15

OK - when I run the app from the IDE, enter text in both fields, and then click on the save button - I get returned to the IDE and I see the first line of code.

Each time I press the STEP button, it moves down a line of code and highlights it - until I get to line 5 db.InsertRecord(“Snippets”, insertRec) - it highlights that line BUT shows a little red bug to the left!

See the below screenshot:
Bug Screenshot

When I press the STEP button once more it jumps somewhere else - please see the attached screenshot:

Screenshot

16

OK, there we go! Look down at the bottom of the screen, see the Error: NilObjectException?

My guess is that db is never instantiated, thus Nil. Do you call db = New SQLiteDatabase and subsequently open the database somewhere?

17

That means your INSERTRECORD line had an exception… and since you have no error checking… it went to an UNHANDLEDEXCEPTION handler…

18

Jeremy - I can see a line which reads: Exception: NilObjectException - Yes :slight_smile:

In my MainWindow’s open event I call the following Method:

opendatabase

That Method contains the following code:

[code]// CHECK TO SEE IF THE SN DATABASE FILE EXISTS - IF NOT - CREATE IT
Dim f As FolderItem = SpecialFolder.ApplicationData.Child(“Snippet Cache Data”)
If Not f.Exists Then f.CreateAsFolder
f = f.Child(“Snippets.db”)

dim db as new SQLiteDatabase

db.DatabaseFile = f

if not f.Exists then

If db.CreateDatabaseFile Then
  db.SQLExecute("CREATE TABLE Snippets(SRef INTEGER PRIMARY KEY, Title TEXT, Code BLOB)")
  If db.Error Then
    MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
  End If
Else
  MsgBox("Snippets database could not be created - this app will now need to close!")
  Quit()
End if

End if

if not db.Connect() then
MsgBox(“Could not connect to the snippets database - this app will now need to close!”)
Quit()

else
PopulateSnippets(Listbox1,db,“select SRef, Title, Code from Snippets order by Title desc”)
end if[/code]

That above code successfully creates (or opens) my database file.
Is it something to do with the scope - as I dimmed it in the MainWindow, but am using it from NewSnippetWindow?

P.S
Thanks Dave :slight_smile:

19

put a breakpoint on the INSERT line… then see what object is NIL that you are expecting/assuming should not be NIL
backtrack from there.

20

Richard, there is the error.

Dim db As New SQLiteDatabase

That creates a local variable db which is probably shadowing your Window’s db property. Thus, the local db is being set, but your Window’s db is not.

Change that line of code to read:

db = New SQLiteDatabase

and see if things work better.