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 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.

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.

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:

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:

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:

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:

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.