locked
No records showing RRS feed

  • Question

  • User-2012457684 posted

    I created a class called DataAccessLayer.  In this class is the following

    public List<Positions> ListPositionsHeld(int EmployeeID)
    {
        DataSet ds = null;
        List<Positions> jobsList = null;
        using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            using (SqlCommand oCmd = new SqlCommand())
            {
                try
                {
                    oCmd.CommandText = "ListPositionsHeld";
                    oCmd.CommandType = CommandType.StoredProcedure;
                    oCmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;
                    oCmd.Connection = oConn;
                    oConn.Open();
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = oCmd;
                    ds = new DataSet();
                    da.Fill(ds);
                    jobsList = new List<Positions>();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        Positions pos = new Positions();
                        pos.InfoID = Convert.ToInt16(ds.Tables[0].Rows[i]["InfoID"].ToString());
                        pos.EmployeeID = EmployeeID;
    
                        pos.Title = ds.Tables[0].Rows[i]["Title"].ToString();
                        pos.Compensation = ds.Tables[0].Rows[i]["Compensation"].ToString();
                        pos.CompTypeID = Convert.ToInt16(ds.Tables[0].Rows[i]["CompTypeID"].ToString());
                        pos.StartDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["StartDate"]);
                        pos.EndDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["EndDate"]);
                        pos.CurrentJob = Convert.ToBoolean(ds.Tables[0].Rows[i]["CurrentJob"]);
                        pos.SupervisesOthers = Convert.ToBoolean(ds.Tables[0].Rows[i]["SupervisesOthers"]);
                        pos.DepartmentName = ds.Tables[0].Rows[i]["DepartmentName"].ToString();
                        pos.AreaName = ds.Tables[0].Rows[i]["AreaName"].ToString();
                        pos.SupervisorName = ds.Tables[0].Rows[i]["SupervisorName"].ToString();
                        jobsList.Add(pos);
                    }
    
                    return jobsList;
                }
                catch
                {
                    return jobsList;
                }
                finally
                {
                    oConn.Close();
                    oCmd.Dispose();
                }
            }
        }
    }

    I also have this model and this class 

    //This model was created by Visual Studio Database first
    public partial class WorkInfo
    {
        public int InfoID { get; set; }
        public int EmployeeID { get; set; }
        public Nullable<int> DepartmentId { get; set; }
        public int LocationId { get; set; }
        public Nullable<int> Supervisor { get; set; }
        public string Title { get; set; }
        public string Compensation { get; set; }
        public int CompTypeID { get; set; }
        public System.DateTime StartDate { get; set; }
        public Nullable<System.DateTime> EndDate { get; set; }
        public bool CurrentJob { get; set; }
        public bool SupervisesOthers { get; set; }
        
        public virtual CompensationType CompensationType { get; set; }
        public virtual EmployeeData EmployeeData { get; set; }
        public virtual EmployeeData EmployeeData1 { get; set; }
    }
    
    //I created this class
    public class Positions : WorkInfo
    {
        public string DepartmentName { get; set; }
        public string SupervisorName { get; set; }
        public string AreaName { get; set; }
    }

    In my Employee controller I have the following 

    public ActionResult Jobs(int? id)
    {
        if (id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }
        DataAccessLayer dal = new DataAccessLayer();
        var jobList = dal.ListPositionsHeld(Convert.ToInt16(id));
        Employee emp = new Employee();
        ViewBag.EmployeeName = emp.GetName(Convert.ToInt16(id));
        ViewBag.EmployeeID = Convert.ToInt16(id);
        return View(jobList.ToList());
    }

    and this is my view

    @model IEnumerable<AMS_RecordsBox.Models.Positions>
    @using System.Configuration;
    @section styles{
      <link href="~/Content/themes/blue/style.css" rel="stylesheet" />
    }
    @{
        ViewBag.Title = "Positions Held";
    	bool UseDepartments = Convert.ToBoolean(ConfigurationManager.AppSettings["UseDepartments"]);
    }
    
    <h2>Positions Held by @ViewBag.EmployeeName</h2>
    
    @Html.ActionLink("Back to Employee Info", "Details", new { id = ViewBag.EmployeeID })
    <div class="row">
    	<div class="col-xs-12">
    		<table class="table tablesorter table-striped table-responsive" id="List">
    			<thead>
    				<tr>
    					<th>Position</th>
    				@if (UseDepartments)
    				{
    					<th>Department</th>
    				}
    					<th>Location</th>
    					<th>Supervisor</th>
    					<th>Start Date</th>
    					<th>End Date</th>
    				</tr>
    			</thead>
    			<tbody>
    				@foreach (var item in Model)
    				{
    					<tr>
    						<td>@item.Title</td>
    					@if (UseDepartments)
    					{
    						<td>@item.DepartmentName</td>
    					}
    						<td>@item.AreaName</td>
    						<td>@item.SupervisorName</td>
    						<td>@item.StartDate</td>
    						<td>@item.EndDate</td>
    					</tr>
    				}
    			</tbody>
    		</table>
    	</div>
    </div>
    
    @section scripts{
    <script type="text/javascript" src="~/Scripts/jquery.tablesorter.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
    		$("#List").tablesorter({ 
    			 
                widgets : ['zebra'] 
    		});
        });
    </script>
    	 
    }
    

    When I run the SPROC in SSMS it does return a single row.  However, when I run this via the web page there are no rows in the body of the table.   

    I put in some breakpoints in the Controller and in the Method ListPositionsHeld .   I can see a count of 1 when I hover the mouse over the  ds.Tables[0].Rows.Count   

    When i hover over the jobList.ToList() in the view it shows a count of 0

    what am I missing?

    Wednesday, May 13, 2020 10:18 PM

