Android Question How to manage null values in SqlLite GetString?

[Paolo Pini]

Hi all,
I have new record inserted in a SqlLite table.
Most of this fields are empty/null.
I have the same routine to read new and existent records

I try to read the fields in a loop to check if assign to a variables:

Dim DbField as string="Field1"

If curor.GetString(DbField)<>Null Then
    textbox.Text=cur.GetString(DbField)
Else
    textbox.Text=""
End If

but the Null check return :Error evaluating expression.

I tried many solution without success.

Dim obj As Object=cur.GetString(campodb)
If obj<>Null Then

I read some post that suggest to insert a clause if in the SELECT:

SELECT ifnull(col1, "") as Field1 ......

but is seem a bit complex, moreover I have field name not 'hardcoded' etc.

Some other suggestions to check the Null value in a field?

Thanks in advance

Paolo

 

[Erel]

The IfNull in the query is the best option.

 

[Paolo Pini]

The IfNull in the query is the best option.

Thank you Erel.

 

[Paolo Pini]

Hi all,
I have new record inserted in a SqlLite table.
Most of this fields are empty/null.
I have the same routine to read new and existent records

I try to read the fields in a loop to check if assign to a variables:

Dim DbField as string="Field1"

If curor.GetString(DbField)<>Null Then
    textbox.Text=cur.GetString(DbField)
Else
    textbox.Text=""
End If

but the Null check return :Error evaluating expression.

I tried many solution without success.

Dim obj As Object=cur.GetString(campodb)
If obj<>Null Then

I read some post that suggest to insert a clause if in the SELECT:

SELECT ifnull(col1, "") as Field1 ......

but is seem a bit complex, moreover I have field name not 'hardcoded' etc.

Some other suggestions to check the Null value in a field?

Thanks in advance

Paolo

UPDATE:
I tried to modify the fields property adding DEFAULT 0 for integer and DEFAULT "" for text, seems to work.

 

[RichardN]

UPDATE:
I tried to modify the fields property adding DEFAULT 0 for integer and DEFAULT "" for text, seems to work.

@Paolo Fiaschi

You have arrived at the ideal solution. A thoughtful database design feature that negates programming difficulties later ?

 

[aeric]

UPDATE:
I tried to modify the fields property adding DEFAULT 0 for integer and DEFAULT "" for text, seems to work.

To make it more safer, combine both DEFAULT value when creating table structure and IFNULL when do a query.

 

[Mahares]

adding DEFAULT 0 for integer

The problem with using a default of 0 for integer is you are actually changing the data in the table. Later on, when someone opens the table and looks at the data, they will not be able to tell if the 0 is from using a default of 0 or it is actually input or came from the field. Unless it is not important in your case, I would avoid the DEFAULT gimmick and do not alter the table. But to avoid crashes, use the INULL or COALESCE functions in your queries.