Solved

Foxpro DBfs connecting with Vb.net and SQL

Posted on 2011-03-09
7
1,110 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
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

630 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