MOD2000: Only One Record Returned When You Change Data Control SQL Statement (230451)


The information in this article applies to:

  • Microsoft Office 2000 Developer
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.

MORE INFORMATION

Steps to Reproduce Behavior

  1. In Microsoft Word 2000, create a new document.
  2. On the View menu, point to Toolbars, and then click Control Toolbox.
  3. 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.
  4. In the Control Toolbox, click the More Controls control, and then click Microsoft DataGrid Control to insert a data grid control into the document.
  5. In the Control Toolbox, click the Command Button control to insert a new command button control into the document.
  6. Right-click the ADO data control, point to ADODC Object on the shortcut menu, and then click ADODC Properties.
  7. On the General tab, click to select Use Connection String, and then click the Build button.
  8. In the OLE DB Provider(s) list, click Microsoft OLE DB Provider for SQL Server.
  9. 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.
  10. In the Select a database on the server list, click the sample pubs database, and then click OK.
  11. 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

  12. 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.
  13. Add the following code to the Private Sub CommandButton2_Click() procedure:
    Set DataGrid1.DataSource=ADODC1
  14. On the File menu, click Save. On the File menu, click Close and Return to Microsoft Word.
  15. In the Control Toolbox, click the Exit Design Mode button.
  16. Click the command button. Examine the data grid and note that all records from the authors table appear.
  17. In the Control Toolbox, click the Design Mode button.
  18. Right-click the ADO data control, point to ADODC Object, and then click ADODC Properties.
  19. 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

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

REFERENCES

For more information about the ADO data control, start the MSDN Library for Microsoft Office 2000 Developer, type ADO Data Control on the Index tab, and press ENTER to view "ADO Data Control in Office 2000 Applications."
Modification Type:MajorLast Reviewed:8/12/1999
Keywords:kbbug KB230451
©2002 Microsoft Corporation. All rights reserved.