Solved

Foxpro DBfs connecting with Vb.net and SQL

Posted on 2011-03-09
7
1,088 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now