Pen Truth Contributor
This is the second installment of working with databases in VB.Net using Visual Studio 2008. The first installment covered the project requirements and explains the project setup including some considerations for advanced features for those running a 64 bit OS. In this installment we will begin by setting up the basics in the code viewer and creating the code to create an empty database.
Now that we have our project setup and the references added we can begin by preparing the basics. Start by double clicking on the form in the Designer Tab. Visual Studio will automatically add the sub that handles the form loading event. We will also create regions in the form which we can place subs and functions by category. Regions are useful because you can hide several sections of the program into one small area, uncluttering your workspace.
To enclose your code within regions simply type #region “some description” and press enter. I recommend placing a spaces in the containing quotes for readability. Example, write ” description ” instead of writing “description” for better readability when the region is collapsed. After creating a region, move the #End Region code below the “End Sub” of “Private Sub frmMain_Load”. Also add a region for the buttons below the end code for the Opening/Closing region. Below the button region create a region for the database functions.
Another useful bit of code is the form closing event handler. This sub is where a lot of cleaning up can be done before your program exits such as disposing of streams, closing open coms etc..
For this project we also need to import ADOX, System.Data, and System.Data.OleDb just as in the Code Example below. We are also going to add some string elements that will define the database provider and the location and name of the database we are creating;
Dim dbProvider As String = “PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ”
Dim dbNameLoc As String = “C:\myDataBase.mdb”
Code Example:
Imports ADOX
Imports System.Data
Imports System.Data.OleDb
Public Class frmMain
Dim dbProvider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = "
Dim dbNameLoc As String = "C:\myDataBase.mdb"
#Region " Form Loading & Closing "
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub ' End of frmMain_Load
Private Sub frmMain_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
End Sub ' End of frmMain_FormClosing
#End Region ' End of region Form Loading & Closing
#Region " Button Events "
#End Region ' End of region Button Events
#Region " Database Functions "
#End Region ' End of region
End Class
Form closing is not necessary for what we are doing so you don’t need to add it for this project. Also notice that I place comments after the End statements. This is helpful when navigating through long lines of code and when sections are collapsed from view. If it is easier you can change the location of your database, just change
The code for creating the database depends on the ADOX and System.Data.oleDb imports. You will also notice that I am commenting my code a lot here, this is for your benefit but I do use comments extensively in my work. Quite often you will have an extensive piece of work that works for years until one day you need to make changes. Comments serve to keep the intentions of your work available and at hand. As long as the comments aren’t outrageously complicated and long it helps to explanation your intentions even if it doesn’t seem like it while you are coding. I have added a little more comments that I normally would as a method of explaining some of the steps within the code.
The function return a string indicating the results of the operation. This string will be parsed to a message box and called from within the button click event. The ADOX object create our database file myDatabase.mdb. Any errors will be trapped by a Try statement and the resulting error will be returned to the calling object.
Public Function createEmptyDatabase() As String
Dim sRtrn As String ' For results
Dim cat As ADOX.Catalog = New ADOX.Catalog()
Try ' Try to run the following code and catch errors
cat.Create(dbProvider & dbNameLoc)
sRtrn = "Successfully Created A Database"
Catch ex As Exception ' Catches errors
sRtrn = ex.Message
Finally ' Once the Try statement has finished do some more stuff
cat = Nothing ' Dispose of any opened objects
End Try ' Finished Trying
Return sRtrn ' Return the results to the calling sub.
End Function ' Created Empty DataBase
The button click event for creating the database wont need System.Object to test if the file exist as ADOX.Catalog.Create raises and exception if a file with the same name already exists. Eliminating Try statements and Conditional tests like the IF statement reduces the amount of code you have to write. You will also notice that the code contains a region which we will use to contain all of the button events. As mentioned earlier, these are very useful as the amount of code begins to increase. Also as a note, if MsgBox doesn’t work within your code, change it to MessageBox.Show.
Place a button on the main form, rename it to btnCreateDatabase, change the text to “Create Database” and double click the button. Move the button event into the region set aside for button events.
MsgBox(createEmptyDatabase()) ' Call The function and display the results
Build your project, press the Create Database button and see if the myDatabase exists in the root of your C: drive. Press the button again and see what message you get. For a little more excitement you might want to try creating the database several times. Instead of deleting it from the folder lets do it programmatically with VB.Net. This is a simple bit of code.
This will be done within the button click event, so place a button on the main form, rename it to btnDeleteDatabase, change the text to “Delete Database” and double click the button. Move the button event into the region set aside for button events. System.IO.File.Exists tests to see if the file exists and returns a Boolean so we are going to test the results within an IF statement since System.IO.File.Delete does not throw an exception. If the file does not exist then we don’t want to try and delete a non-existing file.
Try
If System.IO.File.Exists(dbNameLoc) Then ' Test if file exists
System.IO.File.Delete(dbNameLoc) ' File exists, delete file
MsgBox("Database Deleted")
Else ' Else file was not found
MsgBox("Database Not Found") ' File not exists
End If ' End of If System.IO.File.Exists
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Build your project, press the Create Database button and you should get a message box pop up telling you that the file already exists. Press the Delete Database button, you should get the message box telling you that the file was deleted. Press the Delete Database button again. Did you get the message that you expected?
Return to “VB.Net And Databases”
Continue on to “How To Create 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.