1

I'm trying to read a .DBF data base using VB.NET... the code is working until I reach a column on one of the tables that is supposed to be Decimal but it is instead filled with asterisks.

The connection string and reader I'm using go like this:

Using SDBconnection As New OleDbConnection("User ID=;" & _
                                                    "DSN=;" & _
                                                    "Cache Authentication=False;" & _
                                                    "Data Source=""" & TextBox_DBLoc.Text & """;" & _
                                                    "Provider=""VFPOLEDB"";" & _
                                                    "Collating Sequence=MACHINE;" & _
                                                    "Mask Password=False;" & _
                                                    "persist security info=False;" & _
                                                    "Mode=Share Deny None;" & _
                                                    "Extended Properties=;" & _
                                                    "Encrypt Password=False")

Dim oleReader As OleDbDataReader = New OleDbCommand("Select * from " & table, SDBconnection).ExecuteReader()

Is there some way to evaluate when the data type does not correspond and then to a cast or something like that?

The table is readable in other DBF reader software and no error is showed when read. How can I do the same?

EDIT: The error message thrown by my code when it reaches the column with asterisks is this:

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.

Jorge Perez
  • 23
  • 7
  • Where are you displaying the data, where you see the field is *filled instead with asterisks*? This is not an issue with the data; it's an issue with whatever way you're displaying it, because *the table is readable in other DBF reader software*. It's also *readable* in yours; you're simply displaying it wrong. – Ken White Mar 22 '15 at 00:52
  • For a start I'm just translating the table to a csv file, when I do the read to that column filled with *** throws the "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." – Jorge Perez Mar 22 '15 at 01:04
  • I see the asterisks by opening the tables with a software I downloaded named DBF Viewer 2000, there I can open the tables without problem. – Jorge Perez Mar 22 '15 at 01:05

1 Answers1

2

There is a problem with Visual Foxpro that apparently allows the user to enter a overflowed value in numeric fields which doesn't seem to 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. VFPOLEDB returns these values as asterisks. As a workaround, you could try the following query:

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

This converts the decimal column (SaleAmt) to a string and then back to a numeric value.

You also can check your data for overflowed values and modify the dbf table structure to solve this problem.

This link explains this problem

Tito
  • 364
  • 1
  • 4
  • I've seen that answer many times, but what if I want to read the whole table columns, not just SaleAmt (referring to your example) using the wildcard '*'? – Jorge Perez Mar 22 '15 at 02:42
  • Then you should modify the dbf table structure and increase the size of the numeric field. Using the wildcard will trigger the error. – Tito Mar 22 '15 at 02:47
  • I see... Well, since that is not an option in my case I guess I'll have to call each column explicitly then. Thanks! – Jorge Perez Mar 22 '15 at 19:09