title: VB.NET - Visual Foxpro OLE DB Problem with Numeric Decimal Column
body: <p><strong>In Short:</strong>
  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:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>I'd like to retrieve this column from VB.NET 2008 and keep it in a numeric format.</p>
  
  <p><strong>The Long Version:</strong></p>
  
  <p>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:</p>
  
  <pre><code>99999999  
  99999.99  
      9.99  
      9.00
  {nothing} 
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>