Taffs Taffs - 17 days ago 6
Vb.net Question

Error if DBnull, ms-access

I'm trying to SUM columns in access database, and it somewhat works.

column5
and
column6
are dates. Lets say I have records with dates 15/11/2016, and another one with 19/11/2016. If I select 'OdDate' as 15/11/2016 and 'DoDate' as 19/11/2016 it works, it sums the records. The problems come when I select from 15/11/2016 to lets say... 20/11/2016, in this case, scalar returns records from 19/11/2016, it ignores the records in 15/11/2016. And If I select few days further, then it throws error dbnull.

I know why it throws dbnull error, what I want to know is, why does it ignore records with 15/11/2016 when I select from 15/11/2016 to 20/11/2016, why doesn't it throw dbnull error, there are no records with 20/11/2016 date. Instead it only returns the records in 19/11/2016.

Dim provider As String
Dim dataFile As String = My.Application.Info.DirectoryPath
provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
dataFile = ("Database\Baza-Original.accdb")

Dim SqlQry1 As String = "SELECT SUM(Column2 + Column3 + Column4) FROM [Table] WHERE Column1 = @1 BETWEEN Column5 = @2 AND Column6 = @3"

Using myconnection As New OleDbConnection(provider & dataFile)
myconnection.Open()

Dim cmd1 As New OleDbCommand(SqlQry1, myconnection)
cmd1.Parameters.AddWithValue("@1", CB.SelectedItem)
cmd1.Parameters.AddWithValue("@2", OdDate.Value.Date)
cmd1.Parameters.AddWithValue("@3", DoDate.Value.Date)

If cmd1.ExecuteScalar() Then

Dim field As Int32

field = cmd1.ExecuteScalar()
LBL.text = field

myconnection.Close()
MsgBox("msg1. ", MsgBoxStyle.Information, "successful")
Else
MsgBox("msg2. ", MsgBoxStyle.Critical, "Unsuccessful!")
Return
End If

End Using

Answer

Although the BETWEEN operator looks temptingly simple to use, you have to be careful with it. On top of that, comparing dates is done strictly mathematically as opposed to what we might regard as the way to do it in human.

Also, AddWithValue can give unexpected results and should never be used because it is not worth the effort to make code that lets it work - Can we stop using AddWithValue() already?

SELECT SUM(Column2 + Column3 + Column4)
FROM [Table]
WHERE Column1 = @1
AND Column5 >= @2 AND Column6 < @3

and in the code to set the parameters:

cmd1.Parameters.Add(New OleDbParameter With {.ParameterName = "@3", .OleDbType = OleDbType.Date, .Value = OdDate.Value.Date.AddDays(1)})