2 Functions Recordset Errors

[objuan]

I have two functions, both of which call on stored procedures to do certain things.

Function 1 uses 2 parameters to build a recordset.

Function 2 calls function 1 to assemble the recordset and then tries to filter that recordset as a check before doing anything else. When I call Function 1 from Function 2 (Set rs = Function1(param1, param2), I get the following error:

Object doesn't support this property or method: 'rs.Filter'

I also get the same error when trying to use .EOF

In function 1, I am using a Command Object to run the stored prodedure. I then set the RecordSet with Set rs = cmd1.Execute. Then I set the function = rs.

I can't tell what is going wrong when the recordset from Function 1 comes back into Function 2.

I would greatly appreciate any advice on this.

 

[tty0]

1) What string does Function 1 Pass back?
2) What recordset method are u using?

'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[objuan]

1)I'm trying to pass a recordset back to Function 2...so parameters are sent from function 2 to function 1, function 1 builds a recordset and sends it back to function 2.

2)I'm trying to use a command object (in Function1) that runs a stored procedure in SQL to return the Recordset and then set the Recordset in function 1 to Command.Execute...I then set the Function to = the Recordset...

My goal is to separate the functions that build the recordsets from the ones that actually do things with the data that is in the record set. I seem to be able to get it to work in certain cases, but in other cases, I get the error that the object doesn't support the property or method like .EOF or .Filter...

I hope my answers are clear enough to give you a better picture of what I'm trying to do.

 

[tty0]

Ok I understand exactly what you are trying to do but not why the errors are being generated. ADO supports both methods so I can't see why there is an error. Do you have a copy of the 1st functions code I could look at? and what exactly is passed to function 2.

Maybe the recordset is not being produced at all which may end up with the errors? Just a thought.

The code would help, if not to post then feel free to email
chris@techsupportuk.com

cheers 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[objuan]

Thanks very much!!! The idea is to pull the users from the
table and then filter to see if the userid already exists...if it doesn't, I'd like to Add the user to the table...via the Sub. However, I'm getting the error ...object doesn't support the property method on my attempt to filter...

Function(1) GetUsers(ByVal iOrgID, ByVal iMeetingID)
Dim dbConn
Dim cmdGetUsers
Dim rsUsers

'Set the Connection string
Set cmdGetUsers = CreateObject("ADODB.Command&quot
set dbConn= server.createobject("ADODB.Connection&quot
set rsUsers = server.createobject("ADODB.Recordset&quot
dbConn.Open sConnString

With cmdGetUsers
.CommandType = adCmdStoredProc
.CommandText = "sp_GetUsers"
Set .ActiveConnection = dbConn
.Parameters.Append .CreateParameter("@OrgID", adInteger, adParamInput, 4, iOrgID)
.Parameters.Append .CreateParameter("@MeetingID", adInteger, adParamInput, 4, iMeetingID)
Set rsUsers = .Execute
End With
GetUsers = rsUsers

End Function

2.....

Sub AddUsers(ByVal iOrgID, ByVal iMeetingID, ByVal iUserID)
Dim dbConn
Dim cmdAddUsers
Dim rsUsers

'Set the Connection string
Set cmdAddUsers = CreateObject("ADODB.Command&quot
' set rsUsers = server.createobject("ADODB.Recordset&quot
set dbConn= server.createobject("ADODB.Connection&quot
dbConn.Open sConnString
Set rsUsers = GetUsers(iOrgID, iMeetingID)
rsUsers.filter = "userid= " & iUserID
If rsUsers.EOF then
With cmdAddUsers
.CommandType = adCmdStoredProc
.CommandText = "sp_AddUsers"
Set .ActiveConnection = dbConn
.Parameters.Append .CreateParameter("@OrgID", adInteger, adParamInput, 4, iOrgID)
.Parameters.Append .CreateParameter("@MeetingID", adInteger, adParamInput, 4, iMeetingID)
.Parameters.Append .CreateParameter("@UserID", adInteger, adParamInput, 4, iUserID)
.Execute
End With
' End If
rsUsers.Filter = ""
dbConn.Close

'Release objects
Set cmdAddUsers = Nothing
Set dbConn = Nothing

End Sub

 

[tty0]

Just a couple of things for starters while I am trying to recreate this over here to get the same problem.

Have you tried to test for

if rs.recordcount = 0 then

instead of EOF.

and

plus you set a filter on the recordset and if it is empty then it tests for EOF as before, but then you go on to set the filter again once it has exited the If statement. Is that the statement that is causing the problem?

In the meantime I will attempt to build something similar this end to test the theory.

Any good? 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399