Solved

Need VB.NET ODBC code to transfer data from dbf to SQL

Posted on 2008-10-23
10
2,262 Views
Last Modified: 2008-12-10
Hi there. Our company is currently migrating from Access to SQL so i am getting a crash course in SQL and ODBC. We are still using DBFs so i need to find a way to import and export DBFs to SQL programmatically (through VB.NET).  

I've looked around the net and it sounds like I will need two ODBC connection strings, one for the SQL server and one for my DBF table. Once I established my connection how would i transfer the data - can I use a simple insert query? How would this look?

Thanks in advance for your help
Nino
0
Comment
Question by:ND_2007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22796005
You probably want to select everything from the DBF in a datareader, iterate that datareader, and run inserts into the SQL Server.  That would need to be done for each table.

Data integrity constraints (such as foreign keys) could pose a problem ... I would leave foreign keys disabled in the SQL Server DB until the data are loaded.

Do you have the table structure already defined in SQL Server?

Also ... is this a one-time import?  Or something you need to continue doing programatically?  if one-time, you might do better using the Import wizard from SQL Server management studio (SSMS).

See what Cindy says here: http://64.233.169.104/search?q=cache:LrLMlknvcvsJ:www.developersdex.com/sql/message.asp%3Fp%3D580%26r%3D5305583+DBase+import+to+SQL+Server+2005&hl=en&ct=clnk&cd=1&gl=us&client=firefox-a

"As an alternative you can download and install
 the FoxPro and Visual FoxPro OLE DB data provider from
 msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
 Import Wizard or set up a linked server."
0
 
LVL 1

Author Comment

by:ND_2007
ID: 22812219
Daniel - thanks for the response. I plan on already having the the structure up in SQL on the import side of things. This is definitely something we will be doing repeatedly.

The Jet OLE seems to run into issues on the 64 bit version of SQL server according to this..
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221492&SiteID=1

Looks like i am going to go with the Data Reader. Can i also use the data reader to export to a DBF?

Thanks

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22812466
>>Can i also use the data reader to export to a DBF?

The DataReader, or course, just READS the data ...  but you can read the data in a DataReader from the SQL Db, then use an OleDBCommand to execute insert statements against the FoxPro DB.

Talking to the SQL Server DB, you'll want to use SqlDataReader, SqlCommand, etc.  Talking to the FoxPro DB, I'm pretty sure the best to use will be the OleDB family of data access objects.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:ND_2007
ID: 22813187
the OLE does not seem like a good option due the 64bit SQL server not supporting the Microsoft Jet Driver that seems to show up everytime i investigate OLE....but thanks.

In the ODBC world, I have the DBF and SQL connections open succesfully (code below)....how would I go about transferring data between the two?

'' connection to SQL server
        Dim oODBCConnectionSQL As Odbc.OdbcConnection
        Dim sConnStringSQL As String = _
                  "Driver={SQL Server};" & _
                  "Server=Nexus-SQL;" & _
                  "Database=TEST" & sJN & ";"
        oODBCConnectionSQL = New Odbc.OdbcConnection(sConnStringSQL)
        oODBCConnectionSQL.Open()

        '' connection to DBF folder
        Dim oODBCConnectionDBF As Odbc.OdbcConnection
        Dim sConnStringDBF As String = "DBQ=" & sNCOAFolder & ";Driver={Microsoft dBase Driver (*.dbf)}; DriverId=277;FIL=dBase4.0 "
        oODBCConnectionDBF = New Odbc.OdbcConnection(sConnStringDBF)
        oODBCConnectionDBF.Open()

0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 267 total points
ID: 22813279
ODBC will be less efficient that OLEDB and certainly than the straight SQL Connection ... but if ODBC works and OLEDB doesn't, by all means go ODBC!

OK, so you have your connections open ...

Links to the classes in use are:
dim drSQL as ODBCDataReader
dim cmSQL as new ODBCCommand(oODBCConnectionSQL , "Select * from Table1")
drSQL = cmSQL.ExecuteReader()
 
dim cmFox as ODBCCommand
dim sSqlFoxInsert as String = "Insert Into MyTable(Field1, Field2, Field3) Values (@F1, @F2, @F3)"
 
