Changing Data with a Cursor

The ADO, OLE DB, and ODBC application programming interfaces (APIs) support updating the current row the application is positioned on in a result set. The fundamental process is to:

1. Bind the result set columns to program variables.
2. Execute the query.
3. Execute API functions or methods to position the application on a row within the result set.
4. Fill the bound program variables with the new data values for any columns to be updated.
5. Execute one of these functions or methods to insert the row:
   • In ADO, call the Update method of the Recordset object.
   • In OLE DB, call the SetData method of the IRowsetChange interface.
   • In ODBC, call the SQLSetPos function with the SQL_UPDATE option.

When using a Transact-SQL server cursor, you can update the current row by using an UPDATE statement that includes a WHERE CURRENT OF clause. Changes made with this clause affect only the row on which the cursor is positioned. When a cursor is based on a join, only the table_name specified in the UPDATE statement is modified. Other tables participating in the cursor are not affected.

USE Northwind

GO

DECLARE abc CURSOR FOR

SELECT CompanyName

FROM Shippers

OPEN abc

GO

FETCH NEXT FROM abc

GO

UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.'

WHERE CURRENT OF abc

GO

CLOSE abc

DEALLOCATE abc

GO

For information about joins, see Join Fundamentals.

See Also