Hi, How can I have disconnected recordset from running a stored procedure
and at the same time can get the return value from the output parameters
defined in the stored procedure?
> I've just encountered the same problem and fortunately found a very neat
> solution on the Web.
> '************************************************************************
***
> ********************
> 'Disconnected ADO recordset.
> 'A disconnected recordset is created by
> ' - specify the CursorLocation property as adUseClient.
> ' - then create the recordset as usual
> ' - set the ActiveConnection to Nothing, which disconnects it from
the
> server.
> ' about maintaining the connection.
> '************************************************************************
***
> ********************
> This is function that I created and it works.
> Add some errors checking
>
>
> Public Function ExecDisconnectedRS(a_SQL As String) As ADODB.Recordset
> '************************************************************************
***
> ********************
> 'Disconnected ADO recordset.
> 'A disconnected recordset is created by
> ' - specify the CursorLocation property as adUseClient.
> ' - then create the recordset as usual
> ' - set the ActiveConnection to Nothing, which disconnects it from
the
> server.
> ' You can then pass the whole recordset to the destination
application
> without having to worry
> ' about maintaining the connection.
> '************************************************************************
***
> ********************
>
> Dim lRs As New ADODB.Recordset
> With lRs
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
> .ActiveConnection = myConnectStr
> .Source = a_SQL
> .Open
> .ActiveConnection = Nothing
> End With
>
> Set ExecDisconnectedRS = lRs
> Set lRs = Nothing
> End Function
>
> create a string to execute your store procedure like:
>
> lStrSql = "exec dbo.vstrSPName " & a_Parameter
> set yourRS = ExecDisconnectedRS(lStrSql)
>
> -----Original Message-----
> From: Selvaraju, Parimel [mailto:selvarap@a...]
> Sent: Thursday, May 24, 2001 9:42 AM
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] Re: Disconnected Recordsets (ADO 2.0)
>
>
> Well, the situation I'm trying to get disconnected recordset is using the
> command object. I run a stored procedure which returns a recordset and I
> disconnect from the database. This is where I lose my recordset.
>
> lobjComm.CommandType = adCmdStoredProc
> lobjComm.CommandText = vstrSPName
> Set lobjComm.ActiveConnection = mobjConn
> Set lobjRS = New ADODB.Recordset
> lobjRS.CursorLocation = adUseClient
> lobjRS.CursorType = adOpenStatic
> lobjRS.Open lobjComm
> Does this mean I can't have a disconnected recordset if I use the command
> object? Is this realted to the OLE-DB provider?
>
> Thanks,
> -Parimel
>
> -----Original Message-----
> From: Pete Davis [mailto:pdavis@q...]
> Sent: Wednesday, May 23, 2001 7:04 PM
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] Re: Disconnected Recordsets (ADO 2.0)
>
>
> Basically, it means this: If you have a disconnected recordset, and you
> close the connection, you still have the records in the recordset. They
are
> no longer "connected" to the underlying database. You can do whatever you
> want with them. There are then issues regarding "reconnecting" the data
to
> the database if you want to then update it.
>
> We use disconnected recordsets a great deal. Here's an example: We have a
> COM+ system. All access to our database is done through COM+ components
on
> the server. The client application, itself, never deals directly with the
> database. If the client wants to retrieve data from the database, it
calls a
> component, which retrieves the recordset, disconnected, and returns that
> recordset to the caller.
>
> The connection is freed by the COM+ component, and generally, the COM+
> component is freed by the client. The COM+ component is, therefore,
> stateless. This means, not only is the connection now available for
another
> user, but so is the COM+ component. If later, the user wants to make an
> update, they would then re-instantiate the COM+ component, pass the
updated
> recordset, and that component would then re-connect it to the database
and
> perform the update.
>
> Pain in the butt, but very efficient and scalable.
>
> Pete
>
> ----- Original Message -----
> From: "Parimel" <selvarap@a...>
> To: "ActiveX_Data_Objects" <activex_data_objects@p...>
> Sent: Wednesday, May 23, 2001 8:17 PM
> Subject: [activex_data_objects] Disconnected Recordsets (ADO 2.0)
>
>
> > Hi
> > I have a very basic question on disconnected recordset.
> > What does it really mean?
> > Does it mean it is disconnected from the connection object, so that
> > the "connection object" is available for other processes
> > OR
> > Does it mean it has no connection whatsoever to the database and
> > the "connection" is available for other processes
> > (Note diff. in the quoted words)
> > Take the sample code of disconnected recordset
> >
> > Set mobjConn = New ADODB.Connection
> > mobjConn.Open "DSN=mydsn"
> > Set robjRS = New ADODB.Recordset
> > robjRS.ActiveConnection = mobjConn
> > robjRS.CursorLocation = adUseClient
> > robjRS.CursorType = adOpenStatic
> > robjRS.Open "Select * from something" , , , adLockReadOnly
> > ***Set robjRS.ActiveConnection = Nothing**
> > ''''mobjConn.Close
> > Now, If I close the connection after getting my recordset(uncomment the
> > last line), then I lose all the records in the recordset, which means
the
> > recordset still has the knowledge of the database information and holds
> > the connection in some form.
> >
> > I use ADO 2.0 and I'm fairly confident that this holds good for higher
> > versions as well.
> >
> > Please correct me if I am going wrong on the understanding of
disconnected
> > recordset.
> >
> > -Parimel
>
>
View Cart
