OLEDB Sum error: Data type mismatch in criteria expression. VB.NET

Please help! When I try to sum one column,with two criteria there's an error:

Data type mismatch in criteria expression.

This is the sql code:

 sql1 = "SELECT SUM(quantityofproduct) AS Expr1 FROM (SP001) where year = "2016" AND month = 1"

There are 4 columns :

1.ID

2.quantityofproduct

3.Year

4.month

SP001 is the name of the talbe.

I tried it with date column format and &datetimepicker1.value/text& for the criteria but it didn't work.

Thanks for your help.

Solution

if type of fields is varchar(30), you can use quotes '' like this :

sql1 = "SELECT SUM(quantityofproduct) AS Expr1 FROM (SP001) where year = '2016' AND month = '1'"

But, I advoise you change the type of Column Year and Month to Integer and also use Parameter to avoid SQL injection like this :

Dim query As String = "SELECT SUM(quantityofproduct) AS Expr1 FROM (SP001) " &
                        "where year = @year And month = @month"
Dim cmd As New OleDbCommand(query, connection)
cmd.Parameters.AddWithValue("@year", YearValue)
cmd.Parameters.AddWithValue("@month", MonthValue)
Dim sumQuantity As Int32 = Convert.ToInt32(cmd.ExecuteScalar())

YearValue and MonthValue are the values of year and month searched.