I'm trying to SUM columns in access database, and it somewhat works.
column5
column6
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
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)})