Answered by:
No records showing

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
-
- Secondly, here are some links about how to use StoredProcedure, you can refer to them.
- If you are using Entity Framework Code First,please refer to them.
- https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-procedures
- https://www.c-sharpcorner.com/UploadFile/4b0136/stored-procedures-in-entity-framework-6-in-mvc-5/
- Remarks: In this case, you can add the following code in the context to only generate a stored procedure that implements CUD, so you can create a stored procedure to get all the data.
-
protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<WorkInfo>().MapToStoredProcedures(); modelBuilder.Entity<Positions>().MapToStoredProcedures(); }
- If you are using Entity Framework Database First,please refer to it.
- If you are using Entity Framework Code First,please refer to them.
Here is the result.
Best Regards,YihuiSun
Thursday, May 14, 2020 8:53 AM - First,you should check if there is a Stored Procedure named "ListPositionsHeld" in your database, and then View Code.
-
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 - First,you should check if there is a Stored Procedure named "ListPositionsHeld" in your database, and then View Code.
-
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 dateThe 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