Article source code and database: recipebook.zip
I am a beginner. I wanted to share some experiences with
other beginners. The first frustration we have is finding a book that teaches
us what we want to learn. When looking at beginners books we all want to avoid
the books that are too simple, but can't yet crack the secret code the upper
level books. I think there is some secret language that developers talk in; C#
or something like that. The next few articles that come from my keyboard will
be about my experience with teaching myself to develop applications in VB.NET.
The first thing I did was go out and get a copy of Visual
Basic.NET. Then I decided that I would tackle two easy projects to start with.
I learn best by doing, so choosing an actual project is necessary for me. The
first project was a cook book, or recipe application. I eventually want to make
a simple help desk.
The Recipe Application
For all you experts out there, move along, this is a beginner
writing to other beginners. The first step to creating an application is to
define what you want it to do. There are some basic features I want from my
first ever Visual Basic.NET application and there are others that would be fun
to try and add after I have learned more.
- Store Recipes
- List Ingredients and Instructions
- Be searchable by Keyword, Ingredient, Preparation Time, or Calories **
- Limit recipes and searches to those within a category (beef, chicken, breakfast, etc)
- Allow the user to add, delete or update recipes
(** Not included in version 1.0)
Not the most thrilling application, but it should present several key
learning points. To complete this application we will have to learn how to
take input, retrieve information, display information, store information, and
build an installer.
Where to Begin?
That is the question that all beginners have. I spent the
better part of four days looking at the various and assorted books at my local
super book store and cafe. I settled on Beginning Visual Basic .NET Databases by Forgey, Gosnell, and Reynolds and Murach's Beginning Visual Basic .NET by Anne Prince. These two books, I thought, would enable me to understand some
of the basics of VB.NET and using databases with VB.NET.
The Murach book was an excellent source for the basics with
source code for each application all contained on one page or several pages in
a row. It was a good thing to not have to hunt through several pages of theory
to find scattered code examples each time I had to check my code. Chapter 14
was dedicated to XML and provided me some insight into why I would want to
possibly use an XML file to store the recipe data. The book was not in depth
enough to show if I could do searches based on parts of the XML file or not. My
design specs called for searching and this book did not assist me beyond the
very basic parts of XML. I am a beginner, but there should have been more. I
then moved on to the chapters about relational databases. Chapters 16, 17 and
18 in Murach's Beginning Visual Basic .NET were excellent for getting me
into the basics of how to program an application with ADO.NET. I adopted the
instructions in these chapters to work with a local access database instead of
an SQL server.
The Recipe Application

The Murach book showed me how to create a
Connection, Data Adapter, and a Dataset in Chapter 17. In Chapter 18 it
discussed how to bind controls to the data and then use bound controls to
update, add, and delete data rows. Parameterized queries tied everything
together for the Recipe Application. As you can see below I have created two
DataAdapters and a Dataset for each. I need to keep the Category table and
the Recipe Table separate. There may be a better way to do this, but this
worked.

One of the most important things I learned from the book is that
you must fill the dataset. With VB.NET you are not working directly with the
data like you would using MS Access. You are working with a 'picture' of the
data created by your SQL statements in the Data Adapter and then filled into
the Dataset. You can load the data in any event of the form - a button click, a
combo box selection or a form load event. In most cases you will load the data
in the form load as seen below.
Code:Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DaCategory.Fill(DsCategories1)
DaRecipes.Fill(DsRecipes1)
Me.SetEntryControls(False)
Me.SetMaintenanceButtons(True)
RecipesBindingManager = Me.BindingContext(DsRecipes1, "Recipes")
btnEdit.Enabled = False
lblCategoryText.Visible = False
txtCategory.Visible = False
End Sub
The important lines in filling the data are
the two listed in red. The other line that was critical to using the combo box
to navigate records was the line in green. The binding manager tracks the
position or row of data that the application is currently viewing or
potentially editing. You should also notice the Me.SetEntryControls(False)
line of code. This is calling another sub routine that sets the enabled or
disabled status of certain controls on the form. Each control could be listed
individually, but if you have several controls that are always going to be
enabled or disabled as a group then making a separate sub for that update will
make it easier for you to enable and disable them throughout your application.
Code:Private Sub SetComboBoxControls(ByVal bComboMode As Boolean)
cboCategory.Enabled = bComboMode
cboRecipe.Enabled = bComboMode
End Sub

