Re: Ado Recordset and stored procedure(SQL server 2000)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> wrote in message
news:OHlijM3$GHA.4292@xxxxxxxxxxxxxxxxxxxxxxx

"chris" <chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:071A04A1-E170-45FA-BC0F-65040DB637FE@xxxxxxxxxxxxxxxx
Good day:

I am having the most difficult time with what should be a simple task.

I want to fill a recordset using a stored procedure. The recordset does
not
even open. The error message is ...cannot complete operation on closed
recordset...

Dim rs as new adodb.recordset
rs.open "SP_GetTriplicate '10/30/2006 09:00:01', '10/31/2006 09:00:00',
3",cnn, adOpenKeyset, adLockOptimistic, adCmdStoredProc

do while not rs.eof
something
rs.movenext
loop

I have also tried the techniques with the ado command object and
parameters
to no avail.

The problem is that your stored procedure is returning multiple
recordsets,
not all of which contain records. Add the line:

SET NOCOUNT ON

to the top of your stored procedure, to prevent the non-row-returning
statements from generating empty recordsets.

Alternatively, you could call rs.NextRecordset until it returns a
recordset
object for which rs.State = 1, but that would just be meaningless overhead
in this case. (It's useful when the stored procedure or SQL statement
actually does return multiple recordsets.)

An aside that has nothing to do with your problem specifically, you should
avoid naming your stored procedures with "sp_" as a prefix, unless you
want
a procedure to be callable from any database (and are willing to incur the
slight amount of extra overhead associated with this naming convention.)


-Mark

<snipped>

LOL

I totally missed multiple recordsets.

Talk about one's Doh! moments.

-ralph


.

Relevant Pages

  • How to you page and sort large dataset result in datagrid?
    ... Is there a good way to page/sort VERY large Result set in datagrid ... " Here's a stored procedure that I use for sorting and paging large ... or even thousands, of records, but working with larger recordsets with ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored procedures vs. views for filling record sources
    ... A stored procedure is generally more secure, ... I often use Views for updateable recordsets ... because they are normally easier to bind and modify etc, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Timeout problem
    ... This stored procedure retrieves 4 recordsets, one of the recordset is about 3k raws. ... It does look like the timeout happens during increased users activity on the SQL server. ... > there anything we can do to increase timeout period without modifying source ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: OLE error 80040200 when assigning events of a TCustomADODatasetpointingto a multi-select stored
    ... > recordsets will inherit properties. ... characteristics of the stored procedure that is causing the multiple ... declare @Temp int ...
    (borland.public.delphi.database.ado)
  • Re: Multiple recordsets continued
    ... My next step is to build this technique into my ... > Why would getting one recordset composed of two recordsets result in a ... In cases where field structure is the same will Unions or multiple ... sharing its CPU time with nothing? ...
    (comp.databases.ms-access)