Android Question sql database

[dgoss]

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

thx in advance

 

[Mahares]

Something like this:

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

 

[dgoss]

Something like this:

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

thx mahares works for me

 

[JohnK]

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

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

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

 

[jsanchezc]

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

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

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

To check in a Where if is null you can use ISNULL or COLAESCE statement.
(For more info about ISNULL or COALESCE search at Google: TRANSACT SQL COALESCE
or TRANSACT SQL ISNULL)
ISNULL(FIELD,valuex)
if FIELD is NULL, then statement returns valuex as Value.

So you can also use this:

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.

Also you can set all in a string

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)

Hope this help

 

[Mahares]

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

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

 

[jsanchezc]

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

Ok, i see SQL and i understood SQL Server (MSSQL)

 

[dgoss]

thx guys sorry for delay in replying but i'm doing this on the road, I used Mahares example it works for me with what i'm doing.