Passing recordset back from function 
Author Message
 Passing recordset back from function

Hi! I have this problem about recordset:

I created a MTS object in VB. What I did is that I process all my SQL into
an object.
So after my object receives the SQL statement, it process the statement and
attempts to return the recordset back to the procedure:

Public Function ExecuteQuery() as ADODB.Recordset
                    .
                    .
                    .
Set Return_Recordset= SQLConnection.Execute(SQLQuery)

Set ExecuteQuery=Return_Recordset

End Function

This is not a problem. But when I run it using ASP like this:

Set Test = Server.CreateObject("SQL2.SQL")
Test.ConnectStr = "driver={SQL
SERVER};server=Proverbs;UID=ELASAdmin;PWD=password;database=ELAS"
   Test.QueryStr = "SELECT * FROM ELAS_UserProfile"
   Set recs = Test.ExecuteQuery()
   response.write(recs("Usr_ID"))

The ASP returns this error:

"The operation requested by the application is not allowed if the object is
closed. "

From what I investigate, the recordset 'recs' contains no data. I've tried
many ways to solve the problem but it doesn't seem to help.

Can anyone enlighten me on this?


Thanks for any help!



Sat, 20 Oct 2001 03:00:00 GMT  
 Passing recordset back from function
Have you tried changing the proc to not return a recordset, but rather
accept a reference to a recordset object that was created by the invoker?

Public Function ExecuteQuery(Return_Recordset As ADODB.Recordset) As Boolean

    On Error Goto ProcErr
    Dim MyStatusVar As Long, SQLQuery As String

    SQLQuery = ... 'whatever

    Set Return_Recordset= SQLConnection.Execute(SQLQuery)

ProcEnd:
    ExecuteQuery = Iif(MyStatusVar = 0, True, False)
    Exit Function

ProcErr:
    'Make MyStatusVar pick up the number of the last error
    MyStatusVar = Err.Number
    Goto ProcEnd

End Function

--
Alan Rueckgauer
Rueckgauer Systems
Windows NT & Internet Solutions
www.rueckgauer.com

NOTE:  I do *NOT* read or respond to email replies to newsgroup postings.
Please reply only in the newsgroup so everyone can benefit from the
discussion.

=====

Quote:

>Hi! I have this problem about recordset:

>I created a MTS object in VB. What I did is that I process all my SQL into
>an object.
>So after my object receives the SQL statement, it process the statement and
>attempts to return the recordset back to the procedure:

>Public Function ExecuteQuery() as ADODB.Recordset
>                    .
>                    .
>                    .
>Set Return_Recordset= SQLConnection.Execute(SQLQuery)

>Set ExecuteQuery=Return_Recordset

>End Function

>This is not a problem. But when I run it using ASP like this:

>Set Test = Server.CreateObject("SQL2.SQL")
>Test.ConnectStr = "driver={SQL
>SERVER};server=Proverbs;UID=ELASAdmin;PWD=password;database=ELAS"
>   Test.QueryStr = "SELECT * FROM ELAS_UserProfile"
>   Set recs = Test.ExecuteQuery()
>   response.write(recs("Usr_ID"))

>The ASP returns this error:

>"The operation requested by the application is not allowed if the object is
>closed. "

>From what I investigate, the recordset 'recs' contains no data. I've tried
>many ways to solve the problem but it doesn't seem to help.

>Can anyone enlighten me on this?


>Thanks for any help!



Sun, 21 Oct 2001 03:00:00 GMT  
 Passing recordset back from function
VBPJ April 1999 has an example of returning a record set using ADO.
Comparing theirs with what you posted shows your code to be lacking
a few statements.  Namely, you are not creating and opening a
connection in the function, as they did with theirs.

Ref: VBPJ April 1999 Pg 36: Listing 3

HTH
LFS



Sun, 21 Oct 2001 03:00:00 GMT  
 Passing recordset back from function
Thank you for the suggestion.

However, I may have forgotten. How do I retrieve the Return_Recordset in ASP
from the function?

In ASP, I tried it as this:

   Set Test = Server.CreateObject("SQL2.SQL")
   Test.ConnectStr = "driver={SQL
SERVER};server=Proverbs;UID=ELASAdmin;PWD=password;database=ELAS"
   Test.QueryStr = "SELECT * FROM ELAS_UserProfile"
   rt=Test.ExecuteQuery(rec)
   response.write(rec("Usr_ID"))

