Pen Truth

This is the forth installment of working with databases in VB.Net using Visual Studio 2008. In our last adventure we created a function to report the number of tables within a database, created a function to create a table within the empty database. For this installment we will add data to the table, bind a data table to a grid view, refresh the grid view after updating the table and clear all the data from the table.

To start, add three buttons to the main form, rename them and change their text.  The names will be: btnFillGridView, btnAddData and btnClearAllEntries.  The text will be: “Refresh Grid View”, “Add Data To Table” and “Clear All Table Entries”.  Double click each and move them to the “Button Events” region.

Since we will be adding data to the table we will need to place two textboxes on the main form. Rename the first tbxFirstName and the text to “John”. For the second textbox, rename it txbLastName and the text to “Doe”. Also add two labels and change the text to “First Name” and “Last Name”.

Next, from the Data section of the Toolbox select DataGridView and place it on the page.  We will use the default name of DataGridView1.  We also will be adding four new string variables to the main form:

Public Class frmMain

Dim firstName As String
Dim lastName As String
Dim dbDelete As String
Dim dbTableName As String = "myDbTable"
Dim dbSelectFrom As String = "SELECT * FROM " & dbTableName
Dim dbInsert As String  = "INSERT INTO " & dbTableName & "(First_Name, Last_Name) VALUES (@First_Name, @Last_Name)"
Dim dbTableCreate = "CREATE TABLE " & dbTableName & "(First_Name TEXT(20), Last_Name TEXT(20))"
Dim dbProvider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = "
Dim dbNameLoc As String = "C:\myDataBase.mdb"

If you place these new string variables above Dim dbTableName As String, the compiler will throw an error and the project won’t build.

Now we will create the code to fill  DataGridView1.  This will not be a function, rather it will be a Sub since we don’t need any returned message or data.  The sub will only produce a message if the fillDataTable sub failed, since we will be able to see the  resulting  data if the function completed successfully.  In the region set aside for the functions add the following code:

    Private Sub fillDataTable()

        Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc) ' Create the database connection object
        Dim dataAdapter As New OleDbDataAdapter(dbSelectFrom, dbConnect) ' Adapter connects to the table we want data from
        Dim dbDataSet As New DataSet() ' Creata a dataset to accept data from the table
        Try
            dbConnect.Open() ' Open the database connection
            dataAdapter.Fill(dbDataSet, dbTableName) ' Can only fill when a connection is open
            DataGridView1.DataSource = dbDataSet
            DataGridView1.DataMember = dbTableName
        Catch ex As Exception
            MsgBox(ex.Message) ' Send Ex Message
        Finally ' Regardless of the outcome, make sure that the database has been closed
            dbConnect.Close() ' Close the database connection
        End Try

    End Sub ' Function fillDataTable populates/refreshes DataGridView1 with table data

In the btnFillGridView Click Event sub enter the following code:

fillDataTable() ' Call And Show Results

Save the project and Start Debug.  When you click on the Refresh Grid View button did you get an error?  Try deleting your database and then click on the Refresh Grid View button.  Rebuild your database but don’t build the table, then click on the Refresh Grid View button.  Did the error message change from the last message?

Now lets tackle the function that will ad data to your data table.  This function will also return a string indicating the status of operation.  The reason I am using so many return strings when Booleans could be used is for educational purposes.

Private Function addData() As String

        Dim sRtrn As String ' Returns Results
        Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc) ' Create the database connection object

        Try
            dbConnect.Open() ' Open the database connection
            Dim dbCommand As New OleDbCommand(dbInsert, dbConnect) ' Can only create when a connection is open
            dbCommand.Parameters.AddWithValue("@First_Name", firstName) ' Add data to first column
            dbCommand.Parameters.AddWithValue("@Last_Name", lastName) ' Add data to last column
            dbCommand.ExecuteNonQuery() ' initiate the command to insert data
            sRtrn = "Successfully Added Data."
        Catch ex As Exception ' Catches errors
            sRtrn = ex.Message ' Send Ex Message
        Finally ' Regardless of the outcome, make sure that the database has been closed
            dbConnect.Close() ' Close the database connection
        End Try
        Return sRtrn ' Return the results to the calling sub.

    End Function ' Function addData inserts data into a table

The data we will be entering into the table will come from txbFirstName and txbLastName, so the btnAddData click will initialize our strings with data from the text boxes. In the btnAddData Click Event sub enter the following code:

firstName = txbFirstName.text ' Text that gets entered into the text box will be entered into the table
lastName = txbLastName.text ' Text that gets entered into the text box will be entered into the table
MsgBox(addData()) ' Call And Show Results

Build and run the project and try several name entries. Each time you add new data, click the button “Refresh Grid View”. Close the program and reopen it, click the button “Refresh Grid View” to see that the new data was saved to the table in the database.  The functions could just as easily return a True/False which is tested in an if statement.  Had we done this with the next function, our call to the function could have looked like this:

If Not addData() Then MsgBox("Operation Failed")

In this case the function would return a Boolean, True/False which the If statement could evaluate.  Had the function returned a False, a message box would be the result.  Our final function will clear the table of all entries.  By now you will have noticed that most of these database operations required objects be created for OleDbConnection.  Other operations also require formatting a message that tells OleDb what operation you want the connection to conduct.  For some operations an adapter must be initialized.

There are a lot of good online resources describing the command set.  Lets continue on with our project and create the function that will clear out all of the data from our data table wiping it clean.  In the region set aside for functions enter the following code:

Private Function clearDataTable() As String

        Dim sRtrn As String ' Returns Results
        Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc) ' Create the database connection object

        Try
            dbConnect.Open() ' Open the database connection
            Dim dbCommand As New OleDbCommand(dbDelete, dbConnect) ' Create the database command to create the table
            dbCommand.ExecuteNonQuery() ' initiate the command to create the table
            sRtrn = "Successfully Cleared Table Data"
        Catch ex As Exception
            sRtrn = ex.Message ' Send Ex Message
        Finally ' Regardless of the outcome, make sure that the database has been closed
            dbConnect.Close() ' Close the database connection
        End Try

        Return sRtrn ' Return the results to the calling sub.

    End Function ' Sub clearDataTable()

For the btnClearAllEntries Event, enter the following code:

dbDelete = "DELETE FROM " & dbTableName
MsgBox(clearDataTable()) ' Call And Show Results

When done, build the project and try deleting the table data, then click the button “Refresh Grid View”. Tip, add a call to the fillDataTable function in the btnClearAllEntries so that you don’t have to click the “Refresh Grid View” button every time you want to fill the Grid View.

The next tutorial will deal with searching the database and deleting rows based on the search.  Also we will fill text boxes and initialize labels with data retrieved from a search, so stay tuned.

Return to “How To Create A Table Within A Database”

Continue on to “How To Search A Table Within A Database”

In my completed code I pass values to the functions so that the code is much more portable. After the last installment I will post my complete code for the frmMain.

Disclaimer: The code in this tutorial is not intended as a final product or by no means the only way this project can be coded. The presented code is for educational purposes only and no warranty is granted or implied. I/we are not responsible for any damages as a result of using this code. Use at your own risk.