Help: multiple select statements in SQL stored proc called by ADO 
Author Message
 Help: multiple select statements in SQL stored proc called by ADO

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




Sun, 12 Nov 2000 03:00:00 GMT
 Help: multiple select statements in SQL stored proc called by ADO

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

Quote:

>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





Sun, 12 Nov 2000 03:00:00 GMT
 Help: multiple select statements in SQL stored proc called by ADO

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



Sun, 12 Nov 2000 03:00:00 GMT
 Help: multiple select statements in SQL stored proc called by ADO

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.



Mon, 13 Nov 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. SOLUTION: Multiple Select Statements in Stored Proc

2. Multiple Select Statement in Stored Proc

3. Calling a stored Proc within a Select statement

4. Multiple Step Error When Calling Oracle Stored Proc w/ ADO 2.5+ And oraoledb Provider

5. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

6. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

7. CAN ANYONE HELP?...returning 2 recordsets to VB in one stored proc call through ADO

8. execute stored proc from select statement in SQL Server 7.0

9. Selecting XML into an ADO Stream using multiple select statements

10. Multiple SQL Insert statements in a single ADO.NET Command Object call

11. multiple parameters with ADO and SQL Server 2000 Stored proc

12. Stored Proc Calling Another Stored Proc


 
Powered by phpBB® Forum Software