Actually binding data to a control is very easy in the IDE. Just select the control on
the form and then go to the properties pane and select the DataSource and the
display member. These will both be drop downs and the datasets you have created
will be selectable. The DataSource is the name of your Dataset.Table (so in the
case of my category combo box the DataSource was DsCategories.Categories. The
Display member is the actual field in the table that you want to be bound to
that field and displayed. In the Recipe application that was category. In order
to get this the selected category has to be passed to the DataAdapter that
populates the Recipe DataSet. To do that I used the code below:
Code:Private Sub cboCategory_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCategory.SelectedIndexChanged
If bNewRow Then
txtCategory.Text = cboCategory.Text
Else
DsRecipes1.Clear()
DaRecipes.SelectCommand.Parameters("Category").Value = cboCategory.Text
DaRecipes.Fill(DsRecipes1)
cboRecipe.Focus()
End If
End Sub
The code above clears and refills DsRecipes1. The line in red
sends a value to the DataAdapter which is parameterized. The remainder of the
application uses the binding manager and bound controls to navigate through the
data, edit the data or add new data.
The important thing to remember when you are trying
to add, delete, or edit data is that your application is working with its own
DataSet not the actual database. To update the data you must
use a command to take the current dataset and update the database with it. Here
is the code that I used to accomplish this:
Code:Private SubUpdateDatabase()
DaRecipes.Update(DsRecipes1.Recipes)
End Sub
Any code that updates the database uses the above code
by containing Me.UpdateDatabase()
. This code is
included in the update and delete button code of the Recipe Application code.
Below is the code for the delete button so you can see it in action:
Code:Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim iResult As DialogResult = MessageBox.Show("Delete " & txtTitle.Text & " ?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If iResult = DialogResult.Yes Then
RecipesBindingManager.RemoveAt(RecipesBindingManager.Position)
Me.UpdateDatabase()
cboCategory.SelectedIndex = -1
Me.SetMaintenanceButtons(True)
Me.SetEntryControls(False)
Me.SetComboBoxControls(True)
cboCategory.Focus()
btnDelete.Enabled = True
End If
End Sub
Adding data is done by first clearing
the fields and then using the BindingManager to add a new row. Then the user
must fill out the text fields and click the update button. The code for the Add
button and Update button is below:
Code:Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
txtTitle.Text = ""
DsRecipes1.Recipes.Clear()
RecipesBindingManager.AddNew()
bNewRow = True Me.SetEntryControls(True)
Me.SetMaintenanceButtons(False)
Me.SetComboBoxControls(False)
cboCategory.Enabled = True
txtCategory.Visible = True
lblCategoryText.Visible = True
txtTitle.Focus()
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
If ValidData() Then
RecipesBindingManager.EndCurrentEdit()
Me.UpdateDatabase()
If bNewRow Then
cboRecipe.SelectedIndex = RecipesBindingManager.Count - 1
bNewRow = False
End If
Me.SetEntryControls(False)
Me.SetComboBoxControls(True)
btnUpdate.Enabled = False
Me.SetMaintenanceButtons(True)
EditMode = False
cboCategory.Focus()
End If
txtCategory.Visible = False
lblCategoryText.Visible = False
End Sub
That covers the basics of how to use your data
connections, data adapters, and data sets to retrieve, navigate, add, delete
and update data. I hope that these simple
instructions have helped you gain a better understanding of how to manipulate a
DataSet. Please feel free to write me or post your comments here about your experiences. There will be
more of these beginner type articles as well as book reviews from a beginner's
perspective to help guide those of us that are new to the world of Visual
Basic.