execute stored proc in Excel
 

Hi,
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



--

execute stored proc in Excel
 

William,

I have no idea whether this works in Sybase, but in SQL you need to

SET NOCOUNT ON
at the start of your stored proc and
SET NOCOUNT OFF
at the end

Robin Hammond
www.enhanceddatasystems.com

"William" wrote in message
...
Hi,
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



--