Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Foxpro DBfs connecting with Vb.net and SQL

Posted on 2011-03-09
7
Medium Priority
?
1,113 Views
Last Modified: 2012-05-11
I'm developing a schedule management software in vb.net and MS SQL server for a medical center. The center works in alliance with another company that has a user management sw developed in foxpro.
The  schedule management software needs to consult some data fields from the other sw, where the users data is stored in dbfs.Both data bases, will be stored in the same server.

The screen shot shows the new software´s interface, on the 1st fields, you make a query to the foxpro db and it shows other fields also stored there after clicking the search button (buscar). However, the new sw will manage new fields, (like 5 fields shown in the screenshot) that are only medical related and will be stored in a MS SQL server, linked to the data in the foxpro db.

example: Jhon Doe is listed in the dbf files. I need jon does' id, name, age, gender, etc to come up when a query under his name is done. But also i need to store NEW fields connected to jhon doe stored in a new SQL database.

Any ideas where/how to start?

Thanks in advance! Let me know if I didn't make myself clear.
pantalla.png
0
Comment
Question by:infogws
[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
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35089397
One approach you can take is to create either a linked server or something like OPENROWSET().  This way you can query the dbf as if it was (nearly) a SQL Server table.
0
 
LVL 11

Expert Comment

by:bansidhar
ID: 35090885
you can use OleDB Provider for VFP and run sqls like SQL server (you will be limited only by the VFP database capability) . check the following link.

http://www.devnewsgroups.net/adonet/t35499-connect-foxpro-data-dbf-vb-net.aspx
0
 

Author Comment

by:infogws
ID: 35129798
Hi, may have missled you with my poorly explained question. I have it way more clear now; what I need to do is just load dbfs into a datatable

There are 3 different dbf tables involved; one specifying members data (member id, names, phones, etc). Another table specifying their affiliation status to a health care program (member id, affiliation contract id, contract date, etc). The third and last table specifies the affiliation payments status (member id, payment number, payment date, payment amount, etc).
Once I do a member search, I need data from all 3 tables to load into a datatable in v.basic .net.

I've never worked with foxpro dbf's before so I dont really know how to make this connection. I'd really appreciate some code examples, thanks!


0
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35137079
Foxpro data is queried like any other databse via sql, eg SQL-Select.
Ingredients needed are the VFP OleDB Provider and this code fills a databtable:

Dim conn As OleDbConnection
Dim comm As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable

conn = New OleDbConnection()
        conn.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" _
            + "\Program Files (x86)\Microsoft Visual FoxPro 9\" _
            + "Samples\Northwind\Northwind.dbc;" _
            + "Collating Sequence=MACHINE"

conn.Open()
comm.Connection = conn
comm.CommandText = "SELECT * FROM Customers"
da.SelectCommand = comm
da.Fill(dt)

Open in new window


In this sample code you're connecting to sample data coming with the foxpro. It should also come with the oldb provider, but the path needs to be adjusted.

Bye, Olaf.
0
 

Author Comment

by:infogws
ID: 35140681
I've been trying with this VPF oledb provider but keep getting error messages. This one i cant ger rid of:

"The 'VFPOLEDB.1' provider is not registered on the local machine"

Ive installed this vfp right here:

http://www.microsoft.com/downloads/en/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

Olaf, I tried manipulating your code, didnt work :(
Using dbConn As New System.Data.OleDb.OleDbConnection("Provider=VFPOLEDB.1;C:\dataafiliacion\; Mode=Read; Collating Sequence=MACHINE;")
            Try
                dbConn.Open()

                Dim da As New OleDbDataAdapter("SELECT * FROM socios.dbf", dbConn)
                Using dt As New DataTable()
                    da.Fill(dt)
                    gvrueba.DataSource = dt
                End Using


                dbConn.Close()

            Catch ex As Exception
                MessageBox.Show(String.Format("Error al abrir la base de datos{0}{1}", vbCrLf, ex.Message))
                Exit Sub
            End Try
        End Using

Open in new window

0
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1500 total points
ID: 35141412
"The 'VFPOLEDB.1' provider is not registered on the local machine" suggests the setup failed. What have you downloaded? MSM is not a setup of the oledb provider.

Also: If you are on a 64bit system you must setup your VB.net project to target an 32 bit processor, your application needs to be 32bit to make use of 32bit drivers, as the vfp oledb provider is.

Bye, Olaf.

0
 

Author Closing Comment

by:infogws
ID: 35226783
I continued investigating and found the solution. The suggested solutions helped though.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

704 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