Solved

Foxpro DBfs connecting with Vb.net and SQL

Posted on 2011-03-09
7
1,098 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find results from sql within a time span 11 30
Can > be used for a Text field 6 42
Deal with apostrophe in stored procedures 8 42
access query to sql server 3 19
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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