VB Forum / Database Access / February 2006

Hi,
i think executing the stored proc in VB and VBA are very similar. so, i
posted the question to here to see if someone has came across the same
problem before...

i'm trying to execute a stored proc stored in Sybase in VBA EXCEL 2003. the
stored proc is supposed to return about 30 rows. but, if i execute the stored
proc in VBA, rs.recordcount is -1 and rs.eof is true. i enclosed the stored
proc below. it just makes me very frustrated. any help or feedback is very
welcome.
Thank you very much.
will

   MsgBox rs.RecordCount ==> ALWAYS RETURN -1

       While Not rs.EOF ==> rs.EOF = TRUE

create procedure sp_CoverageEventHorizonHistory
@NoteDBSecId varchar(30)
as

select s.Symbol, s.SecurityName as 'Company', t.PriceTarget_Numeric
from Securities s, TargetPriceActions t
where s.NoteDBSecId =@NoteDBSecId
and s.NoteDBSecId=t.NoteDBSecId and t.PriceTarget_Numeric !=NULL

go

Private Sub CommandButton1_Click()

Dim DB As BSERDBConnect.BSERDataConnect
Dim con As ADODB.Connection
Dim comm As ADODB.Command
Dim rs As ADODB.Recordset

Dim SQL As String

Set DB = New BSERDataConnect
DB.connectDB "database", "login", "pwd"

   Set conn = DB.oConn
   Set comm = New ADODB.Command
   Set rs = New ADODB.Recordset

   comm.ActiveConnection = conn
   comm.CommandType = adCmdStoredProc
   comm.CommandText = "sp_CoverageEventHorizonHistory"
   
 
   Dim paramIn1 As ADODB.Parameter
   
   Set paramIn1 = comm.CreateParameter("NoteDBSecId", adVarChar,
adParamInput, 30, "1782")
   comm.Parameters.Append paramIn1

   Dim records As Integer
   
   rs.CursorLocation = adUseClient
   Set rs = comm.Execute(records)
   
   Set wksEH = Worksheets("TESTING")

   For c = 0 To rs.Fields.Count - 1
       wksEH.Cells(1, c + 1).Value = rs.Fields(c).Name
   Next
   With wksEH.Rows(1).Cells.Font
       .Underline = True
       .Bold = True
   End With

   MsgBox rs.RecordCount ==> ALWAYS RETURN -1
   Do While Not rs Is Nothing
       While Not rs.EOF ==> rs.EOF = TRUE
           MsgBox rs.Fields(0) & " " & rs.Fields(1)
           rs.MoveNext
       Wend
       Set rs = rs.NextRecordset
   Loop

   Set comm = Nothing
   
End Sub