Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure 
Author Message
 Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure

What SQL statement should I use to open a recordset returned by a SQL Server
stored procedure

Current code:
rst.Open "Exec ProcName, cnn, adOpenKeyset, adLockOptimistic

This does not open the recordset.

Jim B



Sat, 01 Oct 2005 05:47:04 GMT  
 Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure
Jim

1. One of the ways is to use the command object

With objCmd
    .CommandText = "NameoftheSP""
    .CommandType = adCmdStoredProc
    'if there parameters append them
    .Parameters.Append .CreateParameter("Address",
adVarChar, adParamInput, 50, txtBoxMinorAddress.Text)
End With

Set objRS=objCmd.Execute

--
Regards

Sukesh Hoogan

http://personal.vsnl.com/sukesh_hoogan


Quote:
> What SQL statement should I use to open a recordset returned by a SQL
Server
> stored procedure

> Current code:
> rst.Open "Exec ProcName, cnn, adOpenKeyset, adLockOptimistic

> This does not open the recordset.

> Jim B

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.471 / Virus Database: 269 - Release Date: 10/04/2003


Sat, 01 Oct 2005 10:47:16 GMT  
 Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure
Hi,

Just addition to Sukesh's posting. If you have complex SP, which includes
variables declaration and different SQL statements, not just SELECT one,
then you would need to add SET NOCOUNT ON as a very first statement inside
that SP to see opened recordset in ADO

--
Val Mazur
Microsoft MVP


Quote:
> Jim

> 1. One of the ways is to use the command object

> With objCmd
>     .CommandText = "NameoftheSP""
>     .CommandType = adCmdStoredProc
>     'if there parameters append them
>     .Parameters.Append .CreateParameter("Address",
> adVarChar, adParamInput, 50, txtBoxMinorAddress.Text)
> End With

> Set objRS=objCmd.Execute

> --
> Regards

> Sukesh Hoogan

> http://personal.vsnl.com/sukesh_hoogan



> > What SQL statement should I use to open a recordset returned by a SQL
> Server
> > stored procedure

> > Current code:
> > rst.Open "Exec ProcName, cnn, adOpenKeyset, adLockOptimistic

> > This does not open the recordset.

> > Jim B

> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.471 / Virus Database: 269 - Release Date: 10/04/2003



Sat, 01 Oct 2005 20:09:51 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ADO against SQL Server - empty string returned as a space

2. ADO against SQL Server - empty string returned as a space

3. Sql server stored procedures not returning a recordset...?

4. Returning keyset/dynamic recordsets when executing parameterized queries against Stored Procedures

5. SQL stored procedure returning unusual ado recordset

6. Returning a recordset from SQL Server stored procedure

7. Returning keyset/dynamic recordsets when executing parameterized queries against Stored Procedures

8. Returning keyset/dynamic recordsets when executing parameterized queries against Stored Procedures

9. Stored Proc Return values / Output Params w ADO and SQL Server 7

10. Stored Proc Return values / Output Params w ADO and SQL Server 7

11. Stored Proc Return values / Output Params w ADO and SQL Server 7

12. Stored Proc Return values / Output Params w ADO and SQL Server 7

 

 
Powered by phpBB® Forum Software