none
OLEDB.4.0 & "The provider could not determine the double-value" when trying to query Average RRS feed

  • Question

  •  

    Hello,

     

    I am getting the above error when I try to query an Access 2003 database in a Windows.Forms .NET 2.0 App. Some results but no success when trying Google or searching in the forums.

     

    This is the query:

     (assume a view "MyView" has 2 columns "ResultGroup" (int) and "ResultValue" (double)):

    "SELECT ResultGroup, Min(ResultValue) as MinimumValue, Max(ResultValue) as MaximumValue, Avg(ResultValue) as AverageValue FROM MyView GROUP BY ResultGroup"

     

    This is the connection string:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mybatabase.mdb;User Id=admin;Password=;"

     

    This is how it looks like if I execute the query:

    OleDbConnection conn = new OleDbConnection(connectionString);

    OleDbCommand cmd = conn.CreateCommand();

    cmd.CommandText = <see sql above>

    OleDbDataReader rd = cmd.ExecuteReader();

    while (rd.Read())

    {

    double min = rd.GetDouble(1);

    double max = rd.GetDouble(2);

    double avg = rd.GetDouble(3); // <== in this row I get the exception

    }

     

    I also have tried rd.GetValue(3) and get the same error.

    The strange thing is that Min() and Max() just work fine (e.g. if I replace the Avg() with another Max() in the statement).

     

    This is the full error message:

    "An unhandled exception of type 'System.InvalidOperationException' occurred
    in system.data.dll

    Additional information: The provider could not determine the double-value.
    For example, the row was just created, the default for the Double column was
    not available, and the consumer had not yet set a new Double value."

     

    Does anybody know how to avoid or workaround this error or can give me some hint?

    Can anyone reproduce this?

    Many thanks in advance.

     

    BTW: This is my first post in here. Please let me know if formatting, style or forum ... is wrong.

    Tuesday, August 12, 2008 8:07 AM

Answers

  • I have found out the reason by myself.

     

    What I have found out:

    Some of the result values had the value "-1,#IND".

    This works for Min() and Max() but not for some other aggregate functions (I have tried Avg() and StDev()).

     

    Min() and Max() return NaN in .NET and this is what I expect for Avg(). For this reason this is a bug in the OLEDB4.0 provider in my opinion.

     

    How I got around it:

    I replaced all the "-1,#IND" values with NULL in the database.

     

    In the code where I execute the insert statement for the table containing the result values I implemented a check.

    If the value is NaN I assign DBNull for that value. Now everything works fine in my case. But one may be in trouble if he gets valid values instead of the expected NaN when querying the average.

     

    Thanks everybody for taking a look at it. Hope this helps anybody.

    Tuesday, August 12, 2008 10:02 AM