locked
Strange Visual FoxPro provider error RRS feed

  • السؤال

  • Hi,

    I'm getting a really strange error. I use the Visual FoxPro OleDb provider to access a FoxPro .dbc database. I have a table where I have several Numeric fields that're giving me problems.

    When my application tries to access the .dbf table, most of the time it runs OK, but on certain rows the provider cannot access the data in them and throws an InvalidOperationException. For example, I have a field called BMI, type: Numeric, width: 4, decimal: 1, Index: none, NULL: false.

    When I try to open the table using the provider (for example, through the Server Explorer in Visual Studio), I get this in the BMI field in certain rows: "<Unable to read data>".

    When my program tries to access these certain rows/fields, it throws an InvalidOperationException with the message: "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."

    When I open the table in FoxPro (7.0) the field contains a normal value of 115. It seems that this error pops up whenever there's a whole number in the field. Rows where this field contains a number with a fraction (for example, 31.5) read fine. What gives? Am I accessing the table incorrectly?

    As mentioned, I'm using the Visual FoxPro provider from the Microsoft FoxPro downloads page with the default settings, and accessing the table through the .dbc file. Any suggestions welcome.

    09/جمادى الأولى/1427 08:34 ص

الإجابات

  • >> When I open the table in FoxPro (7.0) the field contains a normal value of 115. It seems that this error pops up whenever there's a whole number in the field. Rows where this field contains a number with a fraction (for example, 31.5) read fine. What gives? Am I accessing the table incorrectly?

    Just a guess here, but I think the problem is that you have the field defined as Numeric (4, 1)

    However, in VFP the decimal point is counted as part of the definition so that the actual maximum value that you can insert into a field with this specification is "99.9".

    VFP itself is 'smart' enough to realize that if you try to add "115" to this field that it should drop the decimal point and accept the value, but an external program, reading the field specification as "99.9" and getting a value of "999." is likely to see it as invalid. The solution is to always increase the size of the non-integer fields by 1 when you need the decimals.

    In this case, either fix the application so that the maximum value the user can enter is 99.9 or alter the field definition to allow 999.9

     

    09/جمادى الأولى/1427 11:02 ص

جميع الردود

  • >> When I open the table in FoxPro (7.0) the field contains a normal value of 115. It seems that this error pops up whenever there's a whole number in the field. Rows where this field contains a number with a fraction (for example, 31.5) read fine. What gives? Am I accessing the table incorrectly?

    Just a guess here, but I think the problem is that you have the field defined as Numeric (4, 1)

    However, in VFP the decimal point is counted as part of the definition so that the actual maximum value that you can insert into a field with this specification is "99.9".

    VFP itself is 'smart' enough to realize that if you try to add "115" to this field that it should drop the decimal point and accept the value, but an external program, reading the field specification as "99.9" and getting a value of "999." is likely to see it as invalid. The solution is to always increase the size of the non-integer fields by 1 when you need the decimals.

    In this case, either fix the application so that the maximum value the user can enter is 99.9 or alter the field definition to allow 999.9

     

    09/جمادى الأولى/1427 11:02 ص
  • That's exactly what I came up with my company's FoxPro guru. So we wrote ALTER TABLE scripts for every column with this bug. Dumb. Anyway, thanks for your reply.
    09/جمادى الأولى/1427 11:08 ص
  •  AndyKr wrote:
      

    VFP itself is 'smart' enough to realize that if you try to add "115" to this field that it should drop the decimal point and accept the value, but an external program, reading the field specification as "99.9" and getting a value of "999." is likely to see it as invalid. The solution is to always increase the size of the non-integer fields by 1 when you need the decimals. 

    Interesting, why isn't the Visual FoxPro OleDb provider as "smart" as VFP itself.

    09/جمادى الأولى/1427 12:51 م
  • >> Interesting, why isn't the Visual FoxPro OleDb provider as "smart" as VFP itself.

    I am sure that it is - it probably is sending the data through just fine. I would guess this is a limitation of OLE DB generally - it isn't "smart" enough to realize that "115" is still a valid numeric value when the field which contains it is limited to a maximum value of "99.9".

    I don't know too much about the inner workings of OLE DB but I guess that the recordset is created using the specificiation derived from the source table and if any data doesn't match the specification exactly it simply throws an error.

    09/جمادى الأولى/1427 01:23 م
  • the work around for that is to read the fields that are causing the errors convertin them to numeric.
    Convert(field as numeric(11,3))
    08/ربيع الأول/1430 04:27 م
  • "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."


    A resolution that worked for me was to use STR(fieldname, X, Y) AS fieldname
    where X = length of decimal(integer) , Y - decimal point places(integer)

    X, Y parameter values can be changed to whatever is required.

    STR() definition at:
    http://msdn.microsoft.com/en-us/library/texae2db(VS.80).aspx

    26/ربيع الأول/1431 09:40 ص