Problem with Stored Procedure

[sonya9879]

Hi,

I am driving nuts trying to figure out a problem I am having with a stored procedure.
I created a function and call the stored procedure but I am not getting any results. The stored procedure works fine as I get results if I run that from EM. If have tried also to hardcore the results from the stored procedure to see if I get the results to my function and I keep getting null values.

Here is my public function

Public Function GetData(ByVal ID As Integer, ByVal TEL As Integer) As DATAP

' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("constr"))
Dim myCommand As SqlCommand = New SqlCommand("testproc", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterID As SqlParameter = New SqlParameter("@ID", SqlDbType.Int, 4)
parameterID.Value = ID
myCommand.Parameters.Add(parameterID)

Dim parameterTEL As SqlParameter = New SqlParameter("@TEL", SqlDbType.Int, 4)
parameterTEL.Value = TEL
myCommand.Parameters.Add(parameterTEL)

Dim parameterRES As SqlParameter = New SqlParameter("@RES", SqlDbType.Int, 4)
parameterRES.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRES)

Dim parameterRESD As SqlParameter = New SqlParameter("@RESD", SqlDbType.VarChar, 20)
parameterRESD.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRESD)

myConnection.Open()
Try
myCommand.ExecuteNonQuery()
Catch ex As Exception

End Try
myConnection.Close()


'create param for function
Dim myParTest As DATAP = New DATAP

If Microsoft.VisualBasic.Information.IsDBNull(parameterRES.Value) Then
myParTest.RES = 0
Else
myParTest.RES = parameterRES.Value
End If

If Microsoft.VisualBasic.Information.IsDBNull(parameterRESD.Value) Then
myParTest.RESD = "nothing"
Else
myParTest.RESD = parameterRESD.Value
End If

Return myParTest
End Function


When I try to get the results from the function I always get 0 or nothing instead of getting the value that the actual stored procedure give me

Dim test1 As Integer = GetData(1, 20).RES
Dim test2 As String = GetData(1, 20).RESD

response.write(test1)
response.write(test2)

The stored procedure is:

CREATE PROCEDURE testproc
(
@ID int,
@TEL int,
@RES int OUTPUT,
@RESD varchar(20) OUTPUT
)
AS
SELECT
a.RES,
a.RESD
FROM vTTM a LEFT OUTER JOIN
(SELECT tab2.id
FROM tab2 INNER JOIN
tab9 ON tab2.id = tab9.t2_id
WHERE tab2.ID=@ID
AND tab2.TEL= @TEL
GROUP BY tab2.id) b ON a.idt = b.id
GO

This procedure works just fine, and RES and RESD give me the correct result from Enterprise Manager. The problem is in the VB code. Any ideas would be greatly appreciated.

Thanks so much

 

[sonya9879]

ok ignore all of this and sorry to bother all, I found the problem.
I needed to instantiate the variables for both results:

(
@ID int,
@TEL int,
@RES int OUTPUT,
@RESD varchar(20) OUTPUT
)
AS
SELECT
@RES = a.RES,
@RESD = a.RESD
FROM vTTM a LEFT OUTER JOIN
(SELECT tab2.id
FROM tab2 INNER JOIN
tab9 ON tab2.id = tab9.t2_id
WHERE tab2.ID=@ID
AND tab2.TEL= @TEL
GROUP BY tab2.id) b ON a.idt = b.id
GO