I'm using ADO to get a recordset back from a SQL server stored procedure. I
need to edit the contents of some of the fields in the recordset before the
data is passed up to the client.

Opening the recordset as dynamic allows field values to be edited, but
stored procedure calls then fail giving error "cant open a cursor on a
stored procedure that has anything other than a single select statement in
it" . The stored procedures only have one select statement that returns
data, but have others to assign values to local variables. If the ADO
recordset is opened in a read only mode, the error doesn't occur, but then I
can't edit the values.

Would appriciate any help on how I can work around this problem, or edit
values of recordsets some other way.

Cheeers,
 William

William,

I think the message means something else. If the stored procedure contains
only a single select statement, and no other statements of any type, then it
is just like a view and you can open a cursor on it. Except for this special
case, stored procedure result sets are not editable. You must use a view or
table in order to edit values.

You mention that you want to edit values before the data is passed to the
client. Are you trying to modify information just for viewing or are you
trying to send modifications back to the database? If you are modifying just
for viewing you may want to try putting the recordset contents in an array
and then modifying and displaying the array contents. You can't edit the
recordset because it is linked with the database. If you want to send
modifications to the database, you will have to use a table, view or data
modification statement.

Stephanie Giovannini

William,

Try adding SET NOCOUNT ON in the beginning of the proc.  My
understanding is that it will keep the commands that do not return
result sets from signaling the front end that they ran.

Roy

The reason I was trying to edit the recordset was to implement a security
model where some users don't have permission to read certain fields, so I
wanted to set those field values to null. Copying the recordset into an
array works fine, the reason I didn't do that before was I thought it was
inefficient to copy the data if you could edit the recordset. I didn't
realise you couldn't edit recordsets from a SP in general.

Thanks for your help,
 William.