Solved

Foxpro DBfs connecting with Vb.net and SQL

Posted on 2011-03-09
7
1,104 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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

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 29

Accepted Solution

by:
Olaf Doschke earned 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

761 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