While drSQL.Read()
  cmFox = new ODBCCommand(oODBCConnectionDBF , sSQLFoxInsert)
  cmFox.Parameters.Add (@F1, drSql.GetString(drSql.GetOrdinal("Field1"))
  cmFox.Parameters.Add (@F2, drSql.GetString(drSql.GetOrdinal("Field2"))
  cmFox.Parameters.Add (@F3, drSql.GetString(drSql.GetOrdinal("Field3"))
  cmFox.ExecuteNonQuery()
Wend

Open in new window

0
 
LVL 1

Author Comment

by:ND_2007
ID: 22816436
OK i think i am close, just need help on this last part - my code is below. The executeNonQuery commmand gives me "ERROR [07002] [Microsoft][ODBC dBase Driver] Too few parameters. Expected 3." I checked cmfox.parameters.count and it was = 3. When I only add 2 parameters i still get the "too few parameters. Expected 3".  Note the quotes at "@F1" which I had to add to get it to compile - not sure if that goofed something up.... Also worth mentioning is that the GetOrdinal functions are returning correct field numbers..

        Dim drSQL As ODBCDataReader
        Dim cmSQL As New OdbcCommand("Select * from " & sInTable & ";", oODBCConnectionSQL)
        drSQL = cmSQL.ExecuteReader()

        Dim cmFox As ODBCCommand
        Dim sSqlFoxInsert As String = "Insert Into " & Replace(UCase(sOutTable), ".DBF", "") & "(iname,ifirst,ilast) Values (@F1, @F2, @F3)"

        While drSQL.Read()
            cmFox = New OdbcCommand(sSqlFoxInsert, oODBCConnectionDBF)
            cmFox.Parameters.Add("@F1", drSQL.GetString(drSQL.GetOrdinal("iname")))
            cmFox.Parameters.Add("@F2", drSQL.GetString(drSQL.GetOrdinal("ifirst")))
            cmFox.Parameters.Add("@F3", drSQL.GetString(drSQL.GetOrdinal("ilast")))
            cmFox.ExecuteNonQuery()
        End While
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22820649
Are the fields in question, in fact string (varchar or nvarchar) data types?
0
 
LVL 1

Author Comment

by:ND_2007
ID: 22821163
Yes, they are all varchar fields
0
 
LVL 1

Author Comment

by:ND_2007
ID: 22825027
OK I finally got this to work - THANKS! As you mentioned it's painfully slow. Here's how I ended up pulling it off... I would'nt recommend this method to anyone else trying to transfer data to DBF
       
' '' connection to SQL server
        Dim oODBCConnectionSQL As Microsoft.Data.Odbc.OdbcConnection
        Dim sConnStringSQL As String = _
                  "Driver={SQL Server};" & _
                  "Server=Nexus-SQL;" & _
                  "Database=TEST" & sJN & ";"
        oODBCConnectionSQL = New Microsoft.Data.Odbc.OdbcConnection(sConnStringSQL)
        oODBCConnectionSQL.Open()

        ' '' connection to DBF folder
        Dim oODBCConnectionDBF As Microsoft.Data.Odbc.OdbcConnection
        'Dim sConnStringDBF As String = "DBQ=" & sNCOAFolder & ";Driver={Microsoft dBASE Driver (*.dbf)}; DriverId=277 "
        Dim sConnStringDBF As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & sNCOAFolder

        oODBCConnectionDBF = New Microsoft.Data.Odbc.OdbcConnection(sConnStringDBF)
        oODBCConnectionDBF.Open()

        '' now actually do the data transfer
        Dim drSQL As OdbcDataReader
        Dim cmSQL As New OdbcCommand("Select * from " & sInTable & ";", oODBCConnectionSQL)
        drSQL = cmSQL.ExecuteReader()
        Dim cmFox As OdbcCommand
        Dim sSqlFoxInsert As String = ""
        cmFox = New OdbcCommand(sSqlFoxInsert, oODBCConnectionDBF)
        While drSQL.Read()
            For x = 0 To 32
                Try
                    F(x) = drSQL.GetString(x)
                Catch ex As Exception
                    F(x) = ""
                End Try
            Next
           
            sSqlFoxInsert = "Insert Into " & sOutTable & "(uniqueid,iname,iprefix,ifirst,imiddle,ilast,isuffix,ititle,icompany,iaddress1,iaddress2,iaddress3,icity,istate,izip,cmailname,cdualname,cprefix,cprefix2,cfirst,cfirst2,cmiddle,cmiddle2,clast,clast2,csuffix,csuffix2,ctitle,ctitle2,csal,csal2,ccompany,compflag) " & _
                "Values "
            ''add the array fields to the statement
            For x = 0 To 32
                If x = 0 Then
                    sSqlFoxInsert = sSqlFoxInsert & "('" & F(x)
                Else
                    sSqlFoxInsert = sSqlFoxInsert & "','" & F(x)
                End If
            Next
            sSqlFoxInsert = sSqlFoxInsert & "')"

            cmFox.CommandText = sSqlFoxInsert
            cmFox.ExecuteNonQuery()

        End While
0
 

Expert Comment

by:xms001
ID: 23140875
ND_2007:
do you have program in vb.net for  transfer data from foxfro to sql server?
Thanks
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question