Getting and using values from stored procedure 1

[TommyTea]

I am on my first adp. I have a working stored procedure as follows:

CREATE procedure "ud_verify_record"
(
@employee_id int,
@project_id float,
@week_id datetime,
@records int output
)

As

select @records = (select count(t.employee_id) from Tracking t
where t.employee_id = @employee_id
and t.project_id = @project_id
and t.week_id = @week_id)

I am trying to capture the value of @records in a ADO variable.

My adp code is:
Public Sub VerifyTrackingRecord()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim p1 As New ADODB.Parameter, p2 As New ADODB.Parameter, p3 As New ADODB.Parameter, p4 As New ADODB.Parameter

Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
cmd.CommandText = "ud_verify_record"
cmd.CommandType = adCmdStoredProc

Set p1 = New ADODB.Parameter
p1.Name = "Employee"
p1.Type = adInteger
p1.Direction = adParamInput
p1.Value = Forms!frmTest.cmbEmployee
cmd.Parameters.Append p1

Set p2 = New ADODB.Parameter
p2.Name = "Project"
p2.Type = adDouble
p2.Direction = adParamInput
p2.Value = Forms!frmTest.cmbProject
cmd.Parameters.Append p2

Set p3 = New ADODB.Parameter
p3.Name = "Week"
p3.Type = adDBDate
p3.Direction = adParamInput
p3.Value = Forms!frmTest.txtWeek
cmd.Parameters.Append p3

Set p4 = New ADODB.Parameter
p4.Name = "Records"
p4.Type = adInteger
p4.Direction = adParamOutput
cmd.Parameters.Append p4

cmd.Execute

End Sub

This runs fine but does not give me access to the value of the returned . Is there an easy way do accomplish this?

 

[cmmrfrds]

The output variable is the 4th one and the parameter collection is zero based.

cmd.Execute
Debug.print "output = " cmd(3)

OR.
Did you try.
Debug.print "output = " p4.value

FYI. It is a good idea to set NOCOUNT ON in the stored procedure since that will stop sql server from generating informational messages in the form of recordset(s). This can be confusing when you expect to return your own recordset, but in the case of only a parameter this is okay.

 

[TommyTea]

Actually, I got this, what I can't seem to figure out is how to take the value of cmd(3) and pass its value to a variable or to change the procedure to a function and apply cmd(3) so that the function returns it. Thanks for the NOCOUNT ON tip.

 

[cmmrfrds]

You could do this.

CREATE procedure "ud_verify_record"
(
@employee_id int,
@project_id float,
@week_id datetime,
@records int output
)
As
set nocount on
select @records = count(*) from Tracking t
where t.employee_id = @employee_id
and t.project_id = @project_id
and t.week_id = @week_id)
Return

Public Function VerifyTrackingRecord() as Long
'- your code ..........
cmd.Execute
VerifyTrackingRecord = cmd(3)
End Function

 

[TommyTea]

This looks great. I'll try it Monday.