Re: recordset not opening even though query analyzer returns resultset

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: data type error in my query
    ... the resultset returned the same value for comments. ... FROM dbo.tblWeblog as W1 LEFT OUTER JOIN dbo.tblUserComments as C ... > If blogID is a unique identifier for rows of tblWeblog, ... If I strip out W.blogComment the query runs fine. ...
    (microsoft.public.sqlserver.programming)
  • Re: When do selects execute for multiple resultsets
    ... SQL Server executes each query and stops when its ... When the first resultset is ... The CommandTimeout is measured from the time you execute the query until the ...
    (microsoft.public.dotnet.framework.adonet)
  • refreshing a window
    ... the query is processed, the results do not come up at the bottom until ... private JTextField output, input, input1, input2, input3; ... ResultSet resultSet; ...
    (comp.lang.java.programmer)
  • Re: Cant get JDBC to work from Java to MySQL
    ... ResultSet rs = stat.executeQuery("SELECT draw_date FROM ... but process int 'ball_1' ... (Java, Stack trace, and table description - stack trace from the code ... SQL Exception:Unexpected exception encountered during query. ...
    (comp.lang.java.programmer)
  • Re: Group By
    ... If you are looking to do the eqivalent of a GROUP BY in a SELECT query ... resultset and issue queries using the QueryADataSet assembly. ... > I could solve this using multiple queries and differnt filters, ...
    (microsoft.public.dotnet.framework.adonet)