'Object cannot be cast from DBNull to other types.' in stored procedure

Question

I have a method in which I am trying to read some values from the database table DependencyList. It contains columns that can be null, such as ReleaseId, TaskId and PhaseId. I am having issues when reading and trying to visualize them inside the view when one of them is NULL.

The code looks like below:

public ActionResult FullIndex(int id)
{
        List<Dependency> dependencyList = new List<Dependency>();

        string connectionString = Configuration["ConnectionStrings:DefaultConnection"];

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string sql = "ReadFullDependencies";
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter = new SqlParameter
            {
                ParameterName = "@DependencyId",
                Value = id,
                SqlDbType = SqlDbType.VarChar,
                Size = 50
            };

            command.Parameters.Add(parameter);

            using (SqlDataReader dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    Dependency dependency = new Dependency();
                    dependency.Id = Convert.ToInt32(dataReader["Id"]);
                    dependency.ReleaseId = Convert.ToInt32(dataReader["ReleaseId"]);
                    dependency.ReleaseName = ReadReleaseName(dependency.ReleaseId);
                    dependency.PhaseId = Convert.ToInt32(dataReader["PhaseId"]);
                    dependency.PhaseName = ReadPhaseName(dependency.PhaseId);
                    dependency.TaskId = Convert.ToInt32(dataReader["TaskId"]);
                    dependency.TaskName = ReadTaskName(dependency.TaskId);

                    dependencyList.Add(dependency);
                }
            }

            connection.Close();
        }

        return View(dependencyList);
}

If all of them are not null in the database, then it works perfectly. However, let's say that ReleaseId is NULL. Then, on line:

dependency.ReleaseId = Convert.ToInt32(dataReader["ReleaseId"]);

I get this error:

Object cannot be cast from DBNull to other types.

I've tried putting question marks (?) on the side of each data type in the Model, but that didn't work out.

How can I prevent this from happening and store the null values as "None", for example, inside the Model?

you simply have to check whether the value is Null or not before you try to convert it or assign it to another variable. e.g. (untested): `if (!dataReader["ReleaseId"].IsDBNull) dependency.ReleaseId = Convert.ToInt32(dataReader["ReleaseId"]);`...something along those lines. `DBNull` is not the same as C#'s `null`, so using nullable types (with a `?` as you mentioned) won't help. — ADyson, Jan 05 '20 at 12:08
Thanks! The issue here is that `IsDBNull` is not recognized and there is `no accessible extension method` for it — Questieme, Jan 05 '20 at 12:15
I did say I hadn't tested it, just tried to dredge my memory for the syntax! Try it like this then: `datareader.IsDBNull(dataReader["ReleaseId"])` instead. See also https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.isdbnull?view=netframework-4.8 — ADyson, Jan 05 '20 at 12:22
Actually I did a quick search and your question is a duplicate of many others, such as this one: [SQL Data Reader - handling Null column values](https://stackoverflow.com/questions/1772025/sql-data-reader-handling-null-column-values) . Did you try and google it at all? — ADyson, Jan 05 '20 at 12:23
@ADyson Your help was perfect enough for me! I figured it out eventually - I didn't even need more :). Sorry about the duplicate part, it was a big mistake on my part. I admit that after the first opened links I gave up, thinking that it wouldn't be that easy to find an answer. Thank you for your time, nonetheless! — Questieme, Jan 05 '20 at 12:44

score 3 · Accepted Answer · answered Jan 05 '20 at 12:47

3

You should check DBNull then assign value accordingly, like this

dependency.ReleaseId = dataReader["ReleaseId"] != System.DBNull.Value ? Convert.ToInt32(dataReader["ReleaseId"]) : 0;

answered Jan 05 '20 at 12:47

Nguyễn Văn Phong

13,506
17
39
56

Does this answer your question? @Questieme Let me know if you need any help. – Nguyễn Văn Phong Jan 05 '20 at 12:56
Your answer is also helpful, indeed. Highly appreciated – Questieme Jan 05 '20 at 13:09

score 0 · Answer 2 · edited Jan 05 '20 at 13:32

0

if (!dataReader.IsDBNull(dataReader.GetOrdinal("ReleaseId")))
     dependency.ReleaseId = Convert.ToInt32(dataReader["ReleaseId"]);

Did the trick.

edited Jan 05 '20 at 13:32

Suraj Kumar

5,547
8
20
42

answered Jan 05 '20 at 12:22

Questieme

913
2
15
34

'Object cannot be cast from DBNull to other types.' in stored procedure

2 Answers2