This is part ten of a series about programming a SQL Server database using VB.NET and Visual Studio for people just starting to learn about programming databases. You can find the beginning here.
The idea behind this series is to demonstrate Visual Studio and SQL Server in easy steps so even beginners can use them. Too often technical articles either don't show beginners the latest tools, or else are written in language that only experienced programmers can understand. This series is different.
Part One developed a very simple app using the powerful DataGridView control but didn't include much explanation. That's because the next installments were all about the explanation of why it works. Now we're using DataGridView most of the time because it does work!
Parameter queries were the focus of the previous article. Visual Studio will generate a SQL Server parameter query automatically. The problem is that what you get is just barely functional and it's not clear how it should be used. This article will show you how you can update the generated code and make it much easier to use.
This article continues with the code that was created at the end of the previous article. To download the code, Click Here. (Note that this code does not include the database. But it's written to work with a very simple, two table SQL Server RecipeIngredients database described in previous lessons that you can create.)
Our starting code looks like this ...
Click Here to display the illustration
... and the form looks like this.
Click Here to display the illustration
There are a lot of non-obvious things about this form. Here are just a few examples.
- You have to explicitly click the Save Data button or any additions, changes or deletions are lost when the application ends and no warnings are displayed.
- It's not clear that you have to click in the DataGridView and make an entry after clicking the Add New button to actually add something. The Add New button is also completely unnecessary because you can do the same thing by clicking the asterisk in the DataGridView control.
- If you click the Save Data button without entering something in the new row of the DataGridView, the application simply crashes with a NoNullAllowedException error.
... and there are a lot of other quirky things. So you should change your application to customize it and eliminate a few of these problems. Let's start by looking at the generated code.
Notice that event code only exists for the RecipesBindingNavigatorSaveItem Click event of the Navigation ToolStrip. The Click events for the other items in the ToolStrip aren't exposed. That means that you can't change them directly. One problem to solve is to simply figure out where to add code to change the system. But before you can do that, you have to remove the association between an existing ToolStrip item and the action that is generated by Visual Studio. Let's work on the Delete ToolStrip item to see how that's done.
Briefly, the way you delete this association is to set the action property for the Delete item to none. But it's not obvious where to find it. The next two illustrations show how to do this.
Click Here to display the first illustration
Click Here to display the second illustration
Now that the generated association is gone, you need to add a new Click event for BindingNavigatorDeleteItem (the name assigned to the Delete button in the navigator ToolStrip) to accomplish a customized delete action. Here's some code that will do the trick:
Private Sub BindingNavigatorDeleteItem_Click( ByVal sender As System.Object, ByVal e As System.EventArgs ) Handles BindingNavigatorDeleteItem.Click If MsgBox("Are you sure?", MsgBoxStyle.YesNo, "Delete Recipe") = MsgBoxResult.Yes Then ' These three statements delete the row RecipesBindingSource.RemoveCurrent() RecipesBindingSource.EndEdit() RecipesTableAdapter.Update( RecipeIngredientsMToMDataSet.Recipes) End If End Sub
In this code, the DataGridView is refreshed and the deleted row is gone because RecipesTableAdapter.Update is called on the table in the DataSet. Always remember, the DataSet is just a snapshot of the actual database that has been created for your application.
The general process for modifying a ToolStrip are described on the next page.