Wednesday, March 28, 2012

MDAC 2.8 and cursor FETCH NEXT

it seems that starting with MDAC 2.8 the FETCH NEXT statement for cursors requires an INTO clause. otherwise an unspecified error is returned.
so it's not possible to scroll through the records anymore without storing the values of the fields into local variables?Can you post the error code/description ?|||the following code executes from a connection object without issues using ado 2.6 and 2.7:

DECLARE c8281 CURSOR FOR
SELECT * From Users WHERE ( Users.User_ID = 17 )
OPEN c8281
FETCH NEXT FROM c8281
UPDATE [Users] SET
User_Name = 'Unknown'
WHERE CURRENT OF c8281
CLOSE c8281
DEALLOCATE c8281

however, if I use ADO 2.8 it returns error code 80004005 (unspecified error) and native error = 0. furthermore, it actually executes at the database server.

I made it work without errors by modifying the code in the following way:

DECLARE @.username VARCHAR
DECLARE c8281 CURSOR FOR
SELECT User_Name From Users WHERE ( Users.User_ID = 17 )
OPEN c8281
FETCH NEXT FROM c8281 INTO @.username
UPDATE [Users] SET
User_Name = 'Unknown'
WHERE CURRENT OF c8281
CLOSE c8281
DEALLOCATE c8281

which is closer to ansi92 I guess. but I just really need to scroll through the cursor. no need for local variables.

No comments:

Post a Comment