Getting "This method or property cannot be called on Null errors"

Question

UPDATE 1:

An exception is thrown on this line:

client_group_details.Add(new ClientGroupDetails( 

ORIGINAL QUESTION:

I have the following code, which I split from 30 columns of data from the database into only two columns from the database. I get an error when any of the columns returns NULL:

 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 with Null values.

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

Does anyone know how to solve this problem?

Answer 1

This is because reader.GetString should not call DBNull values. Try changing the code as follows:

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

Answer 2

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)); 

Answer 3

There are several ways to do this, but I believe 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 a man page for this: IsNull MSDN link

Basically, you change the SQL text to look something like this:

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

And now, if the column in the database is NULL, it instead returns an empty row.

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

Good luck