SQL Query returns wrong boolean result

Question

Good day guys, I am developing an windows form app using VB.Net. 1 part of my app will accumulate data from a csv file into a local database. I had written a function where I can check whether the particular entry exist in the local database before I do further processing. Here is the function:

Private Function CheckTableGotEntries(ByVal tableName As String, ByVal year As String, ByVal week As String) As Boolean
        Dim stringInsert As String
        Dim result As Boolean = False
        Dim myReader As SqlCeDataReader
        stringInsert = "SELECT CASE WHEN EXISTS (" & _
                        "SELECT *" & _
                        "FROM " & tableName & _
                        " WHERE year_column = " & "'" & year & "'" & " AND week_column = " & "'" & week & "'" & _
                        ")" & _
                        "THEN CAST(1 AS BIT)" & _
                        "ELSE CAST(0 AS BIT) END"
        SQLCon.Open()
        SQLCmd = New SqlCeCommand(stringInsert, SQLCon)
        myReader = SQLCmd.ExecuteReader()
        result = myReader.Read()    'SUPPOSE TO GET FALSE HERE
        SQLCon.Close()
        Return result
    End Function

While running with debugger, I found out that this function is returning a "TRUE" at places where it suppose to return "FALSE". In the initial run itself, when the database table is still empty and the first row of the csv is being processed, my function is saying that the entry is already exist and returns a true.

I suspect something is wrong with my query. Anyone can suggest something?

Scott Hannen · Accepted Answer · 2018-01-18T00:57:45.593

myReader.Read() doesn't return true or false from the SQL query. It returns true if it advances to the next record, or false if there are no records.

We typically call .Read() once to advance to the first record (if there is one) and again and again as long as it returns true, so that we read all of the records in the result set.

To actually read the result from the SqlDataReader you would call .Read() once to advance to the first (only) record, and then call

result = myReader.GetBoolean(0)

to return the value of the first column returned as a boolean.

Or, since the query only returns a single value (one row, one column) you could skip the reader and do

result =  DirectCast(SQLCmd.ExecuteScalar(), Boolean)

You can use ExecuteScalar if there are more rows and columns, but the point is that it only returns the first row and column.

SQL Query returns wrong boolean result

1 Answers1