Select using -1 in tinyint field on MYSQL

Question

I have written the following code to filter the people who wants a newsletter via post or e-mail:

Sub loadoverviewnewsletter()
        Dim sql As String
        Dim sql1 As String
    If newslettermail = True Then
        sql = "SELECT * FROM Donateurs WHERE 'Newsletter per e-mail' =-1"
        sql1 = "SELECT * FROM Donateurs WHERE 'Newsletter per e-mail' =-1"
    ElseIf newsletterpost = True Then
        sql = "SELECT * FROM Donateurs WHERE 'Newsletter per post' =-1"
        sql1 = "SELECT * FROM Donateurs WHERE 'Newsletter per post' =-1"
    ElseIf newsletternew = True Then
        sql = "SELECT * FROM Donateurs WHERE 'Newsletter per post' =0 AND 'Nieuwsbrief per e-mail' =0"
        sql1 = "SELECT * FROM Donateurs WHERE 'Newsletter per post' =0 AND 'Nieuwsbrief per e-mail' =0"
    Else
        sql = "SELECT * FROM Donateurs"
        sql1 = "SELECT * FROM Donateurs"
    End If

    Dim adapter As New MySqlClient.MySqlDataAdapter(sql, modConnectorHandler.ServerString)
    Dim dt As New DataTable("newsletteroverview")
    adapter.Fill(dt)
    ShowDonateurs.datagrid.DataSource = dt

    ShowDonateurs.ShowDialog()
    Dim adapter1 As New MySqlClient.MySqlDataAdapter(sql1, modConnectorHandler.ServerString)
    Dim cmd1 As New MySqlClient.MySqlCommand(sql1, modConnectorHandler.mysqlconnection)
    modConnectorHandler.mysqlconnection.Open()
    Dim myreader As MySqlClient.MySqlDataReader = cmd1.ExecuteReader
    myreader.Read()
    modConnectorHandler.mysqlconnection.Close()
    OverviewNewsLetter.Close()

Unfortunately, it returns an empty datagrid, while when I select the newsletternew option, it shows the complete table. The fields "Newsletter per e-mail" and "Newsletter per post" are tinyint fields which can have the values -1 (means checked in application) and 0 (meand unchecked).

I really don't understand why it doesn't work.. I hope anyone can help.

score 1 · Accepted Answer · answered May 20 '13 at 15:04

You need to use the backticks instead of the single quotes for your column names. Consider this WHERE condition:

WHERE 'Newsletter per e-mail' =-1

It's comparing the string constant Newsletter per e-mail to -1. The result of the comparison is always false. Try this instead:

WHERE `Newsletter per e-mail` = -1

Now it's referring to a column name and not a string constant.

Finally, avoid column names that need backticks whenever possible (which is almost all the time). As you can see, they tend to lead to trouble.

It fixed the problem, thanks!. For a starter like me, it is such i tiny detail, I didn't even noticed that there was a difference between the two. Thanks for your help! — Jelle Wouters, May 20 '13 at 15:59

Select using -1 in tinyint field on MYSQL

1 Answers1