Problems returning recordset from sp using sybase 12 and vb6

[jocasio]

Hey gang:

I have a weird problem I cannot fiure out. I am running a stored procedure that returns a recordset, but for some reason it's always empty. I know why, but I cannot figure out why it's happening. Below is the code I'm using:



Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Param_return As Parameter
Dim Param_p As Parameter
Dim Param_c As Parameter
Dim Param_r As Parameter
Dim Param_o As Parameter
Dim Param_startDate As Parameter
Dim Param_endDate As Parameter


Dim x As Long
Dim rs As ADODB.Recordset


Set Conn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset


g_sServer = "myServer"
g_sDatabase = "myDB"

Conn.ConnectionTimeout = 1000
Conn.CursorLocation = adUseServer
Conn.Open "Driver=" & CONNECTION_DRIVER & _
";Srvr=" & g_sServer & _
";Uid=" & USERNAME & _
";Pwd=" & PASSWORD

Conn.DefaultDatabase = g_sDatabase
'Set command properties
Set cmd.ActiveConnection = Conn
cmd.CommandText = "sp_ss"
cmd.CommandType = adCmdStoredProc

' Set up a return parameter.
Set Param_return = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append Param_return


' Set up an input parameter.
Set Param_p = cmd.CreateParameter("Input", adChar, adParamInput, 2)
cmd.Parameters.Append Param_p
Param_p.Value = cboP.Text

Set Param_c = cmd.CreateParameter("Input", adChar, adParamInput, 2)
cmd.Parameters.Append Param_c
Param_c.Value = cboC.Text

Set Param_r = cmd.CreateParameter("Input", adChar, adParamInput, 2)
cmd.Parameters.Append Param_r
Param_r.Value = cboR.Text

Set Param_o = cmd.CreateParameter("Input", adChar, adParamInput, 3)
cmd.Parameters.Append Param_o
Param_o.Value = cboO.Text

Set Param_startDate = cmd.CreateParameter("Input", adDate, adParamInput)
cmd.Parameters.Append Param_startDate
Param_startDate.Value = sStartDate

Set Param_endDate = cmd.CreateParameter("Input", adDate, adParamInput)
cmd.Parameters.Append Param_endDate
Param_endDate.Value = sEndDate

'Execute the command
Set rs = cmd.Execute(, , adAsyncExecute)

Do While (rs.State = adStateExecuting)
If x = 200000 Then StatusBar1.Panels(1).Text = "Processing request"
DoEvents
x = x + 1
Loop

'just to test the recordset
Do Until rs.EOF
MsgBox rs!address
rs.MoveNext
Loop

'Close connection
rs.Close
Set rs = Nothing

Conn.Close
Set Conn = Nothing



I have the command executing Asyncronously, but it returns every fast. Am I doing this right. I have other projects that work, but this one is not working properly. If anyone can tell me what I'm doing wrong, I would greatly appreciation

Thanks

jocasio

 

[CaptainD]

With VB6 on a SQL Server 2000 I was having the same problem then I figured out I did not "set nocount on" in my Stored procedure. Not sure if Sybase would have the same feature