Need help DLL return recordset

[pookeTime]

I just wrote a simple function in DLL to return a recordset to ASP page. But when the asp page call the function, the dll throw an error on the line that I set the return recordset. Here is the sample code. (The error number that return is 0.!!)

*** VB ***
Public Function fn_getUser(rsUser As Variant) As String

Dim ConnObj As ADODB.Connection
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset

On Error GoTo Errfn_getUser

Set ConnObj = New ADODB.Connection
Set cm = New ADODB.Command
Set rs = New ADODB.Recordset

ConnObj.ConnectionString = sConnStr 'Constanct value
ConnObj.Open

With cm
.ActiveConnection = ConnObj
.CommandText = "sp_getUser"
.CommandType = 4
End With
rs.CursorLocation = adUseClient
rs.Open cm, , adOpenForwardOnly, adLockReadOnly

'This is where the problem is. The stored procedure always returns records, but the return record never get set. Moreover it return an error 0 here.
Set rsUser = rs

ConnObj.Close
Set cm = Nothing
Set rsGroup = Nothing
Set ConnObj = Nothing

Errfn_getUser:
fn_getUser = Err.Number & ": " & Err.Description
End Function
'******************

'***ASP Page********
dim obj, rsUser, msg
set obj=server.CreateObject ("VBCOM_General.IAdmin&quot
set rsUser= server.CreateObject("ADODB.recordset&quot
msg = obj.jobscanCache(rsUser)

response.write msg
'******************

'****browser display**
0:
'*****************

If i try to display data from the recordset

response.write rsUser("name&quot

The page will return an error say the recordset is close.

I wonder that I did any wrong on the dll code for not. Anyone have any idea.. Thank you..

 

[WilMead]

First of all you need an exit function before your error handler. You might want to add some success setting before exiting too. I usually end my functions with
[tt]
DoExit:
Exit Function
DoError1:
...
Resume DoExit ' or whatever
End
[/tt]
To see what is going on with the Record set I recomend putting a breakpoint on on the[tt]
Set rsUser = rs[/tt]
statement and watch rs as you step thru the statements following it. I think you'll find why the recordset turns up closed.

As far as the SP not returning data, I have to yield to someone who knows more than I.

Wil Mead
wmead@optonline.net

 

[Nagrom]

If you are returning the recordset from a function you can't close the recordset within that function. VB is actually passing a reference to that recordset back from the function, not a "copy" of the recordset. Just remove the code for closing the recordset from the function. When you close the recordset you return the value to (rsUser) it will close the recordset you open in the function (because in effect they are the same recordset). I think it is fine that you close the connection object in the function since you are using adClient for the CursorLocation, but you may have to leave that open as well (I really think it will be fine to close that, though).

 

[pookeTime]

Thank you. That works. I leave the recordset open and everything works just fine.

 

[Nagrom]

Cool beans. Glad I could help.