FIX: Updating Single Row Replaces Data in All Rows of a Cursor
ID: Q168295
|
The information in this article applies to:
-
Microsoft Visual InterDev, version 1.0
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
- 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.
- Save and close the table design window.
- Create a second table named "Test2".
- Add one column named "cText".
- Uncheck "Allow Nulls" and make the cText column the primary key.
- Save and close the table design window.
- Add the following data to the Test1 table:
cText nID
test 1
test2 2
test3 3
test4 2
test5 2
- Add the following data to the Test2 table:
cText
test
test2
test3
test4
test5
- 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.
- Type the following SQL into the "SQL Pane":
SELECT Test2.cText, Test1.nID
FROM Test2 INNER JOIN Test1 ON Test1.cText= Test2.cText
- Run the query.
- In the "Results Pane," change one of the "2"'s in the nID column to
a "5".
- 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