Creating a Data Set

You can easily add a database to a VB project, but you will be working with a copy of the database. Any records you add, delete or change will not be made to the original database. The best use without further work is for displaying tables and queries.

To do more, you must create a temporary version, then re-write the data base when you are through. There are two structures in VB you use can use to accomplish this, the data set and the data grid.

The following applies to using a data set. You have already added the database to the project, and prepared a form which will display the dataset items.  The text that will vary in your project is shown in blue, bold and italics.

If you want to know what this is all about, an explanation follows the code examples.

 

Public Class frmForm1 Inherits System.Windows.Forms.Form Dim Inc As Integer Dim MaxRows As Integer Dim con As New OleDb.OleDbConnection Dim ds As New DataSet Dim da As OleDb.OleDbDataAdapter Dim sql As String

Con is the variable that will hold the connection information.  The connection needs to know what data access technology to use and where the database file is.

Con is declared, then given the connection information. I used the continuation character to divide the assigned string onto three lines. This makes it easy to read, but isn't necessary.

A test is done before opening the file, to avoid a run-time error when the file is not there. The sql statement tells which records to extract. The da variable passes it to the data adapter. The data adapter fills the data set.

The connection is then closed.

After the data set is filled, the count from the operation is used to set the variable MaxRows.

The database and the data set are not connected.  A connection object is used to connect them, then the Data Adapter is used communicate between them.

The Connection Object

This connects the database.  The one used to connect to an Access database is Jet OLE DB. Others are SQL Server OLE DB another is Oracle OLE DB.

The following statement declares con as a variable to give the connection object the information it needs.  Declare this variable with the other public variables.

Dim con As New OleDb.OleDbConnection

The connection object needs to know what type of data base connection to use and what the database file to use.  The connection string is set to work with an Access database with the following statement t.

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;data source=C:\folder\filename.mdb"

The Data Adapter

The Data Adapter communicates between the Connection Object to the Data Set. It will need to know two things - what to read or write to the database and what is the connection to use

Declare the Data Adapter like this with the other dimension statements.

Dim da As OleDb.OleDbDataAdapter

When you ask for a read or write action to the data set, you pass the information to the data adapter like this:

da = New OleDb.OleDbDataAdapter(sql, con)

The Data Set

You have one data set in the project, which is directly from your database, but you'll remember that one can't accept any modifications..  You will need a second one that can be modified.

Declare the dataset like this with the other dimension statements.

Dim ds As New DataSet

The Query String

This is the command used to read from or write to a database. It will be passed using a string variable.  Declare the query string variable like this with the other dimension statements. The sql command in this case draws all records from a table in an Access database.

Dim sql As String

Assign the a command to the variable like this:

sql = "SELECT * FROM TableName"

The * means all records.