Returning a recordset

John Pennington · January 25th, 2006, 04:30 PM

Can someone tells me the appropriate conditions for returning a adodb.recordset from a function in VB6?

I keep getting datatype errors. Does it need to be variant to pass the recordset?
------------------------------

Sub subtest10()
    Dim rstTarget As ADODB.Recordset
    ' Set rstTarget = New ADODB.Recordset - This line does not make a difference. With or without I get a type mismatch.
    subDBMainInitPrimaryDatabase
    Set rstTarget = funcGetHistVolume(#12/1/2005#, #12/10/2005#, 39, connAdo1)
    'Call subDebugDisplayRst(rstTarget)
    subDBClosePrimaryConnection
End Sub
---------------------------
Function funcGetHistVolume(dtmValidStartCurrent As Date, dtmValidStopCurrent As Date, intHistGroup As Integer, connTargetConn As ADODB.Connection)
    '\\THIS FUNCTION WILL HIT THE TARGET PRIMARY DATABASE AND REUTRN THE VOLUMES BETEEN THE PARAMETER DATES
    '\\THIS SUB WILL RETURN THE RECORDSET
    '\\RST WILL BE TWO FIELDS, (1) DATE AND (2) VOLUME

    Dim rstTarget As ADODB.Recordset
    Dim strSql1 As String
    Set rstTarget = New ADODB.Recordset

    strSql1 = "SELECT * from tblHistMirrorCMSDvdn;"
    rstTarget.Open strSql1, connTargetConn, adOpenStatic, adLockReadOnly

    Call subDebugDisplayRst(rstTarget)
    funcGetHistVolume = rstTarget

    'rstTarget.Close
    'Set rstTarget = Nothing
End Function

John Pennington
 
United Parcel Service
w(404) 828 6934
c (770) 714 5975

vinod_yadav1919 · January 25th, 2006, 07:32 PM

Hii
Hii John Pennington!!
Try to change --> Call subDebugDisplayRst(rstTarget)

to ---> subDebugDisplayRst rstTarget

Cheers :)

vinod

vinod_yadav1919 · January 25th, 2006, 07:47 PM

My asp code working fine with
set conn = Server.CreateObject("ADODB.Connection")
conn.CommandTimeout = 0
conn.ConnectionTimeout = 0
conn.Open connectionstr
conn.cursorlocation=3
set rs=conn.execute("Select top 10 * from temptable")
test rs
Public Sub Test(rs)
response.write ("Total records"&rs.RecordCount)
do while not rs.eof
response.write ("Firstname"& rs.fields(2).value &"<br>")
rs.movenext
loop
End Sub

Hope this will help you

Cheers :)

vinod

John Pennington · January 27th, 2006, 11:27 AM

The problem is not passing a recordset into a sub or function, it is returning the recordset from the function.

This statement

