[RESOLVED] [2005] Simple: Querying/opening a DBF (db3 fox pro) file

[joshjoneswas]

pretty new to vb.net here

I was never much good at database programming either since I never had to do much of it. Now I find myself searching and not learning much online even though I see tons of "resources" everywhere. I am not grasping it I guess.

I simply had an old database .DBF file (db3 foxpro I think, not 100%) and I need to open and get just three columns/tables from it: EMPNUM, FNAME, and LNAME.

So far, I have:

Const CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\flags\;Extended Properties=dBASE IV;User ID=Admin;Password="
        Dim connection As New OleDb.OleDbConnection
        connection.ConnectionString = String.Format(CONNECTION_STRING, "C:\temp\employee.dbf")
        connection.Open()

And that finally produces no errors since I did a little research and got the right "connection string" for my type of database.

Now, supposedly, from this point forward from what I've read, the actual pulling of information after opening the file should be the same as most of the other database querying done in .net. However, I am unsure of how to do it even if it is the same as the other database types.

I will take the data however I can get it. An Array or just dumped into a variable that I can parse through will be fine. Once I have the data, I predict I'll be fine. Just pulling the data from this database is proving way harder than I thought.

Employee.dbf has no PW on it. I can open and view the file with a program called "Database Desktop 7.0" and edit it there, etc. (much like MS Access). employee.dbf contains about 2 dozen or so columns/tables, but only about 20 rows. This will be different for each employee.dbf I access though, so I has to just pull the 20 rows or however many it has. The employee.dbf file I have comes accompanied by an employee.ndx file that I am not sure if it needs or not...but its there.

Thanks for any input!

[joshjoneswas]

I just realized there is a database forum. I apologize if this thread needs to be moved!

[jmcilhinney]

If you want to interact with databases then you're going to have to learn some SQL for a start. Then it's a matter of creating an OleDbDataAdapter and calling its Fill method or creating an OleDbCommand and calling its ExecuteReader command. There are links to tutorials on SQL and ADO.NET in my signature. As an example, to get all the data from a table in your database above you could do this:

Dim myTable As New DataTable
Dim myAdapater As New OleDb.OleDbDataAdapter("SELECT * FROM MyTable", connection)
 
myAdapter.Fill(myTable)
connection.Close()

Note also that if you only want to execute one Fill method you don't need to explicitly Open the connection, but if you do so then you need to explicitly Close again.

[jmcilhinney]

The Database forum would be for language-independent database issues. For ADO.NET in VB this is the appropriate forum.

[joshjoneswas]

Thanks for the response, jmcilhinney!

I am semi-OK at using SQL in enterprise manager, etc when I am pulling up data on a per-case basis. However, doing so in vb.net is proving a little different than I thought.

I understand your example above except for one part I think.
Once I "fill" the data into "myTable", how do I use it in the program? Is myTable an array? Your SQL command is just the one I imagine... just a one shot simple query that gets it all (I will change that part to just get my three tables later when it works) so that seems pretty straight-forward.

I am just not sure what to do with this last line or two of the code you have. Is everything that is pulled with that SQL command there in that myTable variable/array now? And if it is, how do I reference to that and use that data? Would it be something as simple as:

tmp$ = myAdapter(3)(1)

or something since it may be a 2-d array?

Thank you!

[jmcilhinney]

If you read some ADO.NET tutorials or even some MSDN documentation then you'll get an understanding of these classes. A DataTable is the .NET equivalent of a table in your database. It contains DataColumns and DataRows, the names of which I think speak for themselves. There is also the DataSet class. It is basically a little database within your .NET app. It contains DataTables and DataRelations, just like a database contains tables and relationships between them. Once you've got your data in a DataTable you can do all manner of things with it. One of the most common is to bind it to a DataGridView to display it in tabular form.

[joshjoneswas]

How would I bind it to a datagridview?

[joshjoneswas]

holy! I hacked around here, jmcilhinney, and I got what I was looking for and that is 99.999% thanks to you!

Const CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\flags\;Extended Properties=dBASE IV;User ID=Admin;Password="
        Dim connection As New OleDb.OleDbConnection
        connection.ConnectionString = String.Format(CONNECTION_STRING, "C:\temp\employee.dbf")
        connection.Open()
        Dim myTable As New DataTable
        Dim myAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM employee", connection)
        myAdapter.Fill(myTable)
        connection.Close()
        DataGridView1.DataSource = myTable

That populated the datagridview so beautiful I almost fell out of my chair here at work. You are worth your weight in gold, Sir!

-Josh

[jmcilhinney]

The simple answer is that you assign it to the DataSource property of the DataGridView. That would be all you would need.

The preferred method in .NET 2.0 is to use a BindingSource as an intermediary. If you want to go that way then you add a BindingSource to your form in the designer or create one in code. The DataTable then becomes the DataSource for the BindingSource and the BindingSource becomes the DataSource for the DataGridView. One possible advantage of using a BindingSource is that it allows you to use a BindingNavigator, which is basically a tool bar that allows you to navigate the records as well as add and delete rows.

[joshjoneswas]

I like the bindingsource idea for future reference. For this use right now, though, I simply need to retrieve the data and use it elsewhere. There is no need for editing the table (in fact, I don't want to!! ) and no need for flipping through records, etc. Just a one shot pull of info so the user can simply look at the data there. I don't even need to print it or anything. Just right there on the screen, look at it, then walk away.

Thank you again for all of your responses which are always full of information!

[jmcilhinney]

Nice work. You submitted post #8 while I was typing and beat me to it. Just make sure that you have set the grid's ReadOnly property to True if the data is not to be edited. You'll also want to set AllowUserToAddRows to False to get rid of the empty row at the bottom. You may also need to set AllowUserToDeleteRows to False as well, but that may be taken care of by setting ReadOnly to True.

[joshjoneswas]

yep! I am actually a recovering datagridview newbie. A few months back I had several weeks of toying with datagridview and pulling my hair out, etc. I finally learned enough to use it as I usually need to so I was so excited to get this DBF file pulled into a datagridview to use how I want!

[jmcilhinney]

You may already have seen it but this is where I learned how to use the DataGridView and I have only needed a very small amount of additional information.

http://msdn2.microsoft.com/en-us/lib...23(VS.80).aspx