Re: Recordset Closed After Having Just Being Opened


"Karnifexx" <m.p.fletcher@xxxxxxxxxxxxxx> wrote in message
news:1166284258.836597.257540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hey Mark,
Many thanks for that. Could you possibly elobrate what you mean
by 'pass the command object to a recordset as the source parameter'. I
have tried something similar to

sqlRS.Open command, connection etc

You must set the command.ActiveConnection to an already-opened connection
object, and omit the connection parameter when calling recordset.Open:

Set command.ActiveConnection = connection
sqlRs.Open command, , [other params]


-Mark



Regards,
Marc

Mark McGinty wrote:

"Karnifexx" <m.p.fletcher@xxxxxxxxxxxxxx> wrote in message
news:1165775895.072920.158370@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Yes SQLserver with an access ADP 2003 frontend.

You are creating a "firehose", a read-only, forward-only recordset. If
the
procedure returns 0 rows, there would be nothing else you could do with
it -- can't insert, got no rows... nothing left to do but close it, so it
does that for you.

If you need a different kind of cursor, pass the command object to a
recordset as the source parameter (along with desired options.) If you
just
need to detect that this is the case, test the recordset.State property;
as
long as the recordset has been created, it will always be valid to
reference.


-Mark




Regards,
Marc

Ralph wrote:

"Karnifexx" <m.p.fletcher@xxxxxxxxxxxxxx> wrote in message
news:1165761611.777992.72620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hey,
If someone could help me with this it would be greatly
appreciated.
I have the following code, part of a function (irrevlant and
parameter
declaration has been removed). Suffised to say the function works
most
of the time and a recordset is usually returned. However sometimes,
as
is commented the recordset is closed after the execute statement.
What
is causing this to happen?

Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset

' Establish connection.
Set sqlRS = New ADODB.Recordset
Set sqlCmd = New ADODB.Command
Set sqlConn = New ADODB.Connection

sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open

sqlConn.CursorLocation = adUseClient 'Cache data locally

' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = ExecuteSP.Name
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters....(removed)
Set sqlRS = .Execute()
End With

'Here the SP has executed correctly but the sqlRS is closed ;(
???!?!?!?


Just a quick comment...

This is a common phenomena when using ADO to call a stored procedure
if
no
data is returned. But the exact details escape me at the moment. IIRC
there
are possible changes that can be made to the SP itself to protect
against
this. (depending on provider/database of course).

I'll do a little research and get back to you. But for the moment it
will
help to assure you that no major 'bug' is at work here. <g>

What database are you using? SQLServer?

-ralph




.

Relevant Pages

  • Re: More ASP.Net Newbie Questions
    ... The Command is then what you're doing with this connection, ... In regards to your final point, making grids and controls in general do ... > Connection and Recordset objects into, like, 37 different things. ...
    (microsoft.public.dotnet.framework.aspnet)
  • ADO recordset closed, cannot read
    ... puts it into the spreadsheet using the CopyFromRecordset command. ... I was using an ADODB Recordset object but I ... I changed it to a plain ADO connection instead. ... 'Initialize parameters ...
    (microsoft.public.excel.programming)
  • Re: insert Q
    ... Use an explicit Connection object/ ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ... A Connection object implements the connection to the database. ...
    (microsoft.public.inetserver.asp.db)
  • Re: How to fill a Client Side Recorset with a command (sp exec)
    ... To be able to open recordset on a client side using Command, ... CursorLocation property of the Connection to adUseClient ... > command from a connection, but i´m having problems with the parameters. ...
    (microsoft.public.data.ado)
  • Re: Recordset Closed After Having Just Being Opened
    ... by 'pass the command object to a recordset as the source parameter'. ... Set sqlRS = New ADODB.Recordset ...
    (microsoft.public.vb.database.ado)