problem opening ado recordsets

[ari]

Hi...
I'm trying to return a recordset using ado 2.6. I wrote this code using ODBC driver for db2, however I had to switch it to SQLOLEDB. Everything works fine, except when I call certain stored procedures using recordset.open(). When it doesn't work, the open call returns, yet the recordset is still not open. The only common thread I can find doesn't seem to make any sense: procedures that take more than one argument and return recordsets are having the problem.
Here is an abridged version of my code:
Dim rs As ADODB.Recordset, con As ADODB.Connection
Set rs = New ADODB.Recordset

Set con = New ADODB.Connection

con.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx.xxx.xxx.xxx:1433;Initial Catalog=dbname; User ID=****;Password=****;network=dbmssocn;address=xxx.xxx.xxx.xxx:1433"

con.open

rs.CursorLocation = adUseClient
Call rs.Open("ps_adv 1,2,3", con, adOpenForwardOnly, adLockBatchOptimistic, adCmdText)
Set rs.ActiveConnection = Nothing

Thanks for any help.
-Ari

[Johnny101]

You really should be using Command objects for stored procs with parameters. it really helps out in situations like this.

Also, if a stored proc doesn't return any data, the resulting recordset object will not be open and EOF, but rather still Closed. the command object, unfortunately, does not alleviate this problem. so be sure to check for ((rs.State And adStateOpen) = adStateOpen) before moving through the recordset.

just some thoughts,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org