ALTER PROCEDURE [dbo].[Find_SClinicals_Training_Total]
@DateFrom datetime='09/04/05',
@DateToIn datetime='03/04/06',
@mName varchar(50),
@ccName varchar(20),
@tCount int output
AS
SET NOCOUNT ON
begin
declare @DateTo datetime
select @DateTo = @DateToIn+1
declare @moduleName varchar(50)
--need to add date range and get the total count for the employee get the selected training
if @ccName='All' and @mName='All'
begin
--module log
SELECT distinct h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(SELECT [Employee Name],DOB
FROM [Module Log] where [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo
UNION ALL
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
/** WHERE (h.CC = 'A11')**/
set @tCount=@@rowcount
PRINT '1 all all @Cc = ' + @ccName + ' @mName = ' +@mName
end
if (@ccName='All' and @mName<>'All' )
begin
if @mName='Soarian Clinicals Hands-On Exercises'
begin
SELECT distinct h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
set @tCount=@@rowcount
end
else
begin
SELECT distinct h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(
SELECT [Employee Name],DOB
FROM [Module Log] WHERE [Module Name] like @mName and [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
set @tCount=@@rowcount
end
PRINT '2all cc @mName = ' + @ccName + ' @mName = ' +@mName
end
if ( @ccName<>'All' and @mName='All' )
begin
--module log
SELECT h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(SELECT [Employee Name],DOB
FROM [Module Log] where [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo
UNION ALL
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
WHERE h.CC = @ccName
set @tCount=@@rowcount
PRINT '3CC not ALL @mName=All'+ @ccName +'Module Name' +@mName
end
if (@ccName<>'All' and @mName<>'All')
begin
--module log
if @mName='Soarian Clinicals Hands-On Exercises'
begin
SELECT h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
where h.CC=@ccName
set @tCount=@@rowcount
end
else
begin
SELECT h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(SELECT [Employee Name],DOB
FROM [Module Log]
WHERE [Module Name] like @mName and [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
WHERE h.CC = @ccName
GROUP BY h.[Full Name],h.[DOB]
ORDER BY h.[full Name]
set @tCount=@@rowcount
end
PRINT '4CC not ALL @mName not All'+ @ccName +'Module Name' +@mName
end
end
result:
Stored Procedure: Clinical_Edu_2006.dbo.Find_SClinicals_Training_Total
Return Code = 0
Output Parameter(s):
@tCount = 27
asp.net code:
Dim myConn As New SqlConnection(ConfigurationSettings.AppSettings("ConString"))
myConn.Open()
Dim selectCom As New SqlCommand("Find_SClinicals_Training_Total", myConn)
selectCom.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dtB
selectCom.Parameters.Add("@DateToIn", SqlDbType.DateTime).Value = dtE
selectCom.Parameters.Add("@mName", SqlDbType.VarChar).Value = Request.QueryString("PKey")
selectCom.Parameters.Add("@ccName", SqlDbType.VarChar).Value = Request.QueryString("CC")
Dim tCountParamater As New SqlParameter("@tCount", SqlDbType.SmallInt)
tCountParamater.Direction = ParameterDirection.Output
selectCom.Parameters.Add(tCountParamater)
selectCom.CommandType = CommandType.StoredProcedure
Dim reader As SqlDataReader = selectCom.ExecuteReader()
Dim TCount As Int32 = tCountParamater.Value
TCount is nothing here in asp.net
I tested it with reader.RecordsAffected=-1.
Do not know why ? Thx.
