Microsoft KB Archive/230451

= MOD2000: Only One Record Returned When You Change Data Control SQL Statement =

Article ID: 230451

Article Last Modified on 8/12/1999

-

APPLIES TO


 * Microsoft Office 2000 Developer Edition

-



This article was previously published under Q230451



Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
After you change the CommandText property of a Microsoft ADO data control, the control returns only one record, regardless of how many records the modified SQL statement should return.



RESOLUTION
In your Visual Basic for Applications code, refresh the data control before you bind additional controls to it, for example: 'In this example the control Adodc1 is refreshed before data source 'for the data grid is set. Private Sub CommandButton1_Click Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 End Sub



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
 In Microsoft Word 2000, create a new document. On the View menu, point to Toolbars, and then click Control Toolbox. In the Control Toolbox, click the More Controls control, and then click Microsoft ADO Data Control to insert a data control into the document.

NOTE: Click in the Word document to move the focus from the inserted control to the document after you insert each control. In the Control Toolbox, click the More Controls control, and then click Microsoft DataGrid Control to insert a data grid control into the document. In the Control Toolbox, click the Command Button control to insert a new command button control into the document.</li> Right-click the ADO data control, point to ADODC Object on the shortcut menu, and then click ADODC Properties.</li> On the General tab, click to select Use Connection String, and then click the Build button.</li> In the OLE DB Provider(s) list, click Microsoft OLE DB Provider for SQL Server.</li> Click Next and enter the name of a valid SQL Server or Microsoft Database Engine (MSDE) with an appropriate user name and password to be used to access the server.</li> In the Select a database on the server list, click the sample pubs database, and then click OK.</li> In the ADODC Properties dialog box, click the RecordSource tab, and in the Command Type list, click 8:-adCmdUnknown. Add the following text to the Command Text (SQL) box:

Select * From authors

</li> Click OK, and then right-click the command button. Click View Code on the shortcut menu.

Note that the Visual Basic Editor opens, and that a new procedure named Private Sub CommandButton2_Click is created.</li>  Add the following code to the Private Sub CommandButton2_Click procedure: Set DataGrid1.DataSource=ADODC1 </li> On the File menu, click Save. On the File menu, click Close and Return to Microsoft Word.</li> In the Control Toolbox, click the Exit Design Mode button.</li> Click the command button. Examine the data grid and note that all records from the authors table appear.</li> In the Control Toolbox, click the Design Mode button.</li> Right-click the ADO data control, point to ADODC Object, and then click ADODC Properties.</li> In the ADODC Properties dialog box, click the RecordSource tab, and change the value in the Command Text (SQL) box to the following:

Select * from titles

</li> Click OK to accept the changes, and then close the ADODC Properties dialog box.</li> Click the Exit Design Mode button, and then click the command button in your document.</li> <li>Note that only one record appears in the data grid even though the titles table contains 18 records.</li></ol>

<div class="references_section">