CODEWITHSUNDEEP
c#.net-3.5c#-3.0asmxasp.net-3.5
oshirowanen
oshirowanen

Reputation: 15925

Getting "This method or property cannot be called on Null values" error

UPDATE 1:

The exception is being thrown on this line:

client_group_details.Add(new ClientGroupDetails(

ORIGINAL QUESTION:

I have the following code which I have stripped down from 30 columns of data from the database to just 2 columns from the database. I get an error whenever any of the columns return a NULL value:

public class ClientGroupDetails
{
    public String Col2;
    public String Col3;

    public ClientGroupDetails(String m_Col2, String m_Col3)
    {
        Col2 = m_Col2;
        Col3 = m_Col3;
    }

    public ClientGroupDetails() { }
}

[WebMethod()]
public List<ClientGroupDetails> GetClientGroupDetails(string phrase)
{
    var client_group_details = new List<ClientGroupDetails>();

    using (connection = new SqlConnection(ConfigurationManager.AppSettings["connString"]))
    {
        using (command = new SqlCommand(@"select col2, col3 where col1 = @phrase", connection))
        {
            command.Parameters.Add("@phrase", SqlDbType.VarChar, 255).Value = phrase;

            connection.Open();
            using (reader = command.ExecuteReader())
            {
                int Col2Index = reader.GetOrdinal("col2");
                int Col3Index = reader.GetOrdinal("col3");

                while (reader.Read())
                {
                    client_group_details.Add(new ClientGroupDetails(
                        reader.GetString(Col2Index),
                        reader.GetString(Col3Index)));
                }
            }
        }
    }

    return client_group_details;
}

The error I am getting is:

Data is Null. This method or property cannot be called on Null values.

I'm not sure what to do here to deal with NULL values as the code above is a stripped down version.

Anyone know how to solve this issue?

Upvotes: 4

Views: 12505

Answers (5)

JustJohn
JustJohn

Reputation: 1460

I am hoping this helps:

When adding new columns to my existing table if it is a bit column (boolean) I set the default value in SQL Server 2019 to "(0)" (false), and made the column Nullable.

I guess when making a column nullable, and having a default value, the default value will not be added when creating a new row without explicitly referencing the column in the INSERT SQL.

Since the error never specifies a column name which can really cross your eyes in a big table, I just open the latest rows and look for bit columns that have Null values. Then I change my code to explicitly add "0" to that column when creating a new row. And manually change the offending columns. (might be a little tougher on a lot of rows).

That is one solution but ideal solution is go to back and make the column definition "Not Null".

There is also a solution probably of changing the Model in my .NET Core 2.2 Razor Pages app.

Upvotes: 0

A.M.Rashed Mahamud
A.M.Rashed Mahamud

Reputation: 53

If you are trying to read some nullable data from the database, but your type is not nullable you can get this error.

If MyInt is nullable in the database and you have this entity:

public class MyEntity
{
    public int Id { get; set; }
    public int MyInt { get; set; }
}

You will get the exception: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

To fix this, just change the type of your MyInt property to Nullable or int?:

public class MyEntity
{
    public int Id { get; set; }
    public int? MyInt { get; set; }
}

Note: This is not an answer to the original question, but is an answer to the question in the title.

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

This is because reader.GetString should not be called on DBNull values. Try changing your code as follows:

client_group_details.Add(new ClientGroupDetails(
    reader.IsDbNull(Col2Index) ? null : reader.GetString(Col2Index),
    reader.IsDbNull(Col3Index) ? null : reader.GetString(Col3Index)));

Upvotes: 5

Matt T
Matt T

Reputation: 511

There are a couple ways you can do this, but I think the best approach for your code is to add a simple function call to your SQL text - namely, the IsNull function.

Here's a link to the manual page for this: IsNull MSDN reference

Basically, you'll change your SQL text to look similar to this:

"select IsNull(col2, ''), IsNull(col3, '') where col1 = @phrase"

And now if the column in the DB is null, it will instead return a blank string.

You can also set default values on your columns, or you can check for System.DBNull.Value on your code side.

Good luck!

Upvotes: 0

stuartd
stuartd

Reputation: 73243

You need to use IsDbNull to check if the column is null before calling GetString, something like:

string s1, s2;

if (reader.IsDbNull(Col1Index) == false)
{
   s1 = reader.GetString(Col1Index);
}

if (reader.IsDbNull(Col2Index) == false)
{
   s2 = reader.GetString(Col2Index);
}

client_group_details.Add(new ClientGroupDetails(s1, s2));

Upvotes: 1

Related Questions