The provider could not determine the Decimal value

May 15, 2012 at 5:11 PM

I'm receiving this exception:

The provider could not determine the Decimal value. For example, the row was just created, the default for the Decimal column was not available, and the consumer had not yet set a new Decimal value.

Here's what the table structure looks like:

 

 

CREATE TABLE Invoice FREE ;
                      (invoice N(6), ;
                      date D, ;
                      company C(25), ;
                      address C(25), ;
                      city C(20), ;
                      state C(2), ;
                      zipcode C(10), ;
                      re1 C(25), ;
                      re2 C(25), ;
                      re3 C(25), ;
                      comment C(50), ;
                      lendcode C(4), ;
                      invtotal N(8, 2), ;
                      ref C(10), ;
                      attn C(30))

INDEX ON invoice TAG invoice
INDEX ON date TAG date
INDEX ON lender TAG lendcode                      

If I attempt to run a basic query like this (where the variable invoice is an int):

var inv= Context.Invoices.Where(i => i.Invoice1 == invoice).FirstOrDefault();

I receive the above exception. I tried casting invoice to a decimal, same results.

I've attempted to rewrite it with the raw SQL (and doing a CAST(Invoice AS N(6)) AS Invoice1 and similar for Invtotal), then calling ExecuteStoreQuery<Invoice>() with the same results. 

I went into the EDMX model and modified Invoice1 to a Int32 instead of a decimal - same error. I explicitly set the Default value in the model to 0 for both fields - same error.

However, if I create a new class with properties for each field, then call ExecuteStoreQuery with this type, it works fine.

 

    public class SimpleInvoice
    {
        public int Invoice1 { get; set; }
        public string Company { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zipcode { get; set; }
        public string Re1 { get; set; }
        public string Re2 { get; set; }
        public string Re3 { get; set; }
        public string Comment { get; set; }
        public string Lendcode { get; set; }
        public string Ref { get; set; }
        public string Attn { get; set; }
        public decimal Invtotal { get; set; }
    }

// Call with this:
            string sql = @"SELECT i.Invoice AS Invoice1,
                                  i.Date,
                                  i.Company,
                                  i.Address,
                                  i.City,
                                  i.State,
                                  i.Zipcode,
                                  i.re1,
                                  i.re2,
                                  i.re3,
                                  i.Comment,
                                  i.Lendcode,                                  
                                  i.Ref,
                                  i.Attn        
                             FROM Invoice i 
                            WHERE i.invoice = ?";

            var inv = Context.ExecuteStoreQuery<SimpleInvoice>(sql, invoice).FirstOrDefault();

Coordinator
May 15, 2012 at 5:44 PM

The exception that you have identified is apparently a known issue with the VfpOleDb provider and seems to have a simple work around.  I'll work on changing the generated sql to include this work around.

 

btw... The SimpleInvoice example probably worked because you didn't include the Invtotal field in the query.

Coordinator
May 16, 2012 at 10:26 AM

I posted a new build with a fix for this issue.

Jul 26, 2012 at 7:42 PM

I ran across this issue again and downloaded the new version. Unfortunately this still fails. I'll send you a sample project so you can see it.

Coordinator
Jul 27, 2012 at 1:10 PM

In the sample project that you provided you are writing the sql statement yourself.   But the fix that I made was a change to the generated sql statement. You will have to manually adjust your sql statement by listing out all the fields and wrapping your numeric fields with as cast statement that increased the field width +1.

Example: CAST(gmargin AS N(6, 2)) AS gmargin

(where gmarin was defined in the table as N(5, 2))

 

 

Jul 27, 2012 at 4:18 PM

Got it. I was thinking the modification happened during the binding between the returned results and the class that was being populated. Still seems weird that we have to do anything special with this considering a decimal type doesn't have the same size constraints as a decimal type in the VFP table.