FIX: Updating Single Row Replaces Data in All Rows of a Cursor

ID: Q168295

The information in this article applies to:

SYMPTOMS

When you change the value of a single column and single row in a cursor, the update replaces all rows of the cursor with the new value. This issue arises when you use a two-table cursor and one of the tables has a compound key index.


CAUSE

The cursor does not output all table columns used in the definition of the table's compound key.


RESOLUTION

Make sure all columns in a compound key are included in a cursor.


STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug has been corrected in Visual Studio 97, Service Pack 1.

For additional information about the Visual Studio 97 Service Pack 1, please see the following article in the Microsoft Knowledge Base:

Q170365 INFO: Visual Studio 97 Service Packs - What, Where, and Why


MORE INFORMATION

Steps to Reproduce Behavior

  1. Open/Create Web project.


  2. Add data connection to SQL database (pubs for this example).


  3. Create a new table named "Test1".


  4. Add two columns to the Test1 table, one column named "cText" and the other named "nID". Both fields should not allow nulls and be of type char,10 and int,4 respectively.


  5. Create a Primary key based on both the cText and nID columns by selecting both column names with the mouse while holding down the CTRL key and choosing the key icon from the "Table" toolbar. A key icon should appear next to both column names.


  6. Save and close the table design window.


  7. Create a second table named "Test2".


  8. Add one column named "cText".


  9. Uncheck "Allow Nulls" and make the cText column the primary key.


  10. Save and close the table design window.


  11. Add the following data to the Test1 table:
    
      cText    nID
      test      1
      test2     2
      test3     3
      test4     2
      test5     2


  12. Add the following data to the Test2 table: 
    
      cText
      test
      test2
      test3
      test4
      test5


  13. Double-click the Test1 table to bring up the Query Designer. Open all four of the panes ("Diagram Pane", "Grid Pane', "SQL Pane", and the "Results Pane") in the Query Designer from the "Query" toolbar.


  14. Type the following SQL into the "SQL Pane": 
    
      SELECT      Test2.cText, Test1.nID
      FROM         Test2 INNER JOIN Test1 ON Test1.cText= Test2.cText


  15. Run the query.


  16. In the "Results Pane," change one of the "2"'s in the nID column to a "5".


  17. Run the query.


Result: All of the rows that had a "2" now have a "5," even though you only updated one of the rows.

NOTE: The following select is the root of the problem, because it contains only one column (Test1.nID) of a compound (two or more column) key index in the output field list.


REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

http://support.microsoft.com/support/vinterdev/


Keywords          : kbother kbADO kbVisID100 kbVisID600fix kbVS97sp1fix kbVS97sp2fix kbGrpASP 
Version           : WINDOWS:1.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: May 10, 1999