returning output vars from nested stored procedure

[Joe Van Meer]

Hi all,

I have an app that currently runs through 3 seperate stored procedures each
returning a count of records. What I would like to do is combine these
calls into one call, however I am having an issue getting the output
parameters' values after execution. Here is a snipit of code that calls one
for simplicity's sake: I think I am close, i was under the impression I
could use the datareader to get at it, but is is always returning 0

Cheers & thanks a bunch, Joe


'SQL

'TOP LEVEL CALLING SP
CREATE PROCEDURE [dbo].[desktopquery]

@empid Int,
@deptid Int

AS

EXEC empprojectcnt @empid Int, @projectcnt Int Output
GO



'PROJECTS
CREATE PROCEDURE [dbo].[empprojectcnt]

@empid Int,
@projectcnt Int Output

AS

Select @projectcnt = COUNT(projectid) FROM PROJECT WHERE empid = @empid
Return @projectcnt
GO





'ASP NET

Private Sub GetEmpStats()

Dim curEmpID As Integer = CInt(Session("empid"))

Dim curDeptID As Integer = CInt(Session("deptid"))

Dim MyConn As New
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("dbConn
"))

Dim MySQLe As String = "[dbo].[desktopquery]"

Dim Cmda As New SqlCommand(MySQLe, MyConn)

Cmda.CommandType = CommandType.StoredProcedure

Dim objDR As SqlDataReader

'INPUT parameters

Cmda.Parameters.Add(New SqlParameter("@deptid", curDeptID))

Cmda.Parameters.Add(New SqlParameter("@empid", curEmpID))

'output parameters



Dim projectcnt As SqlParameter = Cmda.Parameters.Add("@projectcnt",
SqlDbType.Int)

projectcnt.Direction = ParameterDirection.Output



Dim curProCount As Integer



Try

If MyConn.State <> True Then

MyConn.Open()

End If



objDR = Cmda.ExecuteReader(System.Data.CommandBehavior.CloseConnection)



While objDR.Read

curProCount = CInt(Cmda.Parameters("@projectcnt").Value)

End While

objDR.Close()



Catch e As SqlException

errpanel.Visible = True

warningpanel.Visible = False

mainpanel.Visible = False

errmessage.Text = e.Message

Finally

If MyConn.State = ConnectionState.Open Then

MyConn.Close()

End If

End Try

End Sub

 

[Joe Van Meer]

Hi All,

Nevermind I figured it out right after I posted this hehe

The problem was that I was trying to access the output parameters BEFORE
closing out the datareader DUH!
So i simply removed the while loop, moved the close() method up and then
grabbed the parameter value


Thx for being there anyways