ND_2007
asked on
Need VB.NET ODBC code to transfer data from dbf to SQL
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
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
ASKER
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
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
>>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.
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.
ASKER
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(sConnS tringSQL)
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(sConnS tringDBF)
oODBCConnectionDBF.Open()
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(sConnS
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(sConnS
oODBCConnectionDBF.Open()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.GetO rdinal("in ame")))
cmFox.Parameters.Add("@F2" , drSQL.GetString(drSQL.GetO rdinal("if irst")))
cmFox.Parameters.Add("@F3" , drSQL.GetString(drSQL.GetO rdinal("il ast")))
cmFox.ExecuteNonQuery()
End While
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,
cmFox.Parameters.Add("@F1"
cmFox.Parameters.Add("@F2"
cmFox.Parameters.Add("@F3"
cmFox.ExecuteNonQuery()
End While
Are the fields in question, in fact string (varchar or nvarchar) data types?
ASKER
Yes, they are all varchar fields
ASKER
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.OdbcCo nnection
Dim sConnStringSQL As String = _
"Driver={SQL Server};" & _
"Server=Nexus-SQL;" & _
"Database=TEST" & sJN & ";"
oODBCConnectionSQL = New Microsoft.Data.Odbc.OdbcCo nnection(s ConnString SQL)
oODBCConnectionSQL.Open()
' '' connection to DBF folder
Dim oODBCConnectionDBF As Microsoft.Data.Odbc.OdbcCo nnection
'Dim sConnStringDBF As String = "DBQ=" & sNCOAFolder & ";Driver={Microsoft dBASE Driver (*.dbf)}; DriverId=277 "
Dim sConnStringDBF As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;Sou rceDB=" & sNCOAFolder
oODBCConnectionDBF = New Microsoft.Data.Odbc.OdbcCo nnection(s ConnString DBF)
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,i first,imid dle,ilast, isuffix,it itle,icomp any,iaddre ss1,iaddre ss2,iaddre ss3,icity, istate,izi p,cmailnam e,cdualnam e,cprefix, cprefix2,c first,cfir st2,cmiddl e,cmiddle2 ,clast,cla st2,csuffi x,csuffix2 ,ctitle,ct itle2,csal ,csal2,cco mpany,comp flag) " & _
"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
' '' connection to SQL server
Dim oODBCConnectionSQL As Microsoft.Data.Odbc.OdbcCo
Dim sConnStringSQL As String = _
"Driver={SQL Server};" & _
"Server=Nexus-SQL;" & _
"Database=TEST" & sJN & ";"
oODBCConnectionSQL = New Microsoft.Data.Odbc.OdbcCo
oODBCConnectionSQL.Open()
' '' connection to DBF folder
Dim oODBCConnectionDBF As Microsoft.Data.Odbc.OdbcCo
'Dim sConnStringDBF As String = "DBQ=" & sNCOAFolder & ";Driver={Microsoft dBASE Driver (*.dbf)}; DriverId=277 "
Dim sConnStringDBF As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;Sou
oODBCConnectionDBF = New Microsoft.Data.Odbc.OdbcCo
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,
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,i
"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
ND_2007:
do you have program in vb.net for transfer data from foxfro to sql server?
Thanks
do you have program in vb.net for transfer data from foxfro to sql server?
Thanks
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
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro
Import Wizard or set up a linked server."