It gives me:

Type mismatch: 'Test.ExecuteQuery'

So how do you retrieve the recordset from the object and use it in my ASP
script?
Thank you for your help!

Regards,
Joseph


Quote:
>Have you tried changing the proc to not return a recordset, but rather
>accept a reference to a recordset object that was created by the invoker?

>Public Function ExecuteQuery(Return_Recordset As ADODB.Recordset) As
Boolean

>    On Error Goto ProcErr
>    Dim MyStatusVar As Long, SQLQuery As String

>    SQLQuery = ... 'whatever

>    Set Return_Recordset= SQLConnection.Execute(SQLQuery)

>ProcEnd:
>    ExecuteQuery = Iif(MyStatusVar = 0, True, False)
>    Exit Function

>ProcErr:
>    'Make MyStatusVar pick up the number of the last error
>    MyStatusVar = Err.Number
>    Goto ProcEnd

>End Function

>--
>Alan Rueckgauer
>Rueckgauer Systems
>Windows NT & Internet Solutions
>www.rueckgauer.com

>NOTE:  I do *NOT* read or respond to email replies to newsgroup postings.
>Please reply only in the newsgroup so everyone can benefit from the
>discussion.

>=====

>>Hi! I have this problem about recordset:

>>I created a MTS object in VB. What I did is that I process all my SQL into
>>an object.
>>So after my object receives the SQL statement, it process the statement
and
>>attempts to return the recordset back to the procedure:

>>Public Function ExecuteQuery() as ADODB.Recordset
>>                    .
>>                    .
>>                    .
>>Set Return_Recordset= SQLConnection.Execute(SQLQuery)

>>Set ExecuteQuery=Return_Recordset

>>End Function

>>This is not a problem. But when I run it using ASP like this:

>>Set Test = Server.CreateObject("SQL2.SQL")
>>Test.ConnectStr = "driver={SQL
>>SERVER};server=Proverbs;UID=ELASAdmin;PWD=password;database=ELAS"
>>   Test.QueryStr = "SELECT * FROM ELAS_UserProfile"
>>   Set recs = Test.ExecuteQuery()
>>   response.write(recs("Usr_ID"))

>>The ASP returns this error:

>>"The operation requested by the application is not allowed if the object
is
>>closed. "

>>From what I investigate, the recordset 'recs' contains no data. I've tried
>>many ways to solve the problem but it doesn't seem to help.

>>Can anyone enlighten me on this?


>>Thanks for any help!



Sun, 21 Oct 2001 03:00:00 GMT  
 Passing recordset back from function
Joseph,

Make sure to set the cursor to the client side, and then set the
activeconnection property of the recordset object to nothing before you
close the MTS object.

Remember to include the MTS libraries under project->references to get the
following example to work.

Try something like this:

Public Function GetGroups() As adodb.Recordset
Dim cSQL As String
Dim cConnection As String
Dim rs As New adodb.Recordset
Dim cn As New adodb.Connection

Dim objCtx As ObjectContext
Set objCtx = GetObjectContext()   'These are MTS calls.  Required to run
correctly in MTS

cSQL = "select * from property where CICN = '149925';"

'*** These are DSNless connection strings.  Please uncomment the one you
wish to use.           ***
'*** The help section on "connectionstring" will help you make a DSN based
connectionstring.    ***
'*** We plan on using DSN based connectionstrings, but the are machine
dependent.               ***
'*** and therefore pretty useless on a multi machine thingy like a demo
project.                ***

cConnection = "Provider=SQLOLEDB.1;Password=mikhael;Persist Security
Info=True;User ID=rthornel;Initial Catalog=PCSTest;Data Source=ILSTest1"
'cConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Initial Catalog=pubs;Data Source=op01296"

'*** First, open a connection object with the connection string

cn.Open cConnection

