Android Question sql database

Mahares said:

Something like this:

B4X:

Dim dSql As SQL
dim Cursor1 as Cursor
dSql.ExecQuery("SELECT * FROM hours WHERE ROWID = " & id)
Cursor1.Position=0
If Cursor1.GetString("FIELDNAME") =Null Then
    dSql.ExecNonQuery2("UPDATE hours SET FIELDNAME= ? WHERE ROWID =?",Array As Object("dgoss",id))
End If

Click to expand...

dgoss said:

I am using the following statement to read database:-
dSql.ExecQuery("SELECT * FROM hours WHERE rowid = " & id)

the table has about 13 fields, how do I check if a field is NULL and insert some other text

Click to expand...

dSql.ExecNonQuery2("UPDATE hours SET my_field_name = ? WHERE my_field_name IS NULL AND rowid = ?", Array As Object("XXXX", id))

JohnK said:

I would do the whole process in SQL in a single line of code eg:

B4X:

dSql.ExecNonQuery2("UPDATE hours SET my_field_name = ? WHERE my_field_name IS NULL AND rowid = ?", Array As Object("XXXX", id))

Click to expand...

dSql.ExecNonQuery2("UPDATE hours SET my_field_name = ? WHERE isnull(my_field_name,'')=''  AND rowid = ?", ArrayAs Object("XXXX", id))

dSql.ExecNonQuery2("UPDATE hours SET my_field_name =ISNULL(my_field_name, ?) WHERE  rowid = ?", ArrayAs Object("XXXX", id))
'Here if my_field_name not is null, it keeps its value,
'if my_field_name is null, "XXXX" is set as new value.

Dim SQLString as String=""
Dim StrNewValue as String="XXXX"
SQLString="UPDATE hours SET my_field_name =ISNULL(my_field_name, '" & StrNewValue & "' ) WHERE  rowid = " & id.ToString
'SQLString contents looks like this (for example if id=21):
'"UPDATE hours SET my_field_name =ISNULL(my_field_name, 'XXXX' ) WHERE  rowid = 21"
dSql.ExecNonQuery(SQLString)

jsanchezc said:

my_field_name =ISNULL(my_field_name, '" & StrNewValue & "' )

Click to expand...

Mahares said:

There is no ISNULL function in SQLite. It should be IFNULL.

Click to expand...