I have a stored procedure that inserts into a table (via the INSERT SQL statement).

If I issue any SELECT statement issued before that,
it gets returned to my ADO Recordset.

But if the SELECT is issued after the INSERT (the SELECT is issued on a table that may or may not have anything to do with the table in the INSERT statement),
it does not work ! The ado recordset does not get any results.

I know the stored procedure itself worked and did not error out, because table A contains the results of the insert.

Create procedure sp_test
( @TEST Varchar(100), @TEST2 varchar(100) )
as
Select * from Y /* this will work, but I don't need this */

insert into A select * from B

Select * from Y /* this will not work, but I want this to work */
Select * from A /* this will not work, but I want this to work */

GO

'******* VB Code
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MYSQLSERVERNAME"
cn.Properties("Initial Catalog").Value = "MYDATABASENAME"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("TEST", adVarChar, adParamInput, 100, "nurskill")
cmd.Parameters.Append cmd.CreateParameter("TEST2", adVarChar, adParamInput, 100, "FK_HOSP")
Set rs = cmd.Execute

Debug.Print rs(0) ' nothing in the resultset !!

I even tried this: the ADO calls stored proc A, which in turn calls a stored proc B, which does the INSERT. Then stored proc A returns the resultset. So that the StoredProc that does th INSERT is not the one that returns the resultset. It still does not reach the ADO recordset.

I even tried creating an ADO Connection object WITHEVENTS so that I can wait till the Command Complete Event before checking hte recordset... still no luck.


thanks