Return a recordset from a COM to an ASP page

[Brad]

Could anyone please tell me the correct way to destroy the objects created
when returning a recordset from a COM component. Whenever I destroy the object,
the recordset is returned empty.

Thanks

[Chad]

Brad,
In your component setup a temporary Recordset. Use this to open and gather
information as you would normally. Then when your are ready set your function
equal to the tmp recordset and kill the the temp one....example:

Public Function myFunction(var1,var1.....) as ADODB.Recordset
Dim myRS
'Open RS etc..
myRS.open sSQL, dbConn
'If Open then
set myFunction = myRS
If not myRS is nothing then
set myRs = nothing
End if
End Function






"Brad" <dilbert@ync.net> wrote:
>
>Could anyone please tell me the correct way to destroy the objects created
>when returning a recordset from a COM component. Whenever I destroy the
object,
>the recordset is returned empty.
>
>Thanks

[Kevin MacCallum]

A more concise method is to use the function name directly as it can be use
inside itself as a variable of the type it returns - in this case an ADODB.Recordset.

Example

Public Function GetaRS() As ADODB.Recordset
Set GetaRS = New ADODB.Recordset
With GetaRS
.CursorLocation = adUseClient
.Open "Select * from table", "yourconnectionstring", adOpenForwardOnly,
adLockReadOnly, adCmdText
Set .ActiveConnection = Nothing
End With

End Function

In a COM component you generally want to return a disconnected recordset
so be sure and set CursorLocation = adUseClient and the active connection
to nothing

Kevin M

"Chad" <chadtheis@hotmail.com> wrote:
>
>Brad,
> In your component setup a temporary Recordset. Use this to open and gather
>information as you would normally. Then when your are ready set your function
>equal to the tmp recordset and kill the the temp one....example:
>
>Public Function myFunction(var1,var1.....) as ADODB.Recordset
>Dim myRS
>'Open RS etc..
> myRS.open sSQL, dbConn
>'If Open then
> set myFunction = myRS
> If not myRS is nothing then
> set myRs = nothing
> End if
>End Function
>
>
>
>
>
>
>"Brad" <dilbert@ync.net> wrote:
>>
>>Could anyone please tell me the correct way to destroy the objects created
>>when returning a recordset from a COM component. Whenever I destroy the
>object,
>>the recordset is returned empty.
>>
>>Thanks
>

[Brad]

"Chad" <chadtheis@hotmail.com> wrote:
>
>Brad,
> In your component setup a temporary Recordset. Use this to open and gather
>information as you would normally. Then when your are ready set your function
>equal to the tmp recordset and kill the the temp one....example:
>
>Public Function myFunction(var1,var1.....) as ADODB.Recordset
>Dim myRS
>'Open RS etc..
> myRS.open sSQL, dbConn
>'If Open then
> set myFunction = myRS
> If not myRS is nothing then
> set myRs = nothing
> End if
>End Function
>
>
>
>
>
>
>"Brad" <dilbert@ync.net> wrote:
>>
>>Could anyone please tell me the correct way to destroy the objects created
>>when returning a recordset from a COM component. Whenever I destroy the
>object,
>>the recordset is returned empty.
>>
>>Thanks
>


I am doing that but the recordset still comes back empty

[Jim Edgar]

Brad --

I use a little bit different approach and it seems to work well for me.
I create a com object that has a 'objInitiaze' method that I call from
my code. This method creates a db connection and returns true
if successful. I have a GetResultset (I'm using RDO) method that
passes a variable to the method and looks like this:

{Warning: pseudo-code}
Public Function GetResultset(ByRef rs As Resultset, _
ByVal sSQL as String) As
Long
' Create the resultset using the global db database object
db.Execute rs, sSQL

If Not rs Is Nothing Then
rs.MoveLast
GetResultset = rs.Recordcount
rs.MoveFirst
Else
GetResultset = 0
End If
End Function

The function returns the number of records found matching
the sSQL statement. You can modify it to return error codes
or raise an error but the point is that you don't have to set
the rs to Nothing within the com object. I also have an
'objCleanup' method that I call before releasing the object
which sets the Connection object to Nothing. From your code
you can use something like:

Sub LoadRecords()
Dim obj As New MyObject, rs As rdoResultset
Dim lRet As Long
If obj.objInitialize Then
' Get the records
lRet = obj.GetResultset(rs, "Select * From Employees")
If lRet > 0 then
' load records
While Not rs.EOF
cboEmployees.AddItem rs!FName & " " & rs!LName
rs.MoveNext
Wend
End If
obj.objCleanup
End If
Set obj = Nothing
Set rs = Nothing
End Sub

HTH

Jim Edgar

"Brad" <dilbert@ync.net> wrote in message news:39f5de52$1@news.devx.com...
>
> "Chad" <chadtheis@hotmail.com> wrote:
> >
> >Brad,
> > In your component setup a temporary Recordset. Use this to open and
gather
> >information as you would normally. Then when your are ready set your
function
> >equal to the tmp recordset and kill the the temp one....example:
> >
> >Public Function myFunction(var1,var1.....) as ADODB.Recordset
> >Dim myRS
> >'Open RS etc..
> > myRS.open sSQL, dbConn
> >'If Open then
> > set myFunction = myRS
> > If not myRS is nothing then
> > set myRs = nothing
> > End if
> >End Function
> >
> >
> >
> >
> >
> >
> >"Brad" <dilbert@ync.net> wrote:
> >>
> >>Could anyone please tell me the correct way to destroy the objects
created
> >>when returning a recordset from a COM component. Whenever I destroy the
> >object,
> >>the recordset is returned empty.
> >>
> >>Thanks
> >
>
>
> I am doing that but the recordset still comes back empty