HOW TO: Make a Typed DataSet Return a Default Value Instead of DBNull by Using Visual Basic .NET

Article translations Article translations
Article ID: 318039 - View products that this article applies to.
This article was previously published under Q318039
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article describes how to change the XML Schema file to make a typed DataSet return a default value instead of DBNULL.

Visual Studio .NET may return an exception if the value of a DataRow is DBNULL, so you must handle the exception properly for values that are DBNULL. If you modify the default value for DBNULL fields, you can retrieve a value you specify other than DBNULL.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows XP, Windows 2000, or Windows NT 4.0 Service Pack 6a
  • Microsoft Data Access Components (MDAC) 2.6 or later
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual Basic .NET syntax
  • ADO.NET syntax
  • Windows Forms

Create the Project

  1. Open Visual Studio .NET.
  2. Create a new Windows application in Visual Basic .NET. Form1 is added to the project by default.
  3. Place a Command button on Form1. Change the Name property of the button to btnTest.
  4. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the General Declarations section of Form1:
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
  5. Use the Customers table in the Northwind database to create a typed DataSet.
  6. Paste the following code in the btnTest_Click event:
        SqlDataAdapter1.Fill(DataSet11, "customers")
        Dim crow As DataSet1.CustomersRow
        For Each crow In DataSet11.Customers
            If crow._Region = "-1" Then
                Console.WriteLine("DBNULL")
            End If
        Next
  7. Save your project.

Test the Project

  1. Run the project and then click btnTest. The following error message appears.
    An unhandled exception of type 'System.Data.StrongTypingException' occurred in nullvaluedbnull.exe

    Additional information: Cannot get value because it is DBNull.
  2. Stop the program and open the DataSet1.xsd file from Solution Explorer.
  3. In the DataSet1.xsd, add the namespace codegen in the namespace declaration. Paste the following line of code for the codegen namespace immediately after the targetNamespace value:
    xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
  4. Modify the element Region to reflect the following code sample:
    <xs:element name="Region" codegen:nullValue="-1" type="xs:string" minOccurs="0" />
  5. Save the DataSet1.xsd file. If you are prompted to reload the file, click Yes.
  6. Run the project and then click btnTest. The exception does not occur. Notice the DBNULLs that are printed in the output window.

Pitfalls

  • The nullValue must match the type of the element; for example, use nullValue="0" to return 0 for null integer fields.
  • The nullValue only retrieves data. You cannot use the nullValue to assign to a field.

REFERENCES

Using Annotations with a Typed DataSet

Properties

Article ID: 318039 - Last Review: September 4, 2003 - Revision: 2.1
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
Keywords: 
kbhowtomaster KB318039

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com