-
Jun 4th, 2018, 03:24 PM
#1
Thread Starter
Fanatic Member
[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
-
Jun 4th, 2018, 05:32 PM
#2
Re: How to handle null value in SQLIE?
Originally Posted by samer22
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.
HTH Wolfgang
-
Jun 4th, 2018, 05:51 PM
#3
Thread Starter
Fanatic Member
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.
-
Jun 4th, 2018, 06:27 PM
#4
Re: How to handle null value in SQLIE?
Originally Posted by samer22
.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.
-
Jun 4th, 2018, 08:05 PM
#5
Thread Starter
Fanatic Member
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 .
-
Jun 4th, 2018, 08:38 PM
#6
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?
-
Jun 4th, 2018, 09:54 PM
#7
Thread Starter
Fanatic Member
Re: How to handle null value in SQLIE?
Does it print out any records?
It doesn't print anything.
thanks
-
Jun 5th, 2018, 03:28 AM
#8
Re: How to handle null value in SQLIE?
Originally Posted by samer22
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
-
Jun 5th, 2018, 04:00 AM
#9
Re: How to handle null value in SQLIE?
-
Jun 5th, 2018, 04:59 AM
#10
Re: How to handle null value in SQLIE?
Originally Posted by Arnoutdv
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
-
Jun 5th, 2018, 09:43 AM
#11
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
-
Jun 5th, 2018, 11:01 AM
#12
Re: How to handle null value in SQLIE?
Originally Posted by Schmidt
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
-
Jun 5th, 2018, 01:20 PM
#13
Re: How to handle null value in SQLIE?
Originally Posted by Zvoni
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
-
Jun 5th, 2018, 02:34 PM
#14
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|