Error if DBnull, ms-access

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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)})

Error if DBnull, ms-access

Sign up