1. Tech

Your suggestion is on its way!

An email with a link to:

http://visualbasic.about.com/od/learnvbnetandadonet/l/aa051703c.htm

was emailed to:

Thanks for sharing About.com with others!

Using ADO .NET - Changing Databases - Part 4
3 - Binding and Changes

Now that we're full of confidence from this exercise in coordinating code with the results of wizards and dialogs, let's update the database with a whole new class built into ADO .NET. called the Binding class. In Part 3, Binding was a key part of making the wizards do their job. In VB .NET and ADO .NET, Binding has been expanded a great deal from the way it was implemented in the old ADO. Binding is actually a .NET object and it can be found in the namespace System.Windows.Forms.Binding. We'll use this properties and methods in this class to update the database. For more background, you might want to look this up in your VB .NET Help system.

To update the database, we will first have to replace the Label components with something we can change. We'll use a TextBox. And to keep things simple, we will only add one Textbox for the artTitle field in the database. We will also need a Button component to send the change to the database. Drag across all of the Label components to select them and then delete them. You can also delete all of the code to process the artDate and artURL fields too since we won't use them in this example. Then add a TextBox and a Button. My Form looked like this after these changes:

New Form

All of the code to change artTitle in the database will be in the Click event for the Button component. First, we'll go over the bare minimum to do that and then we'll discuss some additional coding that is recommended.

The bare minimum code in the Click event is as follows:

Me.BindingContext(Me.DataSet11, "Articles").EndCurrentEdit()
Me.OleDbDataAdapter1.Update(Me.DataSet11, "Articles")
Me.DataSet11.AcceptChanges()

BindingContext is an object we haven't seen before in our program. And you didn't even declare or initialize it! In fact, you did do that when you set the properties of the TextBox1 control. One of the real advantages of VB .NET over VB 6 is that all of the code is there and accessible. In the hidden Region at the top of the program, VS .NET added a statement that creates the binding (I broke the statment into three lines to make it easier to read).

Me.TextBox1.DataBindings.Add( _
	New System.Windows.Forms.Binding("Text", _
	Me.DataSet11, "Articles.artTitle"))

The Binding objects maintain the relationship between a control (like TextBox1) and a datasource (like our Articles database). Changing (or adding or deleting) a record in a datasource involves several steps. First, the DataSet is changed and then the changed records from the DataSet are updated to the datasource. The EndCurrentEdit method tells the BindingContext object that editing is complete for the current record. Then the task of saving the new Record to the underlying database from the DataSet can be done by calling the Update method to save the DataSet changes back to the database.

To observe the effect of failing to call EndCurrentEdit, comment out the statement and run the program. Everything appears to work since the contents displayed in the ListBox and TextBox change. But if you close and rerun the program (or examine the database in Access) and you'll see that no changes have been made to the datasource (the Articles table in the database). You have to call EndCurrentEdit or the methods in the Binding class think you're still changing the content of the DataSet. This gives you the ability to make changes to the DataSet in several places and then send them to the database at the right time.

Finally, the AcceptChanges method confirms the changes to the database. This may seem like a lot of trouble. A lot of new programmers think, "Why can't this just be done with one statement? Like, 'Send to database' or something?" Well ... it could. For example, you could put these three statements inside a subroutine call and then just call the subroutine. But you lose flexibility and reliability when you do that. Database types talk about "two phase commit" and "three phase commit" protocols and these things make a lot of difference. The well designed methods in ADO .NET make it possible. For more information about this, try Databases.About.Com. Or, if you want to get in even deeper, here's an article about committing transactions from Microsoft.

The main thing that you should add to your program to upgrade it, however, is exception handling. In the program from Part 2 of this series, we used Try-Catch blocks to intercept exceptions and report them. Although no structured exception handling has been included in this example program (partly to keep the example as simple and streamlined as possible for you), it's something that definitely must be part of any professional system. Here's a Microsoft reference about exception handling that explains more than you probably wanted to know about it.

See the next page for downloads and the complete code.

Next page > The Code and Download > Page 1, 2, 3, 4

©2014 About.com. All rights reserved.