ADO: Output Parameter value from Stored Proc Recordset Results 1

[bjgolden80]

Hi,

I am working with ADO to retrieve recordsets from SYBASE using stored procedures. The stored procedures also return an output parameter.

The problem I am having is that the value of the output parameter is not being exposed until after the recordset is cycled through and closed, however I need the output parameter value to handle the recordset as I loop through it. This used to work fine with RDO but not with ADO for some reason.

Any ideas on work arounds or fixes?

thanks,
Barry

---------------------

 

[fredericofonseca]

Please post your code so we can look at it.

Including one of the SP used.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bjgolden80]

Below is a code sample and the SP. Basically I am returning the number of records returned by the SP so that I can handle the recordset to fill a flex grid and set it up with the appropriate number of rows etc....

Thanks !!


Here is a Code Sample:

Private Sub Command1_Click()

Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim objPara As ADODB.Parameter
Dim objpara2 As ADODB.Parameter
Dim objRS As ADODB.Recordset
Dim k As Integer
Dim values(1) As Integer

Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command

'Creating the DB connection string
'Please change the below connection string as per your server and database being used.
objCon.ConnectionString = "!!!!!CONN STRING HERE !!!!"

'Opening the connection
objCon.Open objCon.ConnectionString

MsgBox "Connection opened"

'assigning the command object parameters
With objCom
.CommandText = "p_gui_GetJobProcess" 'Name of the stored procedure
.CommandType = adCmdStoredProc 'Type : stored procedure
.ActiveConnection = objCon.ConnectionString
End With

'Create 2 output parameters
Set objPara = objCom.CreateParameter("@JobID", adChar, adParamInput, 4, "0008")
Set objpara2 = objCom.CreateParameter("@pcnt", adInteger, adParamOutput)

'Append the output parameters to command object
objCom.Parameters.Append objPara
objCom.Parameters.Append objpara2

'Store the result in a recordset
Set objRS = objCom.Execute


'MsgBox "Total records returned: " & objpara2.Value
'Open the recordset
Do While Not objRS.EOF
For k = 0 To objRS.Fields.Count - 1
Debug.Print objRS(k).Name & ": " & objRS(k).Value
Next
Debug.Print "_____"
objRS.MoveNext
Loop
'**** COMMENT THE FOLLOWING LINE OUT TO GET A VALUE RETURNED FOR THE OUT-PARAM, while being executed no values are returned....****
values(0) = objpara2.Value
'Close the recordset
objRS.Close

values(1) = objpara2.Value
'retrieve the output parameters values

MsgBox objPara.Value
MsgBox "Total records returned: Pre=" & values(0) & ";PostA=" & values(1)
'close connection
objCon.Close

'cleaning up
Set objCom = Nothing
Set objCon = Nothing
Set objPara = Nothing
Set objpara2 = Nothing
Set objRS = Nothing

End Sub


and here is the SP:


create proc dbo.p_gui_GetDBProcess
(@DBName varchar(30),
@pcnt int output)
as
/*db processes by way of job header, read only*/
select a.ProcessID, a.JobID, a.ExecType, a.rExecute, a.UseParams
from Job_Process a,
Job_Header b
where b.DBName = @DBName
and a.JobID = b.JobID

select @pcnt = @@rowcount

 

[fredericofonseca]

I think your problem is that you are using a cursor location of aduseserver (which is the default).

Try and add the following.

'Opening the connection
objCon.CursorLocation = adUseClient

objCon.Open objCon.ConnectionString
(or add it on the Recordset object
'Store the result in a recordset
change Set objRS = objCom.Execute
to
objRS.CursorLocation = adUseClient
objRS.open objcom
)


and uncomment the following. It should now return the record count.
MsgBox "Total records returned: " & objpara2.Value
'Open the recordset
Do While Not objRS.EOF



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bjgolden80]

Frederico,

Thanks so much...this has been plaguing me !!

You are the man!,

Barry