Results 1 to 14 of 14

Thread: [RESOLVED] How to handle null value in SQLIE?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Resolved [RESOLVED] How to handle null value in SQLIE?

    Hello

    I can't figure out how to handle a null value in an integer field.
    Code:
    StrSql = "Select * from Recette_tbl  where Date_S =? and Tarif=?"
           Set RS = cnn.OpenRecordset(StrSql)
    With cnn.CreateSelectCommand(StrSql)
      .SetShortDate 1, DTP.Value
      .SetInt32 2, IsNull
         Set RS = .Execute
        End With
    This is sending error
    Code:
     .SetInt32 2, IsNull
    Thank you

  2. #2
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: How to handle null value in SQLIE?

    Quote Originally Posted by samer22 View Post
    Code:
    StrSql = "Select * from Recette_tbl  where Date_S =? and Tarif=?"
           Set RS = cnn.OpenRecordset(StrSql)
    With cnn.CreateSelectCommand(StrSql)
      .SetShortDate 1, DTP.Value
      .SetInt32 2, IsNull
         Set RS = .Execute
        End With
    This is sending error
    Code:
     .SetInt32 2, IsNull
    The cSelectCommand class has a .SetNull method for this.

    Code:
      .SetNull 2
    HTH Wolfgang

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: How to handle null value in SQLIE?

    thanks sir
    .SetNull 2 is not sending error but the code is not working when checking for RS.RecordCount or RS.EOF.

  4. #4
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: How to handle null value in SQLIE?

    Quote Originally Posted by samer22 View Post
    .SetNull 2 is not sending error but the code is not working when checking for RS.RecordCount or RS.EOF.
    Not sure what the actual problem is so you better show some more code along with a specific error message, or alternatively a precise description of what isn't working.

    Anyway please note that it's bedtime here now so my reply might take a little while.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: How to handle null value in SQLIE?

    Thank you sir again

    Code:
    StrSql = "Select * from Recette_tbl  where Date_S =? and Tarif=?"
           Set RS = cnn.OpenRecordset(StrSql)
    With cnn.CreateSelectCommand(StrSql)
      .SetShortDate 1, DTP.Value
      .SetNull 2
         Set RS = .Execute
        End With
    If RS.RecordCount > 0 then 
    Msgbox "exist"
    else
    Msgbox "Not exist"
    end if
    The msgbox is always "Not exist" though I can see the record with the criteria above is existing in the database .

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: How to handle null value in SQLIE?

    Something I heard a long time ago...don't rely on Recordcount.
    Instead, try using a While Loop to go through your recordset...something like this:

    Code:
    While Not Rs.EOF
          debug.print Rs!tarif
          rs.movenext
    Loop
    Does it print out any records?

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: How to handle null value in SQLIE?

    Does it print out any records?
    It doesn't print anything.
    thanks

  8. #8
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: How to handle null value in SQLIE?

    Quote Originally Posted by samer22 View Post
    The msgbox is always "Not exist" though I can see the record with the criteria above is existing in the database .
    Hmmm ... hard to say without knowing the data in the table. Probably the value of Tarif isn't NULL but an empty string ("")?

    There are a couple of SQL functions available in SQLite which help analyzing data, e.g. typeof() or length() may help here.

    Good luck,
    Wolfgang

  9. #9
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: How to handle null value in SQLIE?


  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to handle null value in SQLIE?

    Quote Originally Posted by Arnoutdv View Post
    In that case, why not use default-values in his table?

    In case of the OP:
    https://stackoverflow.com/questions/3620828/sqlite-select-where-empty
    SELECT * FROM your_table WHERE some_column IS NULL

    In his case, he would have to change his Query from 2 Paramaters to one Param
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to handle null value in SQLIE?

    The IfNull-Function is the right approach to this kind of problem
    (since it is able to dynamically convert a Null-Value of a given Field into "any normal Value" of a given Type)

    E.g. you could (for the Tarif-Field) decide, that its Null-Value-representation (in Integer-Space) should be -1 for example (because it will never contain negative values)...

    Then appropriate TestCode could look like this one here (based on an InMemory-DB):
    Code:
    Private Cnn As cConnection
    
    Private Sub Form_Load()
    Set Cnn = New_c.Connection(, DBCreateInMemory)
        Cnn.Execute "Create Table Recette_tbl(ID Integer Primary Key, Date_S ShortDate, Tarif Integer)"
        Cnn.Execute "Insert Into Recette_tbl(Date_S, Tarif) Values('2018-01-01', 10)"
        Cnn.Execute "Insert Into Recette_tbl(Date_S, Tarif) Values('2018-01-02', Null)"
    End Sub
    
    Private Sub Form_Click()
    Dim Rs As cRecordset
    
    Set Rs = GetRecetteRecordsFor(CDate("2018-01-01"), 10)
        MsgBox "Record does " & IIf(Rs.RecordCount, "exist", "not exist")
    
    Set Rs = GetRecetteRecordsFor(CDate("2018-01-02"), -1) 'to query for Null-Values in Tarif explicitely, simply pass an Integer of Value -1
        MsgBox "Record does " & IIf(Rs.RecordCount, "exist", "not exist")
    End Sub
    
    Private Function GetRecetteRecordsFor(ByVal SD As Date, ByVal Tarif As Long) As cRecordset
    Const SQL$ = "Select * from Recette_tbl  where Date_S =? and IfNull(Tarif, -1)=?"
      With Cnn.CreateSelectCommand(SQL)
          .SetShortDate 1, SD
          .SetInt32 2, Tarif
        Set GetRecetteRecordsFor = .Execute
      End With
    End Function
    HTH

    Olaf

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to handle null value in SQLIE?

    Quote Originally Posted by Schmidt View Post
    The IfNull-Function is the right approach to this kind of problem
    (since it is able to dynamically convert a Null-Value of a given Field into "any normal Value" of a given Type)

    E.g. you could (for the Tarif-Field) decide, that its Null-Value-representation (in Integer-Space) should be -1 for example (because it will never contain negative values)...

    HTH

    Olaf
    I disagree!
    He's the designer of the Database and the Frontend.
    If he knows during design, that that field will never have negative values, default it to -1 and be done with the IfNull-Checks.
    And suddenly the Queries don't cause so much trouble.....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to handle null value in SQLIE?

    Quote Originally Posted by Zvoni View Post
    I disagree!
    He's the designer of the Database and the Frontend.
    If he knows during design, that that field will never have negative values, default it to -1 and be done with the IfNull-Checks.
    And suddenly the Queries don't cause so much trouble.....
    I agree only partially...
    Because as the designer of the DB(Table) he might well decide, to keep Null-Values as real Null-Values in a certain Table
    (e.g. to ensure, that "normal Left- or Right-Joins" with other tables show the correct behaviour).

    In that case, he can (for certain queries) resort to the IfNull-trampoline -
    as demonstrated in the little function (GetRecetteRecordsFor) in my prior post.

    HTH

    Olaf

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: How to handle null value in SQLIE?

    Thank you all of you

    Code:
    SELECT * FROM your_table WHERE some_column IS NULL
    thanks Zvoni you code is working.

    Special thanks to Mr. Schmidt
    Your function is working great.

    Solved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width