ExecuteScalar always returns 0

问题:

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;

    }

回答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

回答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.