Microsoft KB Archive/308625

= The enumerated DataReader returns incomplete information for batch queries that have different schemas =

Article ID: 308625

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft ADO.NET 2.0
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic 2005 Express Edition
 * Microsoft .NET Framework 1.1

-



This article was previously published under Q308625



SYMPTOMS
If you create the DBEnumerator object on batch queries with different schemas, DBEnumerator returns incorrect or incomplete information. For example, if you run a batch query that contains two SELECT statements that return different schemas, the second statement may not return the entire schema.



CAUSE
This problem occurs because, by design, you can only use the DBEnumerator object with one result set.



RESOLUTION
To work around this behavior, obtain a DBEnumerator for each result set, and then call the NextResult method of a .NET data provider's DataReader object.



Steps to Reproduce the Behavior
 Start Microsoft Visual Studio .NET. Create a new Windows Application project in Visual Basic .NET. Form1 is added to the project by default. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not. Place a Button control on Form1. Button1 is added by default.  Use the Imports statement on the System, System.Data, System.Data.Common, System.Collections and System.Data.SqlClient namespaces so that you are not required to qualify declarations in those namespaces later in your code. Imports System Imports System.Data Imports System.Data.Common Imports System.Collections Imports System.Data.SqlClient   In the Code window, add the following code after the &quot;Windows Form Designer generated code&quot; region: Private Sub Button1_Click(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles Button1.Click

Dim cn As New SqlConnection( _      &quot;server=servername;uid=sa;pwd=;database=Northwind&quot;) cn.Open Dim cm As New System.Data.SqlClient.SqlCommand( _      &quot;SELECT FirstName FROM Employees WHERE EmployeeId = 1;&quot; & _       &quot;SELECT FirstName, LastName FROM Employees WHERE &quot; & _       &quot;EmployeeId = 2&quot;, cn)

Dim dr As System.Data.SqlClient.SqlDataReader dr = cm.ExecuteReader

Dim rEnum As System.Data.Common.DbEnumerator = _ CType(CType(dr, System.Collections.IEnumerable).GetEnumerator, _     DbEnumerator)

Debug.WriteLine(&quot;rEnum.MoveNext = &quot; & rEnum.MoveNext)

Dim record1 As DbDataRecord = (CType(rEnum.Current, DbDataRecord)) Debug.WriteLine(record1.FieldCount) Debug.WriteLine(&quot; The number of fields in this dataRecord &quot; & _      record1.FieldCount) Debug.WriteLine(&quot;The value of the first field is &quot; & _      record1.GetValue(0))

dr.NextResult

rEnum.MoveNext

Dim record2 As DbDataRecord = (CType(rEnum.Current, DbDataRecord))

' Note that the DataRecord only displays a field count of 1, ' which is incorrect Debug.WriteLine(&quot;The number of fields in this DataRecord &quot; & _      record2.FieldCount) Debug.WriteLine(&quot;The value of the first field is = &quot; & _      record2.GetValue(0))

dr.Close cn.Close End Sub </li> Modify the connection string as appropriate for your environment.</li> Save your project. On the Debug menu, click Start to run your project.</li> Click Button1. Press the CTRL+ALT+O key combination to view the Output window. Notice that the number of fields in the second DataRecord (record2) is 1, which is incorrect, and the LastName field is missing.</li> To work around this behavior, add LastName to the SELECT statement to change the schema of the first query so that it matches the second query. Notice that the number of fields is correct.</li></ol>

Additional query words: kbreadme

Keywords: kbprb kbreadme kbsqlclient kbsystemdata KB308625

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.