Btukfyl

I use this store procedure to get the list of test created, but anytime i make a new entry(create a new test) the new entry is not showing on the list.

But i added 2 new columns to Test Table called StartDateTime and EndDateTime

USE [School-OnPremise]

GO



SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



ALTER PROCEDURE [dbo].[CMS_Test_GetList] 

@SubjectId int,

@ProgramId int,

@ProgramLevelId int,

@AcademicTerm int,

@AcademicSession int,

@AddedBy int,

@EntityStatus int = null



AS

BEGIN

   SET NOCOUNT ON;

select i.*,

  (select Count(*) from TestQuestion where TestId = i.Id) As QuestionNumber,

  (select Count(*) from StudentTests where TestId = i.Id and score is not 

  null) AS ResultsCount

  from Test AS i

  where i.ClassId =COALESCE(@ProgramId,i.ClassId)

  and i.SubjectId = COALESCE(@SubjectId,i.SubjectId)

  and i.Session = COALESCE(@AcademicSession,i.Session)

  and i.AcademicTerm = COALESCE(@AcademicTerm,i.AcademicTerm)   

  and i.AddedBy = COALESCE(@AddedBy,i.AddedBy)  

  and i.EntityStatus = COALESCE(@EntityStatus,EntityStatus)

  order by i.AddedOn desc

END





GO

this the table when i execute the stored procedure passing the argument needed i am supposed to get the entries that fall in the category of the arguments i passed.click sql table to see the image below

sql table

When i tried to execute the store procedure this is what i get

USE [School-OnPremise]

GO



DECLARE @return_value int



EXEC    @return_value = [dbo].[CMS_Test_GetList]

        @SubjectId = 3142,

        @ProgramId = 2045,

        @ProgramLevelId = 0,

        @AcademicTerm = 1,

        @AcademicSession = 2018,

        @AddedBy = 3,

        @EntityStatus = null



SELECT  'Return Value' = @return_value



GO

this is the ouput i get

ouput

ouput cont.

Ah, your final condition is testing i.EntityStatus = NULL - this will not work - if a column value = null then it will only satisfy the IS NULL operator.

and (@EntityStatus IS NULL OR (@EntityStatus IS NOT NULL AND i.EntityStatus = @EntityStatus))

the problem is not the test procedure, it from the code below there is nothing saving the enitystatus' value

    public bool insert_New_Test(string TestName, int Duration, int SessionYear, int ClassId, int SubjectId, bool Status, int AddedBy,

                int ProgramLevelId, int AcademicTerm, int questionSetUpType, out int id, DateTime startDateTime, DateTime endDateTime, int? testType)

            {

                bool functionReturnValue = false;

                id = 0;

                try

                {

                    using (SqlConnection con = new SqlConnection(conn))

                    {

                        con.Open();

                        SqlCommand oComm = new SqlCommand();

                        using (oComm)

                        {

                            oComm.Connection = con;

                            oComm.CommandType = CommandType.StoredProcedure;

                            oComm.CommandText = "CMS_Test_Insert";



                            oComm.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar));

                            oComm.Parameters["@Title"].Value = TestName;



                            oComm.Parameters.Add(new SqlParameter("@SubjectId", SqlDbType.Int));

                            oComm.Parameters["@SubjectId"].Value = SubjectId;



                            oComm.Parameters.Add(new SqlParameter("@ClassId", SqlDbType.Int));

                            oComm.Parameters["@ClassId"].Value = ClassId;



                            oComm.Parameters.Add(new SqlParameter("@Session", SqlDbType.Int));

                            oComm.Parameters["@Session"].Value = SessionYear;



                            oComm.Parameters.Add(new SqlParameter("@ProgramLevelId", SqlDbType.Int));

                            oComm.Parameters["@ProgramLevelId"].Value = ProgramLevelId;



                            oComm.Parameters.Add(new SqlParameter("@AcademicTerm", SqlDbType.Int));

                            oComm.Parameters["@AcademicTerm"].Value = AcademicTerm;



                            oComm.Parameters.Add(new SqlParameter("@Duration", SqlDbType.Int));

                            oComm.Parameters["@Duration"].Value = Duration;



                            oComm.Parameters.Add(new SqlParameter("@Status", SqlDbType.Bit));

                            oComm.Parameters["@Status"].Value = Status;



                            oComm.Parameters.Add(new SqlParameter("@AddedBy", SqlDbType.Int));

                            oComm.Parameters["@AddedBy"].Value = AddedBy;





                            oComm.Parameters.Add(new SqlParameter("@QuestionSetupType", SqlDbType.Int));

                            oComm.Parameters["@QuestionSetupType"].Value = questionSetUpType;



                            if (startDateTime > DateTime.MinValue)

                            {

                                oComm.Parameters.Add(new SqlParameter("@StartDateTime", SqlDbType.DateTime));

                                oComm.Parameters["@StartDateTime"].Value = startDateTime;

                            }

                            if (endDateTime > DateTime.MinValue)

                            {

                                oComm.Parameters.Add(new SqlParameter("@EndDateTime", SqlDbType.DateTime));

                                oComm.Parameters["@EndDateTime"].Value = endDateTime;

                            }



                            oComm.Parameters.Add(new SqlParameter("@TestType", SqlDbType.Int));

                            oComm.Parameters["@TestType"].Value = testType;



                            oComm.Parameters.Add(new SqlParameter("@Success", SqlDbType.Bit));

                            oComm.Parameters["@Success"].Direction = ParameterDirection.Output;



                            oComm.Parameters.Add(new SqlParameter("@TestId", SqlDbType.Int));

                            oComm.Parameters["@TestId"].Direction = ParameterDirection.Output;

                            oComm.ExecuteNonQuery();

                            functionReturnValue = Convert.ToBoolean(oComm.Parameters["@Success"].Value);

                            id = Convert.ToInt32(oComm.Parameters["@TestId"].Value);



                        }

                        con.Close();

                    }



                }

                catch (SqlException ex)

                {

                    Elmah.ErrorSignal.FromCurrentContext().Raise(ex);

                    functionReturnValue = false;

                }

                return functionReturnValue;



            }

