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.ConfigurationSe ttings.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.Clo seConnection)
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
