ODBC and SELECT / DataSet issue

Discussion in 'ASP .Net' started by Jamal, Nov 16, 2009.

  1. Jamal

    Jamal Guest

    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
     
    Jamal, Nov 16, 2009
    #1
    1. Advertisements

  2. 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.
     
    Jennifer Mathews, Nov 16, 2009
    #2
    1. Advertisements

  3. Jamal

    Jamal Guest

    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, Nov 16, 2009
    #3
  4. Jamal

    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
     
    Jamal, Nov 17, 2009
    #4
  5. 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...
     
    Alexey Smirnov, Nov 17, 2009
    #5
  6. Jamal

    Jamal Guest

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

    Jamal

    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, Nov 17, 2009
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.