How to handle null data from SQL Server 2017 in C#?

Question

I want to display two joined tables from my SQL Server 2017 using C#. I followed this YouTube video. When everything seemed so perfectly fine, I get this error:

System.NullReferenceException 'Object reference not set to an instance of an object.'

I found out it was because of the null I set in my table.

I knew I could use the LEFT JOIN to handle the null values. But the problem is I don't know how to include the SQL command in my C# file.

The highlighted error line is : <td>@item.rule.Amount</td>

The suspected part I think I did wrong is

public ActionResult Index()
{ 
    db1 sd = new db1();

    List<Group> groups = sd.Groups.ToList();
    List<Rule> rule = sd.Rules.ToList();
    List<RuleDesc> ruleDesc = sd.RuleDescs.ToList();

    ViewData["jointtables"] = from g in groups
                              join r in rule on g.AcctNo equals r.AcctNo 
                              into table1
                              from r in table1.DefaultIfEmpty()
                              select new Class1 { groups = g, rule = r};

    return View(ViewData["joinedtables"]);
}

score 0 · Answer 1 · answered Mar 21 '19 at 02:38

0

assume the null field is mynull then it should be:

bool isnull = dr.IsDBNull(dr.GetOrdinal("mynull"))

or you can use COALESCE() or IFNULL() in your SQL Query

answered Mar 21 '19 at 02:38

Donald

551
2
6
22

Where should I put the statement at? – Kiki Mar 21 '19 at 03:13

score 0 · Answer 2 · answered Mar 21 '19 at 03:50

0

When displaying values in views in ASP.NET or .NET Core using Razor View Engine, I suggest you to use methods like .GetValueOrDefault() on nullable types.

Let say, if your Amount field in your model is the type of: int? double? or float? then use GetValueOrDefault() method.

For example:

<td>@item.rule.Amount.GetValueOrDefault()</td>

This will avoid the error page showing System.NullReferenceException 'Object reference not set to an instance of an object.'

I hope this helps.

answered Mar 21 '19 at 03:50

Luis Mezas

73
1
8

But my field is string type. So what should I put? – Kiki Mar 21 '19 at 04:04
Try debugging then, and look for the object `rule` inside `item`, as well at their properties. See if you can somehow initialize the values of those properties. – Luis Mezas Mar 21 '19 at 04:15
thanks to @marc_s whom edited my code. So I replaced the `return View(ViewData["jointtables"])` to `return View(ViewData["joinedtables"]);` . Then when I ran it, it worked. Thanks! – Kiki Mar 21 '19 at 06:27

How to handle null data from SQL Server 2017 in C#?

2 Answers2