SQLite ignoring null on edit using the RowSet.EditRow method

Databases
1

I am using a SQLite Database with a Windows Desktop Application.

I have following code is inside a Method with the Parameters: RecID As Integer, StartDate As DateTime:

Var rs As RowSet
Var sql As String = "SELECT * FROM tbl_employee WHERE id =" + RecID.ToString

Try
	rs = self.SelectSQL(sql)
	rs.EditRow
	rs.Column("start_date").DateTimeValue = StartDate
	rs.SaveRow
	rs.Close
Catch error As DatabaseException
 MessageBox("DB Error: " + error.Message)
End Try

The Method works fine when the StartDate parameter is Not Null. However, when the StartDate parameter is Null and it is replacing a non-null value in the database, the update is ignored with no errors and the edit remains unchanged.

Executing the following SQL statement does work with no problems with the same database:

UPDATE tbl_employee SET start_date = NULL WHERE  id = "  + RecID.ToString

However, having to write a separate procedure for every variable that is null seems so clumsy and laborious, especially when there are several variables or columns to update.

Does anyone have a solution or workaround where I can use the EditRow method to update a database when the values are Null?

2

If StartDate is null, you may need to check for that and use

rs.Column("start_date").Value = Nil ' sets to NULL in the database

Note: no experience, just read the documentation here
https://documentation.xojo.com/api/databases/databasecolumn.html#databasecolumn-value

3

Thanks Alberto! That works!

I missed that when I originally read the documentation.

1 Like
4

just for the records, I found editrow having lots of weird problems along the years…
making a string with your update statement is far easier and works all the time
(except if you have to take sql injection into account…)

5

Databases in general treat NULL diferently from other values. That’s why I always set a column to NOT NULL and set a default value.

6

If you use API2 database methods you don’t need to worry about sql injection; the parameters are bound for you:

sql = "update students set firstname = ?1 where id = ?2"
db.ExecuteSQL (sql, "Jean-Yves", 27)
7

I find NULL very nice to see if a field has been entered by the user or not. I often use them.