W
William
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
--
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
--