Advanced Basics: Handling Null Values with Controls

Article
10/22/2019

Advanced Basics

Handling Null Values with Controls

Ken Spencer

Code download available at:AdvancedBasics0303.exe(113 KB)

Q In Visual Basic® .NET, the textbox and checkbox controls do not accept null values. Is there a way to modify these controls in order to be able to accept nulls?

A This is a good question. To answer it, I had to do a bit of research and testing. Luckily, you should find the solution applicable to any control you use. First I need to clarify what is meant by null. In the database itself a null value is simply represented by NULL. In Visual Basic .NET and ADO.NET, null is mapped into a special value, DBNull. This distinguishes DBNull from the .NET null value, Nothing. In this column, I'll only be dealing with DBNull.

In thinking about the problem, two approaches came to mind. One was to use inheritance and derive a new control from an existing control, then modify the new control to handle nulls. The other approach was to create what I would call a composite-type control that contains the desired functionality. To test each, I used one approach for the checkbox and one for the textbox.

Let's look at the textbox first as this control uses the composite approach, which is similar to control development in Visual Basic 6.0. In order to create this control, I built a new Windows® Control Library project. I renamed the default control TextBoxNoNull and made the file name for the class TextBoxNoNull.vb.

Next, I sized the user control to 96 × 20. In order to turn this control into a textbox, I added a textbox control to the user control. Then I set the size of the textbox so that it would be exactly the same size as the user control.

Now, the fun begins. The code I created for the textbox control is shown in Figure 1. The first step in generating the code was figuring out how to deal with the null values. If you try to place a null value (such as DBNull) in a string, an error is thrown. This makes for a bit of a challenging task when you try to handle nulls in a control because if the property you are setting happens to be a string or anything other than an object type, an error will be thrown when you try to set the property to DBNull.

Figure 1 Null Textbox

