Android Question Solved: How to insert or update NULL in Sqlite with ExecNonQuery2

[ernschd]

Hello,

I have a query that is to be used for 4 different options. The parameters differ depending on the option. The problem is that sometimes NULL is to be entered in different fields.
So far there have therefore been 4 different queries.

Here is a simplified example:

Select condition
    Case 0
        SQL.ExecNonQuery2("INSERT INTO Test(CustomerID, State, Invoice, Date) VALUES (?, NULL, ?, ?)", Array As String(NewID, Invoice, DateTime.Date(DateTime.Now))) 'State = NULL, Invoice = 1
    Case 1
        SQL.ExecNonQuery2("INSERT INTO Test(CustomerID, State, Invoice, Date) VALUES (?, ?, NULL, ?)", Array As String(NewID, State, DateTime.Date(DateTime.Now))) ' State = 0, Invoice = NULL
    Case 2
        SQL.ExecNonQuery2("INSERT INTO Test(CustomerID, State, Invoice, Date) VALUES (?, ?, ?, ?)", Array As String(NewID, State, Invoice, DateTime.Date(DateTime.Now))) ' State = 1, Invoice = 0
End Select

Since NULL can occur in different variants of State and Invoice, the whole thing becomes quite confusing.

It would be nice if there was only one query instead:

Dim State As Object = NULL
Dim Invoice As Object = NULL
If Condition = 0 Then Invoice = 1
SQL.ExecNonQuery2("INSERT INTO Test(CustomerID, State, Invoice, Date) VALUES (?, ?, ?, ?)", Array As String(NewID, State, Invoice, DateTime.Date(DateTime.Now)))

Unfortunately, I cannot change the table and set the default value to NULL.

Is there a better solution?
Thanks in advance.

 

[aeric]

3 or 4 options?

 

[ernschd]

Currently 4 options, the code was only intended as a simple example.

 

[klaus]

Do you really need a Null in the database ?
Wouldn't an empty string do it ?

 

[ernschd]

It should be NULL, as the value is checked later.

 

[walt61]

Haven't tested this, but shouldn't this do what you need, e.g. for Case 0?

SQL.ExecNonQuery2("INSERT INTO Test(CustomerID, Invoice, Date) VALUES (?, ?, ?)", Array As String(NewID, Invoice, DateTime.Date(DateTime.Now))) 'State = NULL, Invoice = 1

I.e.: just leave the 'State' column out of the equation, including its placeholder question mark?

 

[ernschd]

But I only want one query for all cases. With this solution, I need several again, depending on the case.

 

[Mahares]

But I only want one query for all cases

Here is another possibility to think about. Using IIF in the parameters array:

Dim strQuery As String = "INSERT INTO Test(CustomerID, State, Invoice, Date) VALUES (?, ?, ?, ?)"
SQL1.ExecNonQuery2(strQuery, Array As Object(newID, IIf(Invoice =Null, 0, state), IIf(state= Null, 1, Invoice), DateTime.Date(DateTime.Now)))

Again, without your DB and code , it is not fully tested. You can polish it.

 

[ernschd]

Thank you, but I want to insert NULL in Sqlite. With your solution this is not possible, either.

 

[walt61]

Just tested this with B4J - YMMV with B4A as I've seen subtle differences between the two when using SQLite, but this worked and produced a row with NULL values:

sql1.ExecNonQuery2("INSERT INTO [Tbl] (Field1, Field2, Field3) VALUES(?, ?, ?)", Array(Null, Null, Null))

In other words, use 'Array' for the parameters, not 'Array As String'. You'll have to test if it acts the same in B4A!

 

[ernschd]

What type of field do you have? For me it is NUMERIC.
With "Array" instead of "Array As String" there appears "0" instead of "NULL" in the corresponding field.

 

[walt61]

I tested with INTEGER, TEXT, and NUMERIC. That could be one of those subtle (or not so subtle in this case) differences I mentioned

Test project attached.

 

[ernschd]

@walt61: Thank you very much, your project has put me on the right track! My mistake was that I declared the variables as "String" in my test project. After changing to type "Object", NULL is inserted as desired.
Many thanks to all of you!

 

[Mahares]

My mistake was that I declared the variables as "String" in my test project. After changing to type "Object", NULL is inserted as desired.

Glad you solved your problem. But, I already used Object instead of string in post #9. Apparently, you did not test it. That is what happens when an OP gets bombarded with posts. Some get overlooked and the OP gets overwhelmed.