so when it try to get the value for the entitystatus the value is null

public List<TestViewModel> get_List_Of_Test(int SubjectId, int ProgramLevelId, int AcademicTerm, int AcademicSession,

            int ProgramId, int status)

        {

            List<TestViewModel> sList = new List<TestViewModel>();

            try

            {

                using (SqlConnection con = new SqlConnection(conn))

                {

                    con.Open();

                    SqlCommand oComm = new SqlCommand();

                    using (oComm)

                    {

                        oComm.Connection = con;

                        oComm.CommandType = CommandType.StoredProcedure;

                        oComm.CommandText = "CMS_Test_GetList";



                        oComm.Parameters.Add(new SqlParameter("@SubjectId", SqlDbType.Int));

                        oComm.Parameters["@SubjectId"].Value = SubjectId == 0 ? System.Data.SqlTypes.SqlInt32.Null : SubjectId;



                        oComm.Parameters.Add(new SqlParameter("@ProgramId", SqlDbType.Int));

                        oComm.Parameters["@ProgramId"].Value = ProgramId == 0 ? System.Data.SqlTypes.SqlInt32.Null : ProgramId;



                        oComm.Parameters.Add(new SqlParameter("@ProgramLevelId", SqlDbType.Int));

                        oComm.Parameters["@ProgramLevelId"].Value = ProgramLevelId == 0 ? System.Data.SqlTypes.SqlInt32.Null : ProgramLevelId;



                        oComm.Parameters.Add(new SqlParameter("@AcademicSession", SqlDbType.Int));

                        oComm.Parameters["@AcademicSession"].Value = AcademicSession == 0 ? System.Data.SqlTypes.SqlInt32.Null : AcademicSession;



                        oComm.Parameters.Add(new SqlParameter("@AcademicTerm", SqlDbType.Int));

                        oComm.Parameters["@AcademicTerm"].Value = AcademicTerm == 0 ? System.Data.SqlTypes.SqlInt32.Null : AcademicTerm;



                        oComm.Parameters.Add(new SqlParameter("@AddedBy", SqlDbType.Int));

                        oComm.Parameters["@AddedBy"].Value = MembershipHelper.GetActiveUser().Teacher.IsAdmin ? System.Data.SqlTypes.SqlInt32.Null : MembershipHelper.GetActiveUserId;



                        oComm.Parameters.Add(new SqlParameter("@EntityStatus", SqlDbType.Int));

                        oComm.Parameters["@EntityStatus"].Value = status;



                        SqlDataReader rdr = oComm.ExecuteReader();

                        if (rdr.HasRows)

                        {

                            int _sn = 0;

                            while (rdr.Read())

                            {

                                int publicStatus = _rdrHelper.getOrdinalInt32Value(rdr, "EntityStatus");

                                _sn++;

                                TestViewModel qList = new TestViewModel();

                                qList.ProgramLevelId = _rdrHelper.getOrdinalInt32Value(rdr, "ProgramLevel");

                                qList.ProgramLevelText = Extensions.ListEnums.GetProgramLevel().Skip(qList.ProgramLevelId - 1).First().Text;

                                qList.AcademicTermId = _rdrHelper.getOrdinalInt32Value(rdr, "AcademicTerm");

                                qList.AcademicTermText = Extensions.ListEnums.GetTermChoice().Skip(qList.AcademicTermId - 1).First().Text;

                                qList.TestTitle = _rdrHelper.GetOrdinalStringValue(rdr, "Title");

                                qList.ClassId = _rdrHelper.getOrdinalInt32Value(rdr, "ClassId");

                                qList.ClassName = _rdrHelper.GetOrdinalStringValue(rdr, "ClassName");

                                qList.SubjectName = _rdrHelper.GetOrdinalStringValue(rdr, "SubjectName");

                                qList.SessionId = _rdrHelper.getOrdinalInt32Value(rdr, "Session");

                                qList.TestId = _rdrHelper.getOrdinalInt32Value(rdr, "Id");

                                qList.Duration = _rdrHelper.getOrdinalInt32Value(rdr, "Duration");

                                qList.Status = _rdrHelper.getOrdinalBooleanValue(rdr, "Status");

                                qList.QuestionCount = _rdrHelper.getOrdinalInt32Value(rdr, "QuestionNumber");

                                qList.TestSn = _sn;

                                qList.ResultExists = _rdrHelper.getOrdinalInt32Value(rdr, "ResultsCount") > 0;

                                qList.AddedBy = _rdrHelper.getOrdinalInt32Value(rdr, "AddedBy");

                                qList.Test.StartDateTime = _rdrHelper.getNullableOrdinalDatetimeValue(rdr, "StartDateTime");

                                qList.Test.EndDateTime = _rdrHelper.getNullableOrdinalDatetimeValue(rdr, "EndDateTime");

                                qList.Test.EntityStatus = publicStatus;

                                sList.Add(qList);

                            }

                        }

                        con.Close();

                    }



                }

            }

one my create page there is no field to pass the value for the entitystatus

create test page

but for the edit page there is a field to pass value into the entitystatus

edit page

the publish status is the field that gives the entity status value. The thing is after creating the test i'm redirected to the edit page to click the save button so that all entries will be saved into the databas3 but i did not click the save button i just went back to the index page which shows the list of tests.