Re: recordset not opening even though query analyzer returns resul

I forgot to mention that I have added set nocount on in my sp, this was the
only way i could get an earlier version of it to return in a recordset.

My sp takes around 50 inputs but only requires 5 for the sp to run.
The sp then creates a temporary table which is an exact replica of one of
the tables, then i do an insert select statement to populate the temporary
table with all the records from the table. Then based on the 5 required
parameters (these are bit datatype) deletes from the temporary table if its
true. Its worth mentioning that if this was all the sp did it works and I
get a recordset in my VB code.

However because there are 45 other parameters that could be entered the sp
does something else.

It is for a financial program and based on the parameters removes certain
mortgages from the list based on the adverse data supplied to the sp. So a
maximum of 15 ccjs can be passed to the sp, but each ccj has 3 types of
information associated with it a value, a date registered, a date it was
satisfied.

So the next stage in my sp is to fetch the remaining records in my temp
table into a cursor and add up th evalues of the ccjs based on values in the
table. If ccj1 is £120 but the row in the database which corresponds to a
mortgage ignores ccjs up to £150 and any ccjs older than 24 months or
satisifed over 12 months ago then its not counted. so if this was the case
for all 15 ccjs the mortgage is valid to the client and stays in the temp
table, the next row is fetched and the same is done on that row, if the
criteria entered doesn't fit for the row then it is deleted from the temp
table, once all the rows have been matched the cursor is destroyed and a
select statement is issued returning the results.

so the above is performed in a loop with a series of nested ifs

Then after the select is issued the temp table is dropped

finally

When i test the sp in query analyzer I get the results I expect, when I
execute the sp in my vb code for some reason the recordset refuses to open
and its down to the loop and series of nested ifs I perform.

The only thing I can think that is causing problems is the while loop and
nested ifs because if i remove this section form my sp it works in vb. But
if its returning a resultset in QA why wont it in vb, is there another
setting like nocount that i need to use. Or am I using the wrong parameters
to open the recordset, I just assume I can use a firehose cursor as I only
want to put the results into a grid

thanks for the help


"William (Bill) Vaughn" wrote:

> This is by design. As I discuss (in great detail) in my books, when you
> execute a query the SQL engine returns a resultset. You use the Recordset
> object to handle these. If the resultset has a rowset, the Recordset's State
> is adStateOpen. If no rows were returned, you still get an "open" Recordset.
> However, if you execute an SQL command that does not return a rowset (like
> an UPDATE command), your Recordset tells you there is no rowset by setting
> the State property to adStateClosed.
>
> To remedy this, the easiest way is to turn off these rowsetless resultsets
> by adding SET NOCOUNT ON in your SP. I don't suggest this as it discards the
> "rows affected" value returned by the action command. Instead, you can
> simply step to the next resultset returned by your query-- Set RS =
> Rs.NextResult (or somesuch).
>
> hth
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> www.betav.com
> www.sqlreportingservices.net
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
>
> "steven scaife" <stevenscaife@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:691F66FD-18C1-421A-BAF0-A1303A1EDDB0@xxxxxxxxxxxxxxxx
> >I have a query that runs a stored procedure which returns a resultset in
> > query analyzer but when run in vb doesn't open a recordset
> >
> > I have tried set rs = conn.execute(strSQL)
> > and
> > rs.open strSQL, conn, adOpenForwardOnly , adLockReadOnly
> >
> > i get an error saying operation not allowed when object is closed
> >
> > I dont understand why it isn't opening when I get results from the same sp
> > in query analyzer
> >
> > Does anyone have any ideas if you need more information just ask
>
>
>
.

Relevant Pages

  • Re: recordset not opening even though query analyzer returns resul
    ... I'm convinced that VB can execute this complex SP. ... > only way i could get an earlier version of it to return in a recordset. ... > table into a cursor and add up th evalues of the ccjs based on values in ... >> execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • recordset not opening even though query analyzer returns resultset
    ... I have a query that runs a stored procedure which returns a resultset in ... query analyzer but when run in vb doesn't open a recordset ...
    (microsoft.public.vb.database.ado)
  • Re: Fastest db methods?
    ... side-effects, such as scalability issues (for example, a SqlDataReader is ... > Reading a recordset using SqlDataAdapter.Fill/ reading a ... recordset using SqlDataReader ?? ... and then send the actual resultset. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordset closes immediately
    ... the exact same query in the Query analyzer to see what goes wrong. ... also check to see that connection is still alive after you have performed ... finished working with your recordset. ... but I have tested the stored procedure using ...
    (microsoft.public.vb.database.ado)
  • Re: Missing Resultset When Calling an Stored Procedure w/a Nested
    ... we tried advancing to the next recordset by using the NextRecordSet ... program only returned the nested stored procedure call resultset and failed ...
    (microsoft.public.data.ado)