VB.NET - Visual Foxpro OLE DB Problem with Numeric Decimal Column

Question

In Short: I'm using VB.NET 2008 to connect to a Visual Foxpro 6 Database using the vfpoledb.1 driver. When I attempt to fill an OleDbDataAdapter with a dataset table that contains one of the numeric columns, I get the following error 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.

I'd like to retrieve this column from VB.NET 2008 and keep it in a numeric format.

The Long Version:

I'm using VB.NET to connect to a Visual Foxpro 6 database. Several of the columns in the table are intended for numeric data type of up to 8 digits. I'm not sure how Visual Foxpro data types work but it appears that this field allows someone to enter any of the following example values:

99999999  
99999.99  
    9.99  
    9.00
{nothing} 

From Visual Foxpro: I have access to small program called Foxwin that allows me to browse the VFP tables in a native VFP environment. This is what I'm using to access the data to obtain my examples for what I posted above. From here I can see that some rows contain no values at all in this field although they appear to be filled with spaces when there is no data. I've tried to run update queries to fill in every row with valid data but my update queries finish without updating any rows. I've tried ISNULL(bal_qty) and bal_qty IS NULL and neither one works.

From MS Access 2007: Using the same driver that I'm using in VB.NET and I can load the ADO recordset and bind it to a form without a problem. The decimal values appear to be stripped off, probably because all of them are ".00". I prefer to build this small program in VB.NET so I'm using MS Access only for testing.

From VB.NET: My SQL statement works if I convert bal_qty to String but this causes sort problems. I've tried VAL(STR(bal_qty)) and it fails with the same error message I've posted above. Here's the code I'm using:

Imports System.Data.OleDb

Public Class Form1
    Dim sConString As String = "Provider=vfpoledb.1;Data Source=C:\MyDatabase.dbc;Mode=3;"
    Dim con As OleDbConnection = New OleDbConnection(sConString)

    Private Function FetchData()

        con.Open()
        Dim ds As DataSet = New DataSet()
        Dim sSQL As String
        'This SQL statement works but the data doesn't sort properly.
        'sSQL = "SELECT item_cd, item_desc, STR(bal_qty) FROM invent;"

        sSQL = "SELECT item_cd, item_desc, bal_qty FROM invent;"

        Dim cmd As OleDbCommand = New OleDbCommand(sSQL, con)
        Dim daInv As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim iRecCount As Integer
        iRecCount = daInv.Fill(ds, "invent") 'The error occurs here.
        Me.DataGridView1.DataSource = ds.Tables("invent").DefaultView
    End Function

    Private Sub btnFetchData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFetchData.Click
        Call FetchData()
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        con.Close()
        con = Nothing
    End Sub
End Class

Solution 1

I've found the problem that was causing this. In the bal_qty column/field there was numeric data entered that didn't conform to the column's data type definition.

My field bal_qty has a Visual Foxpro data definition of:
Type: Numeric
Width: 8
Decimal: 2

The Visual Foxpro software apparently allowed the user to enter a value of 1000987 in this field which, as near as I can tell, doesn't cause an issue in Visual Foxpro. However, it does cause problems when accessing the data using anything other than Visual Foxpro because it violates the settings for this field.

Further testing revealed that MS Access 2007 also has a problem with this value. After loading the recordset into my Datasheet view form I get the error: "Data provider or other service returned an E_FAIL status." If I include the following WHERE clause I do not get the error: WHERE bal_qty < 9999

I've now resolved the problem by running an SQL UPDATE statement to change the value of bal_qty in the offending record.

I also found bad data in a column called markup. Hundreds of records are showing only asterisks where they should be showing numeric data. Including this markup column in my recordset queries causes my queries to fail with errors also.

See this SO Post concerning Asterisks in Numeric Columns and how to deal with it from .NET: How do I read asterisk (***) fields from .DBF data base?

If you're trying to resolve this problem you can view and edit VFP data natively using Visual RunFox 6 which is free on Ed Leafe's website: http://leafe.com/dls/vfp You can also edit the table structure from here. This tool is far from intuitive unless you are an experienced VFP programmer. You have to enter VFP commands from the command window for most everything you want to do.

Solution 2

We have this problem with a .NET app that reads foxpro dbf's. Our solution was to use the following in the select statement:

SELECT PropertyID, VAL(STR(SaleAmt)) as SaleAmt FROM MyTable

This converts the decimal column (SaleAmt) to a string and then back to a numeric value. Additionally, if an integer is desired, you can use INT(SaleAmt) in your SELECT statement.

Solution 3

I've worked with VFP through C# and VB with many tables with/without decimals without problems. As for the ordering of data, you could add the "order by" clause to the select so its default coming DOWN from VFP into VB in a presorted mode.

Additionally, in the version's I've built, I don't query into a dataset, and don't know if that might be a problem somewhere somehow...

dim dt as DataTable
dim sSQL as String
sSQL = "Select item_cd, item_desc, bal_qty from invent order by bal_qty"

then, your data adapter...

daInv.Fill( dt )

then you would still be able to bind directly to your grid...

Me.DataGridView1.DataSource = dt

As for the numeric content, from your DBF Viewer utility, the input mask of the table structure should default to its expecting values... browse to any record, get into th field and start typing "9.99999", so "9." will force you to ITs known decimal location, then keep typing 9's after the decimal see how many actual places ARE available. I could see how it might nag you if you try to put in a value with greater decimal precision than it allows.

Comments

[spottedmahn]

This answer worked on most columns for us but not all. This answer worked on all columns: CAST(FieldName As NUMERIC(11, 3))