joe joe - 4 months ago 29
Vb.net Question

SQL Syntax error, expression of non-boolean type

This code is supposed to create a graph of revenue from money made through sales tickets at an event.

The code only executes up to

da.Fill(ds)
when it returns the error, which can be seen at the end of the code.

Does anybody know why

Private Sub frmRevenue_Load(sender As Object, e As EventArgs) Handles Me.Load

frmMDI.addFormToCMS()

Dim dt As DataTable
dt = New DataTable
dt.Columns.Add("Fee")

Dim sales As Integer = 0

Dim gridtable As New DataTable
gridtable.Columns.Add("Month")
gridtable.Columns.Add("Total")
gridtable.Columns.Add("#")

For i = 1 To 12
sql = "SELECT Fee FROM tblTickets WHERE MONTH(DatePurchased) = " & i & " AND (YEAR(DatePurchased) = " & Today.Year & " OR " & Year(Today.AddYears(1)) & ") AND (Status = 'SOLD' OR RESERVED" _
& " = 'AWAITING CONFIRMATION' OR Status = 'AVAILABLE' OR Status = 'AWAITING PAYMENT');"

da = New OleDb.OleDbDataAdapter(sql, con)
ds = New DataSet
da.Fill(ds)

Dim dr As DataRow

For Each dr In ds.Tables(0).Rows
monthly(i) = monthly(i) + 1
contracts = sales + 1
total(i) = total(i) + dr.Item("Fee")
yearlytotal = yearlytotal + dr.Item("Fee")
Next
Next

For i = 1 To 12
Dim month As String
Select Case i
Case 1
month = "Jan"
Case 2
month = "Feb"
Case 3
month = "Mar"
Case 4
month = "Apr"
Case 5
month = "May"
Case 6
month = "Jun"
Case 7
month = "Jul"
Case 8
month = "Aug"
Case 9
month = "Sep"
Case 10
month = "Oct"
Case 11
month = "Nov"
Case 12
month = "Dec"
Case Else
month = "ERR"
End Select

gridtable.Rows.Add(month, FormatCurrency(total(i)), monthly(i))
Next

ugTickets.DataSource = gridtable
ugTickets.DisplayLayout.Bands(0).Columns("Month").Width = 35
ugTickets.DisplayLayout.Bands(0).Columns("#").Width = 20
ugTickets.DisplayLayout.Override.AllowUpdate = DefaultableBoolean.False
txtAnnual.ReadOnly = True
txtAnnual.BackColor = Color.White
txtAnnualContracts.ReadOnly = True
txtAnnualContracts.BackColor = Color.White
chRevenue.Titles("chTitle").Text = "Predicted revenue for " & Today.Year & " - " & Year(Today.AddYears(1))
txtAnnual.Text = FormatCurrency(yearlytotal, 2)
txtAnnualContracts.Text = contracts
chRevenue.Series("Series1").Name = "Revenue"

For i = 1 To 12
chRevenue.Series("Revenue").Points.AddY(total(i))
Next

Try
chRevenue.BackColor = Color.Transparent
chRevenue.Legends("Revenue").BackColor = Color.Transparent
chRevenue.Series("Revenue").ChartArea = "ChartArea1"
chRevenue.Series("Revenue").Color = Color.SkyBlue
chRevenue.Series("Revenue").ToolTip = FormatCurrency("#VALY", 2)
Catch
End Try

End Sub



An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Answer

The problem is with this bit of the SQL:

(YEAR(DatePurchased) = " & Today.Year & " OR " & Year(Today.AddYears(1)) & ")

It should probably be

(YEAR(DatePurchased) = " & Today.Year & " OR YEAR(DatePurchased) = " & Year(Today.AddYears(1)) & ")

The OR in SQL (and in most other languages) needs to have two independently valid conditions on each side. The left hand side currently looks like this:

YEAR(DatePurchased) = 2016

...which is fine. But the right looks like this:

2017

...which isn't a valid boolean.

When you get an error like this on the da.Fill() line (ie. the line that's actually running the SQL in the database), the easiest way to debug it is to print out the value of the "sql" variable.

Often, you can just look at the SQL it's generated and the problem will be obvious. Other times you have to copy it and run it directly against your database to see what the problem is.