OleDbCommand.ExecuteReader 抛出异常

Question

我的 C# .NET 3.5 应用程序在执行 OleDbCommand.ExecuteReader 时引发异常。该应用程序对 Access 2007 数据库进行 5 次类似的查询。其中三个查询执行没有任何问题；另外两个导致抛出完全相同的异常。

这是一个传递的查询：

String query =
    "SELECT " +
        "bas.[BAS BACnet Object Type/Instance], " + // OBJECT_IDENTIFIER_ATTRIBUTE/ITEM_REFERENCE_ATTRIBUTE
        "bas.[BAS BACnet Object Name], " +          // USER_NAME_ATTRIBUTE
        "bas.[BAS Point List Description], " +      // DESCRIPTION_ATTRIBUTE
        "bas.[BAS Monitor Only], " +                // MONITOR_ONLY_ATTRIBUTE
        "ref.[ENUM_H], " +                          // PROPERTY_REFERENCE_VALUE_ATTRIBUTE
        "yk.[CCC Max Value (eng units)], " +        // MAX_PRESENT_VALUE_ATTRIBUTE
        "yk.[CCC Min Value (eng units)], " +        // MIN_PRESENT_VALUE_ATTRIBUTE
        "yk.[CCC Enum/Data Set], " +                // UNITS_ATTRIBUTE
        "ore.[ORE COV Increment], " +               // COV_INCREMENT_ATTRIBUTE
        "ore.[ORE Display Precision] " +            // DISPLAY_PRECISION_ATTRIBUTE
    "FROM (([OV2 BAS] AS bas " +
    "INNER JOIN [OV2 ORE] AS ore ON bas.[Ref ID] = ore.[Ref ID]) " +
    "INNER JOIN [OV2 RefID] AS ref ON bas.[Ref ID] = ref.[Ref ID]) " +
    "INNER JOIN [YK CAPP] AS yk ON bas.[Ref ID] = yk.[Ref ID] " +
    "WHERE bas.[BAS BACnet Object Type/Instance] LIKE 'AV%';";

this.RunQuery(query, MappingTable.AV_QUERY_IP_FIELDS, this.IPAnalogValuesList);

这是一个抛出异常的查询：

String query =
    "SELECT " +
        "bas.[BAS BACnet Object Type/Instance], " + // OBJECT_IDENTIFIER_ATTRIBUTE/ITEM_REFERENCE_ATTRIBUTE
        "bas.[BAS BACnet Object Name], " +          // USER_NAME_ATTRIBUTE
        "bas.[BAS Point List Description], " +      // DESCRIPTION_ATTRIBUTE
        "bas.[BAS Monitor Only], " +                // MONITOR_ONLY_ATTRIBUTE
        "ref.[ENUM_H], " +                          // PROPERTY_REFERENCE_VALUE_ATTRIBUTE
        "ses.[ENUM_H], " +                          // STATES_TEXT_ATTRIBUTE
        "ore.[ORE States] " +                       // ACTIVE_TEXT_ATTRIBUTE/INACTIVE_TEXT_ATTRIBUTE
    "FROM (([OV2 BAS] AS bas " +
    "INNER JOIN [OV2 RefID] AS ref ON bas.[Ref ID] = ref.[Ref ID]) " +
    "INNER JOIN [OV2 ORE] AS ore ON bas.[Ref ID] = ore.[Ref ID]) " +
    "INNER JOIN [StatesEnumSet] AS ses ON ore.[ORE States] = ses.[ID] " +
    "WHERE bas.[BAS BACnet Object Type/Instance] LIKE 'BV%';";

this.RunQuery(query, MappingTable.BV_QUERY_FIELDS, this.BinaryValuesList);

以下是所有查询的执行方式：

private void RunQuery(String query, Int32 fieldCount, Object target)
{
    OleDbCommand cmd = null;
    OleDbDataReader reader = null;

    try
    {
        OleDbConnectionStringBuilder connection = new OleDbConnectionStringBuilder();
        connection.Provider = "Microsoft.ACE.OLEDB.12.0";
        connection.DataSource = XML_Generator.Program.MappingTableFilename;
        connection.PersistSecurityInfo = false;

        this.DbConnection = new OleDbConnection(connection.ToString());
        this.DbConnection.Open();

        using (cmd = new OleDbCommand(query, this.DbConnection))
        {
            cmd.Connection = this.DbConnection;
            cmd.CommandText = query;
            cmd.CommandType = System.Data.CommandType.Text;
            reader = cmd.ExecuteReader(); // <== Exception is thrown here.

            while (reader.Read())
            {
                . . .
            }
        }

        if (this.DbConnection != null)
        {
            this.DbConnection.Close();
            this.DbConnection = null;
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
}

这是异常：

System.Data.OleDb.OleDbException：没有为一个或多个必需参数指定值 之三。 在 System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling（OleDbHResul 小时） 在 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult（tagDBPARA MS dbParams、对象&执行结果) 在 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&executeResult) 在 System.Data.OleDb.OleDbCommand.ExecuteCommand（CommandBehavior 行为，Ob 项目&执行结果) 在 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal（CommandBehavior 行为 ior，字符串方法） 在 System.Data.OleDb.OleDbCommand.ExecuteReader（CommandBehavior 行为） 在 System.Data.OleDb.OleDbCommand.ExecuteReader() 在 XML_Generator.MappingTable.RunQuery（字符串查询，Int32 fieldCount，对象 目标）位于 C:\ccmdb\prep\ov2_shared_cec_v1.0\ov2_shared_cec\Private\Tools\XML_G enerator\XML_Generator\MappingTable.cs:第 312 行

感谢任何帮助。谢谢。

Answer 1

以下是可以声明 OleDbConnection、Command 和 DataReader 对象的示例
如果您选择将其设为静态变量，则连接字符串的外观示例

<add key="strAccessConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\castonmr\Documents\"Your AccessDB Name.mdb";Mode='Share Exclusive';Jet OLEDB:Database Password="your password";"/>    


try
{
    OleDbConnection oleconn = null;
    OleDbDataReader reader = null;
    oleconn = new OleDbConnection(strAccessConnectionString);
    oleconn.Open();
    using (OleDbCommand cmd = new OleDbCommand())
    {
       cmd.Connection = oleconn;
       cmd.CommandText = query;
       cmd.CommandType = CommandType.Text;
       reader = cmd.ExecuteReader();
    }
}//try
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Answer 2

有人重命名了数据库中的字段之一。 -_-

Answer 3

此错误可能是因为您的 mdb 没有运行查询所需的所有列/对象，您可以仔细检查 mdb 文件并查看查询是否在那里正确运行