[dba-SQLServer] Can't Get Select Results In Visual Basic

To all,

Sorry about cross posting this but really having a nightmare, I have the
following store procedure in SQL Server 2005 :

usp_MyStoredProcedure
 (
  @nvcPayroll  [nvarchar](50),
  @dtiJobDate  [datetime],
  @nvcCode  [nvarchar](1)
 )
AS
BEGIN
 DECLARE @intUpdateFailed INT
 UPDATE dbo.MyTable
 SET AvailabilityCode=@nvcCode
 WHERE PayrollNo =  @nvcPayroll AND
  JobDate  =  @dtiJobDate AND
   (
  @nvcCode =  'A'  OR
  @nvcCode =  'N'
   )                AND
   (
  AvailabilityCode=  'A'  OR
  AvailabilityCode=  'N'
   )

 SET @intUpdateFailed=@@ROWCOUNT
 DELETE FROM dbo.MyTable2
 WHERE PayrollNo=@nvcPayroll
 INSERT INTO MyTable2 (WebResult, PayrollNo)
 SELECT CASE WHEN @intUpdateFailed>0 THEN 'Success' ELSE 'Failed' END,
@nvcPayroll
 SELECT *
 FROM MyTable2
 WHERE PayrollNo=@nvcPayroll
END

If I run this in SQL Server seems to work fine, and the SELECT * FROM
MyTable2 WHERE PayrollNo=@nvcPayroll returns all the fields.  However I have
a simple piece of VB code as below:

Dim rs As ADODB.Recordset
If DE.Conn.Start = adStateClosed Then
    DE.Conn.Open
Endif
Set rs = DE.Conn.Execute(usp_MyStoredProcedure '999999', '" &
format(Date,"mm/dd/yy") & "','N'")
msgbox rs.fields(0)

DE.Conn.Close

' DE.Conn is a DataEnvironment connection which links to the SQL Server

The vb code returns Item cannot be found in the collection corresponding to
the requested name or ordinal.

However if I run the SP, then comment out everything except the SELECT
statement it returns the resultset.

Can anyone tell me what is happening or what I am doing wrong please.

Thanks in advance for any help on this....






-- 
Paul Hartland
paul.hartland at googlemail.com