Re: Recordset Closed After Having Just Being Opened

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

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)
  • RE: Beginners explanation for API Dialog Box Code?
    ... I am getting the name of a file I want to copy a recordset ... 'Set filter to show only xls Spreadsheets ... If varGetSaveName = "" Then ... The code examples above go in the Click Event of the command button you will ...
    (microsoft.public.access.formscoding)
  • Re: Recordset Closed After Having Just Being Opened
    ... by 'pass the command object to a recordset as the source parameter'. ... You must set the command.ActiveConnection to an already-opened connection ...
    (microsoft.public.vb.database.ado)
  • 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)
  • 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)
Loading