Public Shadows Property text() As Object Get Return txtBoxStandard.Text End Get Set(ByVal Value As Object) Try If IsDBNull(Value) = True Then txtBoxStandard.Text = "" Else If Len(Value) = 0 Then txtBoxStandard.Text = "" Else txtBoxStandard.Text = Value End If End If Catch exc As Exception txtBoxStandard.Text = "Incorrect value type " & exc.Message End Try End Set End Property Private Sub TextBoxNoNull_Resize(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.Resize Dim oControl As UserControl oControl = CType(sender, UserControl) txtBoxStandard.Width = oControl.Width txtBoxStandard.Height = oControl.Height End Sub Private Sub TextBoxNoNull_Layout(ByVal sender As Object, _ ByVal e As System.Windows.Forms.LayoutEventArgs) _ Handles MyBase.Layout Try txtBoxStandard.Width = e.AffectedControl.Width txtBoxStandard.Height = e.AffectedControl.Height Catch exc As Exception End Try End Sub

To handle this I created a new Text property of type object. Since the underlying control has a Text property, I had to create this new property with the Shadows keyword.

The code in the property procedure is pretty simple. The Get function simply returns the value of the child textbox. The Set procedure has the code to test for DBNull. The Try/Catch block is used to make sure any errors are caught and handled by throwing the error message. The If...Then block sets the text of the textbox to an empty string if the value is DBNull or empty; otherwise, the textbox is set to the new value.

Now, how do you keep the textbox sized to match the user control? Use the Resize event, right? Partially. This works for run-time changes to the control, but what about design-time changes? To change the textbox size in design mode, I used the Layout event to trap these changes. It contains almost the same code as the Resize event, except that it uses the AffectedControl class of the System.Windows.Forms.LayoutEventArgs passed to the event to extract the height and width of the control. This code would occasionally generate a runtime error, complaining that there is not a valid object. Using a dummy Try/Catch error handler to trap this error solved the problem.

Next, let's look at the checkbox and see how it can handle nulls. Instead of creating a simple composite control, I created the new checkbox by deriving from the standard checkbox (see Figure 2) by creating a new class and adding the following Inherits statement:

Inherits System.Windows.Forms.checkbox

This makes the new class behave exactly like the standard checkbox control. So far, so good.

Figure 2 Null Checkbox

Public Class checkNoNull Inherits System.Windows.Forms.CheckBox Private mChecked As Boolean Public Shadows Property Checked() As Object Get Return mChecked End Get Set(ByVal Value As Object) Try If IsDBNull(Value) = True Then mChecked = False Else If Len(Value) = 0 Then mChecked = False ElseIf Value = True Then mChecked = True Else mChecked = False End If End If Catch exc As Exception mChecked = False End Try MyBase.Checked = mChecked End Set End Property Private Sub checkNoNull_CheckedChanged( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles MyBase.CheckedChanged Checked = MyBase.Checked End Sub End Class

Next, I added the following line to declare a class-level variable to hold the checked state:

Private mChecked As Boolean

At this point I had to deal with the Checked property and figure out how to make it handle DBNull. Like the Text property in the textbox, I created a new Checked property of type Object. This property either returns or sets the mChecked variable. As with the textbox, this code checks to see if the new value is DBNull and handles it appropriately. Instead of returning empty strings or any type of error, this code simply returns True if the value is True; if the value is False, then it returns False.

Just after the end of the Try block, the following line was placed to set the Checked property of the base class to be equal to the current value of the local variable:

MyBase.Checked = mChecked

That takes care of handling values set in code, but what happens when the user enters a value by clicking the checkbox? To handle this action, I created an event handler for the CheckedChanged event. This event is provided by the underlying base class and occurs when a user clicks the checkbox. The only code in this event is the following, which sets the Checked property of the new checkbox class to the value the state caused by the user's click.

Checked = MyBase.Checked

Now, a word of caution. Patience is required when building controls. During the development of these controls, I ran into a stack overflow error. This caused all sorts of problems such as the host form refusing to load, the solution refusing to load, and so on. What caused this error? Instead of the line used at the end of the Try/Catch block, I had inadvertently used this line:

Me.Checked = mChecked

This actually just resets the Checked property of the new class in a recursive manner, causing the stack overflow. The moral? You should be careful with Me and MyBase since they can really affect the environment dramatically.

Let's discuss one more approach to handling nulls. The function in Figure 3 checks a variable to make sure it is valid and returns either a blank string or the value.

Figure 3 Check for Null

Function CheckNull(ByVal Value As Object) As String Dim outVar As String Try If IsDBNull(Value) = True Then outVar = "" Else If Len(Value) = 0 Then outVar = "" Else outVar = CStr(Value) End If End If Catch exc As Exception outVar = "" End Try Return outVar End Function

This function is easy to use, but where do you put it so you can reuse it? You can, of course, place it in a module for your project. But having to recreate the module each time is a pain. Instead, you can create a new macro and add this code:

Sub LoadGeneralModule() DTE.ItemOperations.AddExistingItem( _ "C:\Components\modDBGeneral.vb") End Sub

All this line does is take the file modDBGeneral.vb from the C:\Components folder and add it to the current project. To use this, I customize the toolbar and add an icon for this macro. Then it's one click to load the module whenever it's needed.

Handling null values is pretty simple in most cases. To do so, you can riddle your code with If statements or you can create routines or custom controls to handle the nulls. Nulls can be the result of many circumstances. The sample code for this column uses a function (GetCustomerRegion) that returns a single string using the Command object's ExecuteScalar method (see the link at the top of this article). If you set the return type to String, this function will generate an error if you try to return a value that is DBNull. So, you can put your error handling right there and you will not have any null values returned.

DataSets, on the other hand, can legally contain DBNull values. Not only does a DataSet need DBNull values, but the DataGrid also supports DBNull fields. If you try to set a textbox or checkbox to a value from either a DataSet or DataGrid and the value is DBNull, an error will occur. That's where these custom versions come in very handy, as they happily accept the DBNull value but then return either an error message or False. In production applications, I would change the checkbox to always return an empty string in cases where the value is blank or DBNull.

Remember, it's little things like null handling that make applications reliable and trusted.

Send your questions and comments for Ken to basics@microsoft.com.

Ken Spencerworks for 32X Tech (https://www.32X.com), where he provides training, software development, and consulting services on Microsoft technologies.

Additional resources