Stored Procedure via ADO 1

[LochDhu]

Hello everyone, having experience in VB & ADO but not a minute with SQL 2000 Stored Procedures, I hope someone can straighten me out with this stuff. I am using ADO to run a stored procedure which simply gets the sum of a field. Below is the code for VB & the stored procedure - @SumTime parameter always returns NULL.

//yada yada - cmd is ado.command object
With cmd
.CommandType = adCmdStoredProc
.CommandText = "GetTechData" 'stored procedure
.Parameters.Append cmd.CreateParameter("@Tech", adVarChar, adParamInput, 25) 'input param
.Parameters("@Tech&quot.Value = "Moore"
.Parameters.Append cmd.CreateParameter("@SumTime", adDouble, adParamOutput, 5) 'output param
.ActiveConnection = cn
End With

cmd.Execute

msgbox cmd.Parameters("@SumTime&quot.Value 'always = NULL!

Stored procedure:

CREATE PROCEDURE GetTechData (
@Tech varchar(25),
@SumTime real OUTPUT)
AS

SELECT @SumTime = SUM(SERVICETIME) FROM TIMECARDENTRIES
WHERE (EMPLOYEEUSERNAME = @Tech)

Notes:
No NULL values in TimeCardEntries table.
SQL Field for ServiceTime is REAL, I had to use adDouble with ADO - I don't think that's the problem.

Any comments surely appreciated.

 

[essa2000]

Dear ;

I have tested the same Code here at my own VB & SQL and it is returning the accurate value.

Create table TestData
(
PrimaryId [int] ,
Tech [varchar] (25) ,
SumTime [real]
)


Insert into TestData Select 1, 'Essa' , 30
Insert into TestData Select 2, 'Essa1' , 30
Insert into TestData Select 3, 'Essa' , 25


CREATE PROCEDURE GetTechData (
@Tech varchar(25),
@SumTime real OUTPUT)

AS


SELECT @SumTime = SUM(sumTime) FROM TestData
WHERE (Tech = @Tech)


I think there is not record agains the "Moore". Please , check the Stored Procedure in SQL Query analyser with the same values.

like this ;

Declare @sumtime real
Exec GetTechData 'Essa' , @sumtime OUTPUT
Select @sumtime

I wrote this code in VB ;

Dim CN As New ADODB.Connection


Private Sub Form_Load()
CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=essa"


Dim cmd As New ADODB.Command

With cmd
.CommandType = adCmdStoredProc
.CommandText = "GetTechData" 'stored procedure
.Parameters.Append cmd.CreateParameter("@Tech", adVarChar, adParamInput, 25) 'input param
.Parameters("@Tech&quot.Value = "Essa"
.Parameters.Append cmd.CreateParameter("@SumTime", adDouble, adParamOutput, 5) 'output param
.ActiveConnection = CN
End With

cmd.Execute

MsgBox cmd.Parameters("@SumTime&quot.Value 'always = NULL!


End Sub


I hope it will help you out.

Regards,
Muhammad Essa Mughal

 

[LochDhu]

Essa,

That was indeed the problem - embarassed to admit it - but thanks for takinng the time to look at my code!