Microsoft KB Archive/168295

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

Article ID: 168295

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Visual InterDev 1.0 Standard Edition

-



This article was previously published under Q168295



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:

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



Steps to Reproduce Behavior
 Open/Create Web project. Add data connection to SQL database (pubs for this example). Create a new table named "Test1". 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. 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.</li> Save and close the table design window.</li> Create a second table named "Test2".</li> Add one column named "cText".</li> Uncheck "Allow Nulls" and make the cText column the primary key.</li> Save and close the table design window.</li>  Add the following data to the Test1 table: cText   nID test     1 test2    2 test3    3 test4    2 test5    2 </li>  Add the following data to the Test2 table: cText test test2 test3 test4 test5 </li> 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.</li>  Type the following SQL into the "SQL Pane":      SELECT      Test2.cText, Test1.nID      FROM         Test2 INNER JOIN Test1 ON Test1.cText= Test2.cText                    </li> Run the query.</li> In the "Results Pane," change one of the "2"'s in the nID column to a "5".</li> Run the query.</li></ol>

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.

<div class="references_section">