Answers

  • User-2012457684 posted

    mgebhard

    The date is null.  Technically, the date does not exist.  How do you cast something that does not exist to a date?

    It is up to you to come up with a solution.  If the data is required then you have an issue where the data is initially entered.  If it is okay to have a null end date then pass the null date along to the UI.  Don't assign a value or assign a default value if the date is null.

    I am just trying to display info  yes it can be null if the person is still working at that job.    I was doing my null check wrong 

    I had this 

    if(ds.Tables[0].Rows[i]["EndDate"] != null)
    {
        pos.EndDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["EndDate"]);
    }
    else
    {
        pos.EndDate = null;
    }

    instead of this

    if (ds.Tables[0].Rows[i]["EndDate"] != DBNull.Value)
    {
    pos.EndDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["EndDate"]);
    }
    else
    {
    pos.EndDate = null;
    }

     The first still throws the exception about casting to DBNull value.   The second way did not

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 14, 2020 3:39 PM

All replies

  • User-158764254 posted

    looks like you put in a do-nothing Catch that will hide any errors you might get when reading from the table to create the jobsList

    fire up the debugger and step through the code to see if you getting an exception

    Wednesday, May 13, 2020 10:40 PM
  • User1686398519 posted

    Hi,  mj1223

    • First,you should check if there is a Stored Procedure named "ListPositionsHeld" in your database, and then View Code.
      Below is my StoredProcedure. Please refer to it.
      • CREATE PROCEDURE [ListPositionsHeld]
        	@EmployeeID int
        AS
        	SELECT * From dbo.WorkInfoes

    Here is the result.

     
    Best Regards,

    YihuiSun

    Thursday, May 14, 2020 8:53 AM
  • User-2012457684 posted

    Hi,  mj1223

    • First,you should check if there is a Stored Procedure named "ListPositionsHeld" in your database, and then View Code.
      Below is my StoredProcedure. Please refer to it. 

    1) There is a stored procedure and as I said it works in SSMS  

    2) NO  I do not use code first approach it is database first approach 

    3) the way I have stored procedurres returning data generally works.   

    Thursday, May 14, 2020 2:30 PM
  • User-2012457684 posted
     
    
    looks like you put in a do-nothing Catch that will hide any errors you might get when reading from the table to create the jobsList
    
    fire up the debugger and step through the code to see if you getting an exception
    
    Mike Banavige

    Thanks   yes i found a null value in the EndDate.

    I am not sure how to cast a nullable date

    Thursday, May 14, 2020 2:32 PM
  • User475983607 posted

    mj1223

    I am not sure how to cast a nullable date

    The date is null.  Technically, the date does not exist.  How do you cast something that does not exist to a date?

    It is up to you to come up with a solution.  If the data is required then you have an issue where the data is initially entered.  If it is okay to have a null end date then pass the null date along to the UI.  Don't assign a value or assign a default value if the date is null.

    Thursday, May 14, 2020 2:58 PM
  • User-2012457684 posted

    mgebhard

    The date is null.  Technically, the date does not exist.  How do you cast something that does not exist to a date?

    It is up to you to come up with a solution.  If the data is required then you have an issue where the data is initially entered.  If it is okay to have a null end date then pass the null date along to the UI.  Don't assign a value or assign a default value if the date is null.

    I am just trying to display info  yes it can be null if the person is still working at that job.    I was doing my null check wrong 

    I had this 

    if(ds.Tables[0].Rows[i]["EndDate"] != null)
    {
        pos.EndDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["EndDate"]);
    }
    else
    {
        pos.EndDate = null;
    }

    instead of this

    if (ds.Tables[0].Rows[i]["EndDate"] != DBNull.Value)
    {
    pos.EndDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["EndDate"]);
    }
    else
    {
    pos.EndDate = null;
    }

     The first still throws the exception about casting to DBNull value.   The second way did not

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 14, 2020 3:39 PM