A field that contains only NULL values is not copied into a new DataSet when you use the GetXml method to copy data from one DataSet to another DataSet (317961)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.Net 2.0
This article was previously published under Q317961

SYMPTOMS

When you use the GetXml method to copy data from one DataSet object to another, if any of the fields in the original DataSet contain only NULL (DbNull) values, these fields are not copied into the new DataSet.

CAUSE

This problem occurs because the DataSet.GetXml method does not include schema information. If you use another DataSet to read this output, the DataSet depends on the InferSchema value to recover schema information. However, because all of the values in the column are NULL, the column is absent from the GetXml output, and InferSchema cannot recover this column information.

For additional information about why attributes are not generated for fields that contain a NULL value, click the article number below to view the article in the Microsoft Knowledge Base:

296393 PRB: Attributes Are Not Generated for Fields That Contain a NULL Value When ADO Recordset Is Persisted in XML

RESOLUTION

To resolve this problem, use the Copy method instead of the GetXml method. Microsoft recommends that you use the Copy method because the Copy method does not convert the DataSet to Extensible Markup Language (XML) and back.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Start Microsoft Visual Studio .NET.
  2. Create a new Windows application in Visual Basic .NET.
  3. Make sure that your project contains a reference to the System.Data namespace; add a reference to this namespace if it does not.
  4. Drag a Button control and two DataGrid controls from the toolbox to Form1.
  5. Change the Name property of the Button control to btnTest, and then change the Text property of the Button control to Test.
  6. Use the Imports statement on the System.Data, the System.Data.SqlClient, and the System.IO 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.Data
Imports System.Data.SqlClient
Imports System.IO
  7. Add the following code in the btnTest_Click event:
            Dim myConnString As String = _
            "User ID=myUID;password=myPWD;Initial Catalog=Northwind;Data Source=mySQLServer"
        Dim mySelectQuery As String = "SELECT * FROM Customers WHERE Region IS NULL"
        Dim con As New SqlConnection(myConnString)
        Dim daCust As New SqlDataAdapter(mySelectQuery, con)
        Dim ds As New DataSet()

        daCust.Fill(ds, "Cust") 'Fill the initial DataSet.
        DataGrid1.DataSource = ds 
        DataGrid1.DataMember = "Cust"
        Dim ds1 As New DataSet()

        Dim sr As New StringReader(ds.GetXml)
        ds1.ReadXml(a)
        'Comment out the previous two lines and uncomment the next line 
        'to display columns with all NULL values.
        'ds1 = ds.Copy()

        DataGrid2.DataSource = ds1
        DataGrid2.DataMember = "Cust"
  8. Modify the connection strings as appropriate for your environment.
  9. Save your project. Click Start on the Debug menu to run your project.
  10. Click Test. Notice that the Region column appears in the first data grid and not in the second.

    Resolution

  11. Comment out the lines of code that use the GetXml method as follows:
            'Dim sr As New StringReader(ds.GetXml)
        'ds1.ReadXml(a)
  12. Uncomment the line of code that uses the Copy method as follows:
            ds1 = ds.Copy()
  13. Save your project. Click Start on the Debug menu to run your project again.
  14. Click Test. Notice that the Region column appears in both of the data grids.

REFERENCES

For additional information about how to write and how to read XML data from a DataSet, click the article numbers below to view the articles in the Microsoft Knowledge Base:

308064 HOW TO: Persist an ADO.NET DataSet as XML by Using Visual Basic .NET

309702 HOW TO: Read XML Data into a DataSet by Using Visual Basic .NET

For additional information about ADO.NET, click the article number below to view the article in the Microsoft Knowledge Base:

313590 INFO: Roadmap for ADO.NET

Modification Type:MinorLast Reviewed:3/9/2006
Keywords:kbDataAdapter kbManaged kbprb kbSystemData KB317961 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.