Pen Truth

Pen Truth Contributor

This is the third installment of working with databases in VB.Net using Visual Studio 2008.  In our last adventure we created an empty database, deleted the database and tested if the database existed.

In this installment we will create a table within our empty database.  Using the previous project we will add two more buttons to the main form.  The buttons will be named btnTablesExist and btnCreateTable.  Change the text to: “Test Tabels” and “Create Tabel”. Double click each button then move the events into the region we created for the button events.

Also we need to create two string variables to hold the name of the table we will be working with and one to store the command for creating the table.  So, below Public Class frmMain add Dim dbTableName As String = “myDbTable”

Public Class frmMain

Dim dbTableName As String = "myDbTable" 
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"

Next we will create a function that counts the number of tables that exist within our database.  As before, the function will return a string  However, GetOleDbSchemaTable returns an integer representing the number of tables within the database, so we will have to convert that to a string before returning the value with the statement dbSchema.Rows.Count.ToString:

Private Function checkForTables() As String

        Dim sRtrn As String ' Returns number of tables found in database
        Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc) ' Create the database connection object

        Try
            dbConnect.Open()
            ' Can only create when a connection is open
            Dim dbSchema As DataTable = dbConnect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, dbTableName, "TABLE"})
            '  dbConnect.Close()
            sRtrn = "There Are  " & dbSchema.Rows.Count.ToString & " Tables In The Database"
        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()
        End Try
        Return sRtrn ' Return the results to the calling sub.

End Function ' Function checkForTables

In the btnTablesExist button event add the following code:

MsgBox(checkForTables()) ' Calls the Function And Shows The Results

Save your project, then build and run your project.  Let’s test the ” Test Tables” button.  Did the the message show the expected result?

Now lets create the function that will create the data table.

Private Function createDataBaseTable() As String

        Dim sRtrn As String ' Returns Results
        Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc) ' Create the database connection object
        Dim dbCommand As New OleDbCommand(dbTableCreate, dbConnect) ' Create the database command to create the table

        Try
            dbConnect.Open() ' Open the database connection
            dbCommand.ExecuteNonQuery() ' initiate the command to create the table
            sRtrn = "Successfully Created A Table"
        Catch ex As Exception ' Catches errors
            sRtrn = ex.Message ' Could not create a table, returns False
        Finally ' Regardless of the outcome, make sure that the database has been closed
            dbConnect.Close() ' Open the database connection
        End Try

        Return sRtrn ' Return the results to the calling sub.

End Function ' Function createDataBaseTable

In the button event for btnCreateTable, add the following code:

MsgBox(createDataBaseTable())

Save your project and test the createDataBaseTable function.  Try the creating a table again.  Did you get the expected message?

In the next  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.

Return to “How To Create An Empty Database”

Continue on to “How To Insert Data Into 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.