ExecuteScalar always returns 0

Question

I'm not sure why this is happening. I've seen the same issue online with little help out there to correct it.

When i run my query inside Access i get different values ranging from 0 - 10 but for some reason, it won't return that same value inside my code.

static int OrdersPerHour(string User)
    {
        int? OrdersPerHour = 0;
        OleDbConnection conn = new OleDbConnection(strAccessConn);
        DateTime curTime = DateTime.Now;


        try
        {

            string query = "SELECT COUNT(ControlNumber) FROM Log WHERE DateChanged > #" + curTime.AddHours(-1) + "# AND User = '" + User + "' AND Log.EndStatus in ('Needs Review', 'Check Search', 'Vision Delivery', 'CA Review', '1TSI To Be Delivered');";
            OleDbCommand dbcommand = new OleDbCommand(query, conn);
            dbcommand.Connection.Open();
            dbcommand.CommandType = CommandType.Text;
            dbcommand.CommandText = query;
            OrdersPerHour = (int?)dbcommand.ExecuteScalar();


                      }
        catch (OleDbException ex)
        {

        }
        finally
        {
            conn.Close();
        }
        return OrdersPerHour.Value;

    }

Kktp

You might want to edit to improve the formatting of your markdown. Also did you mean to have some of that code inside the try{} ? – Loram

Please use parameters... SQL injection is very real – Woodman

1.) Please use Parameterized Queries. 2.) Why do you have an empty Try Catch Finally? Is that just to ensure the connection is closed? You should have your OleDbConnection in a using statement. – Straus

Answer 1

Do not use string concatenation and the Access syntax to build your sql commands.
Use a simple parameterized query like this

string query = "SELECT COUNT(ControlNumber) FROM Log " + 
                "WHERE DateChanged > ? AND [User] = ? AND " + 
                "Log.EndStatus in ('Needs Review', 'Check Search', 'Vision Delivery'," + 
                "'CA Review', '1TSI To Be Delivered');";

  OleDbCommand dbcommand = new OleDbCommand(query, conn);
  dbcommand.Parameters.AddWithValue("@p1", curTime.AddHours(-1));
  dbcommand.Parameters.AddWithValue("@p2", User);
  dbcommand.Connection.Open();
  dbcommand.CommandType = CommandType.Text;
  OrdersPerHour = (int)dbcommand.ExecuteScalar();

In this way the burden to correctly interpret your value is passed to the Framework code that could format dates, decimals and strings according to your database requirements. By the way this will also prevent Sql Injection

Also, the word USER is a reserved keyword in Access SQL and thus you need to encapsulate it with square brackets

Rustproof

In C# you can use the @ symbol to make a query span multiple lines without the need for concatenation. – Sidestep

While you are right on the usage of the @ symbol, you should also mention the fact that this use add a considerable amount of white spaces to the string built (depending on how you format the lines). Try to print the length of this string against the same with the @ symbol. Also the string concatenation of constant text like this is resolved at compile time building a statically defined unique string without expensive string concats (Looking at IL code confirms this) – Rustproof

I'm not getting a "Data type mismatch in criteria expression." error once it runs my ExecuteScalar() – Kktp

Check if DateChanged column is effectively a DateTime column, the User column is a text datatype – Rustproof

Answer 2

First and most important: Use Parametrized Queries!

Regarding your problem, I suggest you to debug the code:

Get the Commandtext of your "OleDbCommand dbcommand" and manually query to see if you get the same result.

Also, you should put your code within the try catch block, else it does not make sense at all.

Inelegant