Error reading date fields after update

I’m new to Xojo and so I found a video series created by Paul Lefebure that walks through building a simple ToDo program. I have followed his examples to a T only changing some old references to REALSQL to read SQLite. I just completed the section titled “Saving Tasks to the Database” and now I have an issue. I can create a task without any issue and it writes to the table just fine, but if I edit the task and change either the name or note section it will save the changes, but it is wiping out the NULL from the date fields and then I can’t run the program again until I delete the record from the table using SQLVue.

I’m leaving the date fields blank when I create the record and they show as NULL in the table when viewed wit SQLVue, but after I edit the record using the code below, the date fields are blank when viewed in SQLVue and I’m unable to run the app again. Below is the code used to update the record.

// Update the data
Dim sql As String
sql = "UPDATE Task SET Name = ?, Note = ?, Completed = ?, Category = ?, CompletedDate = ?, " + _
“Priority = ?, DueDate = ?, DueTime = ? WHERE ID = ?”

Dim statement As PreparedSQLStatement = ToDoDB.Prepare(sql)
statement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) // Name
statement.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) // Note
statement.BindType(2, SQLitePreparedStatement.SQLITE_BOOLEAN) // Completed
statement.BindType(3, SQLitePreparedStatement.SQLITE_TEXT) // Category
statement.BindType(4, SQLitePreparedStatement.SQLITE_TEXT) // CompletedDate
statement.BindType(5, SQLitePreparedStatement.SQLITE_INTEGER) // Priority
statement.BindType(6, SQLitePreparedStatement.SQLITE_TEXT) // DueDate
statement.BindType(7, SQLitePreparedStatement.SQLITE_TEXT) // DueTime
statement.BindType(8, SQLitePreparedStatement.SQLITE_INTEGER) // ID

statement.Bind(0, Name)
statement.Bind(1, Note)
statement.Bind(2, Completed)
statement.Bind(3, Category)

If CompletedDate <> Nil Then
statement.Bind(4, CompletedDate.SQLDate)
Else
statement.Bind(4, “”)
End If

statement.Bind(5, Priority)

If DueDate <> Nil Then
statement.Bind(6, DueDate.SQLDate)
Else
statement.Bind(6, “”)
End If

statement.Bind(7, DueTime)
statement.Bind(8, ID)

statement.SQLExecute

If Not DatabaseController.IsError Then
ToDoDB.Commit
Return True
Else
ToDoDB.Rollback
Return False
End If

Thanks in advance,
Rick

An empty string is not the same as NULL. Try this in the appropriate section above:

If DueDate <> Nil Then
statement.Bind(6, DueDate.SQLDate)
Else
statement.Bind(6, Nil)
End If