Insert VB.net Data Table into a Visual Foxpro table

Question

My company wants to slowly switch over some old programs that are written in Visual FoxPro 6.0. The problem is that we have a bunch of programs that still reference FoxPro tables.

I have a query that pulls information down to a data table in vb.net. What do I need to do to insert the information from the data table into a FoxPro table.

This is the initial query that get's put into a data table.

 Dim SConn As New OleDbConnection(conn)
 Dim da As OleDbDataAdapter
 Dim dt As New DataTable
 Dim sSql As String = "SELECT DISTINCT * FROM DWEmployee where cono <> 'XX' AND officeno <> 'XX' AND cono <> '' AND officeno <> ''"

 SConn.Open()
 da = New OleDbDataAdapter(sSql, SConn)
 da.SelectCommand.CommandTimeout = 90
 da.Fill(dt)
 SConn.Close()

What do I need to do to replicate this Foxpro code in VB.net

select distinct * from (the data table) into table gp_emps

This will be my connection string for my FoxPro Table if I need it.

Dim FConnString As String = "Provider=vfpoledb;Data Source=Z:\update_dwprm01\gp_emps.DBF;Collating Sequence=general;"
Dim FPdbConnection As New OleDbConnection(FConnString)

Thanks in advance.

DRapp DRapp · Accepted Answer · 2013-09-11T20:20:32

You appear to be on the right track. A few minor issues. The connection string should have the provider as

Provider=vfpoledb.1;

The Data Source component of the string should point to the physical directory where the .dbf files are located and NOT the specific .DBF file itself.

Data Source=Z:\YourShareOnServer\SomePath

The query itself looks ok as you have it coded. HOWEVER, when you DO get more into .net select, insert, update, delete, you will DEFINITELY need to look into using PARAMETERIZED queries to prevent SQL-Injection (even though not practical with VFP since it would just choke on comment / hack attempts), but is still good to be in the practice.

Build your Command and have "?" as a place-holder for the "parameters", then add parameters in the same order as your "?" placements in the query such as:

dim OleDbCommand oCmd = new OleDbCommand();
oCmd.CommandText = "SELECT DISTINCT * from DWEmployee " _
                 + "where cono <> ? and officeno <> ? AND cono <> '' AND officeno <> ''"

' Only sampling with first 2 as parameters to get the idea...    
oCmd.Parameters.Add( "parmCONO", "XX" );
oCmd.Parameters.Add( "parmOffice", "XX" );

SConn.Open()
da = New OleDbDataAdapter(oCmd, SConn)
da.SelectCommand.CommandTimeout = 90

continue with filling the data table...

I've had many posts out for VFP, OleDB and C#, but the principles all are the same. It's been a while since using OleDB connectivity but you are getting there.

Insert VB.net Data Table into a Visual Foxpro table

1 Answers