Issue with cursor

Hi

I have created a cursor with the following syntax: "DECLARE costs_cursor CURSOR SCROLL DYNAMIC FOR SELECT RegNumber, FirstName, LastName, Assessment, Catering, Travel, Accommodation, Other from dbo.T_Course_Data ORDER BY LastName OPEN costs_cursor" which works.

What I don't understand is why, when I attempt to update a value in the cursor, irrespective of whether I use the 'FOR UPDATE' option or not, I get the error message to the effect that the cursor cannot be updated because it is READ ONLY. Clearly (to my mind anyway) the cursor wasn't created as read only. My update statement is "Update dbo.T_Course_Data set Assessment='222' WHERE CURRENT OF costs_cursor"

The odd thing is I have another cursor in my app using the exact same statements and it doesn't give this error.

Please help if you can.

Neil

I beleive, the answer's here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_66sz.asp|||

The link in the other post explains the cursor conversions. Below is the SQL Server 2005 Books Online topic link:

http://msdn2.microsoft.com/en-us/library/ms190641.aspx

You can use the TYPE_WARNING option in the DECLARE CURSOR statement to detect these conversions in SQL Server 2005. See link below for more details:

http://msdn2.microsoft.com/en-us/library/ms180169.aspx

The best thing will be however to eliminate the use of cursor altogether to get better performance, cleaner code and maintainability. So post an example of what you are doing with the cursor and it will be easier to suggest a set-based solution.

|||

Hi

Thanks. Your previous answers helped me find the answer. I didn't have a unique index on the table and adding this has solved the issue.

Regards

Neil