OleDbCommand.ExecuteReader Throwing Exception

Question

My C# .NET 3.5 application is throwing an unusual exception when executing OleDbCommand.ExecuteReader. The application makes 5 similar queries of an Access 2007 database. Three of the queries execute without any problems; the other 2 cause exactly the same exception to be thrown.

This is a query that passes:

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

This is a query that throws throws an exception:

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

Here is how all of the queries are executed:

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

This is the exception:

System.Data.OleDb.OleDbException: No value given for one or more required parame ters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResul t hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARA MS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Ob ject& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav ior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at XML_Generator.MappingTable.RunQuery(String query, Int32 fieldCount, Object target) in C:\ccmdb\prep\ov2_shared_cec_v1.0\ov2_shared_cec\Private\Tools\XML_G enerator\XML_Generator\MappingTable.cs:line 312

Any help is appreciated. Thanks.

@DJKRAZE: It's declared locally in the function. I've updated the OP to clarify. — Jim Fell
– Jim Fell, Commented Jan 5, 2012 at 16:17
ok I didn't see that..ok let me look at your code again there is a better way to create the OleDbCommand and connection objects give me a second also can you post what the full connection string is.. the way it's declared in your code it's hard to tell if there may or may not be an issue there.. are you using a .config file at all.? I would recommend placing the connection string in the config file — MethodMan
– MethodMan, Commented Jan 5, 2012 at 16:18

MethodMan · Accepted Answer · 2012-01-05 16:46:10Z

1

here is an example of what a OleDbConnection, Command and DataReader object can be declared example of how the Connection String should look if you choose to make this a static variable

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

edited Jan 5, 2012 at 16:46

answered Jan 5, 2012 at 16:12

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jim Fell Over a year ago

Should I close the database, after the using statement completes?

MethodMan Over a year ago

depends on what you want to do.. the using will Dispose of the underlying connection and return it to the connection pool.. it depends on what you truly need to do.. repeated opening and closing of connections could be costly in regards to round trips

Jim Fell Over a year ago

Thanks. Does reader.Read() need to be inside the using block as well?

Jim Fell Over a year ago

From your example, it looks like cmd.Connection is set to null. Is that correct? Should it be set to oleconnStkLink?

MethodMan Over a year ago

no necessarily but if you plan on doing any reading from that within a while loop why not just keep it within the using.. it will not hurt it since the null assignment of the reader was declared outside the using() this makes it still accessible I normally do my coding within the using

|

Jim Fell · Accepted Answer · 2012-01-09 18:06:24Z

1

Someone renamed one of the fields in the database. -_-

answered Jan 9, 2012 at 18:06

Jim Fell

14.4k41 gold badges135 silver badges212 bronze badges

Comments

Brijesh Mishra · Accepted Answer · 2012-01-05 16:46:30Z

0

this error could be because your mdb doesnot have all the columns/object required to run query, you may double check mdb file and see if the query run there properly

answered Jan 5, 2012 at 16:46

Brijesh Mishra

2,7581 gold badge21 silver badges36 bronze badges

2 Comments

Jim Fell Over a year ago

I have double-checked the database. All this started happening when I updated the queries to reflect a change in the database (a new column was added to one of the tables). This new field (BAS Monitor Only) is referenced the same way in all of the queries.

Brijesh Mishra Over a year ago

maybe do some trial and error, change error query to select bas.*, * to analyse the column return,

Collectives™ on Stack Overflow

OleDbCommand.ExecuteReader Throwing Exception

3 Answers 3

7 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related