Set rstTarget = funcGetHistVolume(#12/1/2005#, #12/10/2005#, 39, connAdo1)

always returns a type mismatch error.

where the following are the defining statements.

Dim rstTarget As ADODB.Recordset
Set rstTarget = New ADODB.Recordset

Removing the set statement did not make a difference.
Any ideas?

John Pennington
 
United Parcel Service
w(404) 828 6934
c (770) 714 5975

gbianchi · January 27th, 2006, 11:40 AM

hi there..

maybe you need this changes:

Code:

Function funcGetHistVolume(dtmValidStartCurrent As Date, dtmValidStopCurrent As Date, intHistGroup As Integer, connTargetConn As ADODB.Connection)  as ADODB.Recordset
    '\\THIS FUNCTION WILL HIT THE TARGET PRIMARY DATABASE AND REUTRN THE VOLUMES BETEEN THE PARAMETER DATES
    '\\THIS SUB WILL RETURN THE RECORDSET
    '\\RST WILL BE TWO FIELDS, (1) DATE AND (2) VOLUME

    Dim rstTarget As ADODB.Recordset
    Dim strSql1 As String
    Set rstTarget = New ADODB.Recordset

    strSql1 = "SELECT * from tblHistMirrorCMSDvdn;"
    rstTarget.Open strSql1, connTargetConn, adOpenStatic, adLockReadOnly

    Call subDebugDisplayRst(rstTarget)
     set funcGetHistVolume = rstTarget
    'of course dont close it because it will return close, but you can set this temp to nothing
    Set rsttarget = nothing
    'rstTarget.Close
    'Set rstTarget = Nothing
End Function

HTH

Gonzalo

nalla · January 31st, 2006, 12:43 AM

Hi John Pennington,

Did you set the function's return type as ADODB.Recordset
Instantiate rstTarget // Set rstTarget = New ADODB.Recordset

nalaka hewage

mattycas · March 12th, 2008, 06:02 PM

You need to make the recordset disconnected.

After you open it set the ActiveConnection property to nothing.

Function funcGetHistVolume(dtmValidStartCurrent As Date, dtmValidStopCurrent As Date, intHistGroup As Integer, connTargetConn As ADODB.Connection)
    '\\THIS FUNCTION WILL HIT THE TARGET PRIMARY DATABASE AND REUTRN THE VOLUMES BETEEN THE PARAMETER DATES
    '\\THIS SUB WILL RETURN THE RECORDSET
    '\\RST WILL BE TWO FIELDS, (1) DATE AND (2) VOLUME

    Dim rstTarget As ADODB.Recordset
    Dim strSql1 As String
    Set rstTarget = New ADODB.Recordset

    strSql1 = "SELECT * from tblHistMirrorCMSDvdn;"
    rstTarget.Open strSql1, connTargetConn, adOpenStatic, adLockReadOnly
    'Add this line
    Set rstTarget.ActiveConnection = Nothing

    Call subDebugDisplayRst(rstTarget)
    Set funcGetHistVolume = rstTarget

    'rstTarget.Close
    'Set rstTarget = Nothing
End Function

BrianWren · March 13th, 2008, 03:03 PM

Just to clarify for you. When you do not specify the return type of a function in VB6, the type is
Variant.
You should always specify the return type.
Objects are handled by the OS in a way that keeps track of how many references there are to it.

When you do this:

Code:

    Dim rstTarget As ADODB.Recordset
    Set rstTarget = New ADODB.Recordset

the reference count for the object in memory that rstTarget refers to = 1. If you did this:

Code:

    Dim rstTarget As ADODB.Recordset

Code:

    Dim rstTwo As ADODB.Recordset
    Set rstTarget = New ADODB.Recordset
    Set rstTwo = rstTarget

that object in memory has 2 references to it. If you set rstTarget to Nothing,
the recordset would still exist. If you then set rstTwo to Nothing, the OS would
then destroy the object in memory. Objects are destroyed when their reference
count = 0.

So. You can do this to return a recordset:

Code:

Public Function XdX() As ADODB.RecordSet
    Set XdX = New ADODB.RecordSet
        XdX.Open . . .
End Function

and you would return the recordset that was created with the New statement, and opened
with the .Open method. Even if you didn't open the recordset, you could still pass the unopened recordset created with the New statement this way.

Some people find that a little too terse for comfort. So you can do this instead:

Code:

Public Function XdX() As ADODB.RecordSet

    Dim r As ADODB.RecordSet
    Set r = New ADODB.RecordSet

    r.Open . . .
    Set XdX = r
    Set r = Nothing  ' This is highly recommended.  it makes it clear that
                     ' youâre through with the reference, and avoids relying
                     ' on âautomaticâ behavior.  Itâs possible that future
                     ' versions of VB will end implicit release of object
                     ' variables.  Not likely but itâs really better practice.
End Function

mad2max · August 18th, 2008, 08:37 AM

I know this is an old post.
I was looking for an answer and found it in the post.

Not really an answer, but a clue.

You have to set the variable in the calling fucntion and also the returning function, which will then pass the record set.

----------------------------------------------------------
Sub ListUsers
Dim userRS

set userRS = GetUsers(...)

End Sub

Function GetUsers(...)
Dim userlistRS

.... Build the record set ...

set GetUsers = userlistRS '''I was missing this SET
End Function
----------------------------------------------------------

Hope this helps,
Regards
NK