ODBC and SELECT / DataSet issue

[Jamal]

Hi,

I am trying to import a FoxPro 2.6 DBF table into SQL table using the
following connection string:

Dim ds As DataSet
Dim cDbfName AS String = "mydbf"
Dim sql As String = "SELECT * FROM " + cDbfName

ds = GetDataTable(str_path, sql)

Public Function GetDataTable(ByVal str_path As String, ByVal sql As String)
As DataSet
Dim ds As New DataSet
Dim da As New OdbcDataAdapter()
Dim con As New OdbcConnection("Driver={Microsoft dBASE Driver
(*.dbf)};DriverID=277;Dbq=" + str_path)

Using con
Dim adapter As New OdbcDataAdapter(sql, con)

' Open the connection and fill the DataSet.
Try
con.Open()
adapter.Fill(ds) ' debug breakpoint here to view ds
contents.
Catch ex As Exception
Throw New ApplicationException("Exception Occured: " +
ex.Message)
End Try
End Using

Return ds

End Function

When I debug the 'ds' variable which refers to the dataset, all the columns
read appear fine in the grid, however some columns are blank even though
they have values in the read DBF file.
In particular a column named EMAIL which 60 chacacters long. I changed the
column name to EMAIL1, however, it did not make a difference.

Anyone has a clue on why this occurring and how to correct it?

Using VS 2008 Sp1, SQL Server 2008 sp1.

Thanks,
James

 

[Jennifer Mathews]

Jamal,


I don't know if this has anything to do with it but you are not using a FoxPro driver
but a dBase driver. FoxPro and dBase table structures are not the same.

 

[Jamal]

Thanks for the reply. Foxpro 2.6 dbf files are compatible with dBase III dbf
files.

I also used OleDbConnection("Provider=VFPOLEDB.1;Data Source=" & str_path &
";")
as shown below with same result.
So, it must be something else. Could the DataAdapter.Fill( ) method be the
culprit?

Public Function GetDataTableOLEDB(ByVal str_path As String, ByVal sql As
String) As DataSet
Dim cn As New OleDbConnection("Provider=VFPOLEDB.1;Data Source="
& str_path & ";")
cn.Open()
Dim cmd1 As New OleDbCommand(sql, cn)
Dim ds As New DataSet
Dim da As New OleDbDataAdapter()

da.SelectCommand = cmd1
da.Fill(ds)
Return ds
End Function

Jamal

 

[Jamal]

Hi All,

The code works fine after all. Apparently the DBF file did not really follow
the standards and there was a very long column (width 1200) that came before
the EMAIL field and apparently the Fill() method did like that; I moved the
EMAIL up the order list of columns and it magically worked!

HTH,
Jamal

 

[Guest]

Hi All,

The code works fine after all. Apparently the DBF file did not really follow
the standards and there was a very long column (width 1200) that came before
the EMAIL field and apparently the Fill() method did like that; I moved the
EMAIL up the order list of columns and it magically worked!

HTH,
Jamal


















- Show quoted text -

That's strange. Anyway, try to reduce the number of columns in the
SELECT statement, since it looks like you don't need them all. Use

SELECT id, email, etc... FROM...

 

[Jamal]

That was one the things I tried early on, but did not work.

Jamal

Hi All,

The code works fine after all. Apparently the DBF file did not really
follow
the standards and there was a very long column (width 1200) that came
before
the EMAIL field and apparently the Fill() method did like that; I moved
the
EMAIL up the order list of columns and it magically worked!

HTH,
Jamal


















- Show quoted text -

That's strange. Anyway, try to reduce the number of columns in the
SELECT statement, since it looks like you don't need them all. Use

SELECT id, email, etc... FROM...