'*** Second, set the properties of the recordset object.  The following set
the recordset
'*** to be un-updateable (static) and the recordset location to be on the
client (the machine
'*** the dll is instansiated at)

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient   'very important!

'*** Third, open the recordset to get the data into it.

rs.Open cSQL, cn, adOpenUnspecified, adLockUnspecified, adCmdUnspecified

Set GetGroups = rs

'*** Close the recordset

Set rs.ActiveConnection = Nothing   'very important.  Else, you loose
connection pooling in MTS!

objCtx.SetComplete

End Function


Quote:
> Hi! I have this problem about recordset:

> I created a MTS object in VB. What I did is that I process all my SQL into
> an object.
> So after my object receives the SQL statement, it process the statement
and
> attempts to return the recordset back to the procedure:

> Public Function ExecuteQuery() as ADODB.Recordset
>                     .
>                     .
>                     .
> Set Return_Recordset= SQLConnection.Execute(SQLQuery)

> Set ExecuteQuery=Return_Recordset

> End Function

> This is not a problem. But when I run it using ASP like this:

> Set Test = Server.CreateObject("SQL2.SQL")
> Test.ConnectStr = "driver={SQL
> SERVER};server=Proverbs;UID=ELASAdmin;PWD=password;database=ELAS"
>    Test.QueryStr = "SELECT * FROM ELAS_UserProfile"
>    Set recs = Test.ExecuteQuery()
>    response.write(recs("Usr_ID"))

> The ASP returns this error:

> "The operation requested by the application is not allowed if the object
is
> closed. "

> From what I investigate, the recordset 'recs' contains no data. I've tried
> many ways to solve the problem but it doesn't seem to help.

> Can anyone enlighten me on this?


> Thanks for any help!



Sat, 27 Oct 2001 03:00:00 GMT  
 Passing recordset back from function
Richard,

Wouldn't you want to use
objCtx.CreateInstance("ADODB.Connection") to create your Connection object
in the same context/transaction as your MTS component ?  This is based upon
the following statement:

"Important Do not use the New operator, or a variable declared As New, to
create an instance of a class that is part of the active project. In this
situation, Visual Basic uses an implementation of object creation that does
not use COM. To prevent this occurrence, it is recommended that you mark all
objects passed out from a Visual Basic componentas Public Creatable, or its
equivalent, and created with either the CreateObject function or the
CreateInstance method of the ObjectContext object."

located at http://msdn.microsoft.com/library/sdkdoc/mts20/mtxpg04_0eb7.htm

-Josh

Quote:

>Public Function GetGroups() As adodb.Recordset
>Dim cSQL As String
>Dim cConnection As String
>Dim rs As New adodb.Recordset
>Dim cn As New adodb.Connection

>Dim objCtx As ObjectContext
>Set objCtx = GetObjectContext()   'These are MTS calls.  Required to run
>correctly in MTS



Thu, 01 Nov 2001 03:00:00 GMT  
 Passing recordset back from function
Dumb Question: Did you instantiate rec?

set rec = Server.CreateObject("ADODB.Recordset")

Beyond that, try specifying the rec parameter as type "Object" - i.e.,
use late binding - and you should be set. Alternatively, you should
(theoretically) be able to reference the ADO DLL's type library, and
early binding will work fine. (Someone correct me if I'm wrong - I'm
not in a position to test this right now...)

-J-

--
Jay Andrew Allen
Senior Programmer, Real Time Enterprises (http://www.rte.com/)
http://www.geocities.com/~jayandrewallen/



Quote:
> Thank you for the suggestion.

> However, I may have forgotten. How do I retrieve the Return_Recordset
in ASP
> from the function?

> In ASP, I tried it as this:

>    Set Test = Server.CreateObject("SQL2.SQL")
>    Test.ConnectStr = "driver={SQL
> SERVER};server=Proverbs;UID=ELASAdmin;PWD=password;database=ELAS"
>    Test.QueryStr = "SELECT * FROM ELAS_UserProfile"
>    rt=Test.ExecuteQuery(rec)
>    response.write(rec("Usr_ID"))

> It gives me:

> Type mismatch: 'Test.ExecuteQuery'

> So how do you retrieve the recordset from the object and use it in my
ASP
> script?
> Thank you for your help!

> Regards,
> Joseph

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---


Sat, 03 Nov 2001 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Passing recordset back from function

2. Passing recordset back from function

3. Passing recordset back from function

4. Ado recordsets-passing back from function

5. passing recordset back to ASP from a COM-object

6. PASS recordset BACK to the Oracle SP

7. how to get value passed back from function..

8. pass an array back from function

9. HELP: Passing an Array back in a function

10. Passing array back from Sub/Function

11. HELP: Using an Array passed back from a function

12. Passing Me.Recordset to Public Function

 

 
Powered by phpBB® Forum Software