ExecuteScalar始终返回0 [英] 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.

当我在Access中运行查询时，我会得到0到10之间的不同值，但是由于某种原因，它不会在我的代码中返回相同的值.

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;

    }

推荐答案

请勿使用字符串串联和Access语法来构建您的sql命令.
使用像这样的简单参数化查询

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();

通过这种方式，正确解释您的值的负担被传递给Framework代码，该代码可以根据您的数据库要求设置日期，小数和字符串的格式.顺便说一句，这也可以防止Sql注入

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

此外，USER单词是Access SQL中的保留关键字，因此您需要使用方括号将其封装

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

这篇关于ExecuteScalar始终返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！