Error-Specified cast is not valid

Question

Hi

I am getting above error . Data file also attached.

public List<Employee> GetAllEmployee()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnn0"].ConnectionString);
string query = "select empid,firstname,lastname,dept,sex from Oh ";
SqlCommand cmd = new SqlCommand(query, conn);
List<Employee> Employee = new List<Employee>();
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Employee.Add(new Employee
{
EmpId = (int)dr[0],
FirstName = dr[1].ToString(),
LastName = dr[2].ToString(),
Dept = (int)dr[3],
Sex = dr[4].ToString()
});
}
}
conn.Close();
return Employee;
}

Thanks

Ranganath Prasad · Answer

Hi Ramco, You can handle nulls either in your SQL query or in your C# code. If you prefer to do it in Sql Query : Change string query = "select empid,firstname,lastname,dept,sex from Oh "; To string query = "select COALESCE(empid, 0) , COALESCE(firstname, ""), COALESCE(lastname, ""), COALESCE(dept, 0),COALESCE(sex, "") from Oh "; What am doing above is : If any integer columns are null, retrun 0 ( emp id, dept ). If any string columns are null, return empty string ( first name, last name, sex ). Please use COALESCE only where you can expect nulls. Using it for every column is not a good practice. If you rather prefer to do it in C# while (dr.Read()) { Employee.Add(new Employee { EmpId = dr.IsDBNull(0) ? 0 : Convert.ToInt32(dr.GetValue(0)), FirstName = dr.IsDBNull(1) ? string.Empty : dr[1].ToString(), LastName = dr.IsDBNull(2) ? string.Empty : dr[2].ToString(), Dept = dr.IsDBNull(3) ? 0 : Convert.ToInt32(dr.GetValue(3)), Sex = dr.IsDBNull(4) ? string.Empty : dr[4].ToString(), }); } Again, this code will store 0 in EmpId and Dept if they are null, and Empty string in FirstName, LastName and Sex columns if they are null. You can change the values depending on your requirement. If you prefer to keep them null as it is, you might need to mark your fields in Employee class as nullable types ( supported in C# >=8 version ). Here is an example : public class Employee { public int? EmpId { get; set; } // Notice the ? symbol public string FirstName { get; set; } public string LastName { get; set; } public int? Dept { get; set; } // Notice the ? symbol public string Sex { get; set; } } And then, while (dr.Read()) { Employee.Add(new Employee { EmpId = dr.IsDBNull(0) ? default(int?) : Convert.ToInt32(dr.GetValue(0)), FirstName = dr.IsDBNull(1) ? default(string) : dr[1].ToString(), LastName = dr.IsDBNull(2) ? default(string) : dr[2].ToString(), Dept = dr.IsDBNull(3) ? default(int?) : Convert.ToInt32(dr.GetValue(3)), Sex = dr.IsDBNull(4) ? default(string) : dr[4].ToString(), }); } Let me tell you something very important here. Changing the model ( employee class ) to have nullable types might break exisiting code - Please be careful there

Rijwan Ansari · Answer

This is because null invalid data. You can do like this. public List GetAllEmployee() { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ "cnn0" ].ConnectionString); string query = "select empid,firstname,lastname,dept,sex from Oh " ; SqlCommand cmd = new SqlCommand(query, conn); List Employee = new List (); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Employee.Add( new Employee { EmpId = Convert.ToInt32(dr[0]), FirstName = Convert.ToString(dr[1]), LastName = Convert.ToString(dr[2]), Dept = Convert.ToInt32(dr[3]), Sex = Convert.ToString(dr[4]) }); } } conn.Close(); return Employee; }

Ranganath Prasad · Answer

Hi Ramco, so, have you updated the employee class fields to nullable types since you decided to keep nulls as it is ? If yes, please share your updated Employee class

Ramco Ramco · Answer

HI Ranganath In case of numeric fields it gives below error Cannot implicitly convert type 'int?' to 'int'. An explicit conversion exists (are you missing a cast?) while (dr.Read()) { Employee.Add(new Employee { EmpId = dr.IsDBNull(0) ? default(int?) : Convert.ToInt32(dr.GetValue(0)), FirstName = dr.IsDBNull(1) ? default(string) : dr[1].ToString(), LastName = dr.IsDBNull(2) ? default(string) : dr[2].ToString(), Dept = dr.IsDBNull(3) ? default(int?) : Convert.ToInt32(dr.GetValue(3)), Sex = dr.IsDBNull(4) ? default(string) : dr[4].ToString(), }); } Thanks

Ramco Ramco · Answer

Hi Ranganath Now i am getting error Object cannot be cast from DbNull to other types. Can i handle Null scenario here in the class. public class Employee { public int EmpId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public int Dept { get; set; } public string Sex { get; set; } } Thanks

Nitin Sontakke · Answer

Hello, From whatever data that you have shared, this is obviously happening due to last column of the last row. As suggested already, you will need to add the null check. Either quering the data or while consuming it. I prefer to deal with this in the query itselft as shown below: string query = "select [empid], [firstname], [lastname], [dept], isnull([sex], 0) [sex] from Oh "; Hope it helps!

Ranganath Prasad · Answer

Try to use Convert.ToInt32(dr.GetValue(0 )); and Convert.ToInt32(dr.GetValue(3 )); when converting to Int. And you might want to handle nulls coming from db. If you are still facing issue, give me your Employee class