reader.HasRows Returns FALSE Every Time

Question

I'm new with C# and I'm writing a desktop application to read an MS-Access Invoice table so I can perform further processing on it. But I can't get my "reader.HasRows" statement to return rows from my SELECT statement even though there are rows in the table. Every time I run the application I get the "No Invoice records found" MessageBox displayed.

I have researched and read several other posts on this forum of similar problems, but none seem to address my particular issue. I've also searched the Internet for a solution with no results. Can someone help me spot what I'm doing wrong?

    private void AddContactsAcconutNumberToInvoices()
    {
        //Use a variable to hold the SQL statement.
        string inputString = "SELECT Invoices.[Job_Name], * FROM Invoices " +
            "WHERE Invoices.[Account_Number] = Null";

        try
        {
            //Create an OleDbCommand object and pass in the SQL statement and OleDbConnection object
            OleDbCommand cmd = new OleDbCommand(inputString, conn);

            //Send the CommandText to the connection, and then build an OleDbDataReader.
            OleDbDataReader reader = cmd.ExecuteReader();

            // Read through the database and test the integrity of the data
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    var jobname = reader.GetString(0);
                    var contactsQuery = "SELECT Account_Number FROM CONTACTS WHERE Contacts.Full_Name = " + jobname;
                    MessageBox.Show("Contacts query contatins: " + contactsQuery);
                }
            }
            else { MessageBox.Show("No Invoice records found"); }
        }
        catch (Exception ex)
        {
            error_message = ex.Message;
            MessageBox.Show(error_message);
        }
    }

Unrelated tips: the command and DataReader are both IDisposable so each should be in a `using` block. The same is true of the connection, so you may want to consider a design where that is not a field. — Richardissimo, Nov 26 '18 at 05:50

score 6 · Accepted Answer · answered Nov 26 '18 at 00:56

The problem is your query, particularly the = Null. You need to use Is Null:

string inputString = "SELECT Invoices.[Job_Name], * FROM Invoices " +
        "WHERE Invoices.[Account_Number] Is Null";

This is true not just for Access, but for SQL (the language) in general.

More info here specifically for Access: https://support.office.com/en-us/article/examples-of-query-criteria-3197228c-8684-4552-ac03-aba746fb29d8#bm1

reader.HasRows Returns FALSE Every Time

1 Answers1