Android Question sql database

Discussion in 'Android Questions' started by dgoss, Aug 30, 2015.

  1. dgoss

    dgoss Member Licensed User

    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
     
  2. Mahares

    Mahares Well Known Member Licensed User

    Something like this:
    Code:
    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
     
    Johan Schoeman and mangojack like this.
  3. dgoss

    dgoss Member Licensed User

    thx mahares works for me
     
  4. JohnK

    JohnK Active Member Licensed User

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

    Code:
    dSql.ExecNonQuery2("UPDATE hours SET my_field_name = ? WHERE my_field_name IS NULL AND rowid = ?"Array As Object("XXXX"id))
     
    LucaMs likes this.
  5. jsanchezc

    jsanchezc Member Licensed User

    Code:
    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:
    Code:
    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
    Code:
    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
     
    Last edited: Aug 30, 2015
  6. Mahares

    Mahares Well Known Member Licensed User

    There is no ISNULL function in SQLite. It should be IFNULL.
     
    Last edited: Aug 30, 2015
    jsanchezc likes this.
  7. jsanchezc

    jsanchezc Member Licensed User

    Ok, i see SQL and i understood SQL Server (MSSQL) :confused:
     
  8. dgoss

    dgoss Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice