Data is Null in my case in my List.Add

Question

I am getting an error from following code, which has written for show table data

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

So here is my code so that you can help me investigate it.

public static List<NomsPRRequest> LoadPRfromDB_withParams(DateTime from, DateTime to, string EntityID,
        string DepartmentID)
    {
        string sScript = m_sReport + ((EntityID == "") ? "" : " AND d.[EntityID]=" + EntityID) + ((DepartmentID == "") ? "" : " AND d.[DepartmentID]=" + DepartmentID)
            + " and [RequestDate] between '" + from.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + to.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        Dictionary<long, NomsPRRequest> data = new Dictionary<long, NomsPRRequest>();
        long key;
        double dAmount;
        using (SqlConnection con = new SqlConnection(m_sConnectionString))
        {
            con.Open();
            using (SqlCommand command = new SqlCommand(sScript, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    key = reader.GetInt64(0);
                    if (!data.ContainsKey(key))
                    {
                        data.Add(key, new NomsPRRequest()
                        {
                            RequestID = key,
                            RequestDate = reader.GetDateTime(1),
                            PARNumber = reader.GetString(2),
                            DepartmentName = reader.GetString(10),
                            DepartmentID = reader.GetInt64(11),
                            StatusID = reader.GetInt64(3),
                            FullName = reader.GetString(7),
                            InboxLearUID = reader.GetString(12),
                            ProgramName = reader.GetString(14),
                            ItemList = new List<NomsPRItem>(),
                            TotalAmount = 0.0
                        });
                    }
                    dAmount = (double)reader.GetDecimal(21) * (double)reader.GetDecimal(22);
                    data[key].TotalAmount += dAmount;
                    ****data[key].ItemList.Add(new NomsPRItem()****
                    {
                        RequestID = key,
                        PartDesc = reader.GetString(17),
                        PartNumber = reader.GetString(23),
                        SupplierID = reader.GetString(18),
                        FullName = reader.GetString(7),
                        AccountType = reader.GetString(19),
                        CurrName = reader.GetString(20),
                        PartQuantity = (double)reader.GetDecimal(21),
                        PiecePrice = (double)reader.GetDecimal(22),
                        Amount = dAmount
                    });
                }
            }
        }

        return data.Values.ToList();
    }

I get error on this part

data[key].ItemList.Add(new NomsPRItem()

Here is my view ..

  <table data-ng-show="models != null" class="table table-striped table-bordered table-hover"
                id="PRTable" >

                <tr class="titlerow">
                    <th>
                        <a href="#" data-ng-click="sorting='RequestDate'; reverse = !reverse">PR Date <span
                            data-ng-show="sorting == 'RequestDate'"></span>
                        </a>
                    </th>

                    <th>
                        <a href="#" data-ng-click="sorting='RequestID '; reverse = !reverse">PR # <span data-ng-show="sorting == 'RequestID '"></span>
                        </a>
                    </th>

                    <th>
                        <a href="#" data-ng-click="sorting='PARNumber '; reverse = !reverse">PAR # <span
                            data-ng-show="sorting == 'PARNumber '"></span>
                        </a>
                    </th>

                    <th>
                        <a href="#" data-ng-click="sorting='ProgramName '; reverse = !reverse">Program <span
                            data-ng-show="sorting == 'ProgramName '"></span>
                        </a>
                    </th>

                    <th>
                        <a href="#" data-ng-click="sorting='FullName '; reverse = !reverse">Requestor <span
                            data-ng-show="sorting == 'FullName '"></span>
                        </a>
                    </th>

                    <th>
                        <a href="#" data-ng-click="sorting='DepartmentName '; reverse = !reverse">Department <span
                            data-ng-show="sorting == 'FullName '"></span>
                        </a>
                    </th>


                    <th>
                        <a href="#" data-ng-click="sorting='PONo'; reverse = !reverse">PO # 
                        </a>
                    </th>
                    <th>
                        <a href="#" data-ng-click="sorting='StatusID '; reverse = !reverse">PRStatus<span
                            data-ng-show="sorting == 'StatusID '"></span>
                        </a>
                    </th>
                    <th>
                        <a href="#" data-ng-click="sorting='Amount '; reverse = !reverse">Total Amount<span
                            data-ng-show="sorting == 'Amount '"></span>
                        </a>
                    </th>

                    <th>
                        <a href="#" data-ng-click="sorting='InboxLearUID '; reverse = !reverse">Last Action<span
                            data-ng-show="sorting == 'InboxLearUID '"></span>
                        </a>
                    </th>
                </tr>


                <tr data-ng-repeat="model in models  | orderBy: sorting:reverse | filter : filterAllColumns | filter : filterOptions ">

                    <td>{{jsonDatetotext(model.RequestDate) | date:'MM/dd/yyyy'}}</td>
                    <td>
                        <a href="#" data-toggle="modal" data-target="#basicModalContent" data-ng-click="getSelectedPR(model)">{{model.RequestID}}
                        </a>
                    </td>
                    <td>{{model.PARNumber }}</td>
                    <td>{{model.ProgramName }}</td>
                    <td>{{model.FullName }}</td>
                    <td>{{model.DepartmentName | uppercase}}</td>
                    <td>{{model.PONo}}</td>
                    <td>{{StatusList[model.StatusID] | uppercase}}</td>
                    <td class="totalAmount"><span class="pull-right">{{model.TotalAmount | number:2}}</span>
                    </td>
                    <td>{{model.InboxLearUID | lowercase}}</td>
                </tr>
            </table>
        </div>
        <!-- /.Modal Na ni -->

        <div class="modal fade" id="basicModalContent" tabindex="-1" role="dialog" aria-labelledby="myModalLabel"
            aria-hidden="true">
            <div class="modal-dialog">
                <div class="modal-content">
                    <div class="modal-header">

                        <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                    </div>
                    <div class="modal-body" id="exportablePRItems">
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered table-hover" id="dataTables-example">
                                <thead>
                                    <tr>

                                        <th>Item Code
                                        </th>
                                        <th>Item Description
                                        </th>
                                        <th>Supplier 
                                        </th>
                                        <th>Account
                                        </th>
                                        <th>Currency
                                        </th>
                                        <th>Amount
                                        </th>
                                        <th>(USD) Amount
                                        </th>
                                    </tr>
                                </thead>

                                <tbody data-ng-repeat="selectedPR in selectedModal.ItemList">

                                    <tr>
                                        <td>{{selectedPR.PartNumber}}</td>
                                        <td>{{selectedPR.PartDesc}}</td>
                                        <td>{{selectedPR.SupplierID }}</td>
                                        <td>{{selectedPR.AccountType}}</td>
                                        <td>{{selectedPR.CurrName }}</td>
                                        <td data-ng-model="amount" class="amount">{{selectedPR.Amount | number:2}}</td>
                                        <td>{{selectedPR.AmountUSD}}</td>
                                    </tr>
                                </tbody>
                                <tr>
                                    <td><span class="pull-right"><i class="glyphicon glyphicon-plus-sign"></i></span></td>
                                    <td colspan="3"><b>{{selectedModal.RequestID}}</b> </td>
                                    <td colspan="1"><b>Total :  </b></td>
                                    <td colspan="2">{{selectedModal.ItemList | sumbykey : 'Amount' | number:2}}</td>
                                </tr>

                            </table>

                        </div>
                        <footer>
                            <br />
                            <button data-ng-click="exportDataItems()" class="btn btn-warning"><i class="glyphicon glyphicon-export"></i>Export Item </button>
                        </footer>
                    </div>
                    <div class="modal-footer">

                        <button id="btnModalCancel" type="button" class="btn btn-default" data-dismiss="modal">
                            Close</button>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

Can anyone please help me to investigate this codes so that I could proceed to my next steps ?

It's more likely to be the data in the `reader` than the specific line you pointed out. That line technically continues down to the `});` — ethorn10, May 26 '15 at 03:12
First thing you might want to do is to refactor your SqlCommand text to use SqlParameters, those string concatenation you use are tricky, not to tell that it is almost prohibitive approach. — E-Bat, May 26 '15 at 03:12

score 2 · Answer 1 · answered May 26 '15 at 03:25

I think the issue is in the line to convert decimal type into double. If the value of 21 and 22 return DbNull then you can't convert that type into double. I would suggest to:

Make sure that value at index 21 and 22 return valid decimal number. You can use the IsNull() function in sql query to do so.
Try to use reader.GetDouble(21) and reader.GetDouble(22) instead of type conversion if your intent is to get a double value.

Alternatively, you can do a little debugging by extract that object initialization code into smaller parts:

        var npri = new NomsPRItem();
        npri.RequestID = key;
        npri.PartDesc = reader.GetString(17);
        npri.PartNumber = reader.GetString(23);
        npri.SupplierID = reader.GetString(18);
        npri.FullName = reader.GetString(7);
        npri.AccountType = reader.GetString(19);
        npri.CurrName = reader.GetString(20);
        npri.PartQuantity = (double)reader.GetDecimal(21);
        npri.PiecePrice = (double)reader.GetDecimal(22);
        npri.Amount = dAmount;

This will point down exactly which column index that causing the issue.

Oh, it's just my habit of using var for debugging. To me double and decimal are different. I use double for mathematics calculation or fractions; decimal for money datatype which provide more accuracy in financial calculation. — Tran Nguyen, May 26 '15 at 03:59
Ahh . Ok .. So i will use double for non-money and decimal for money ? — Mijevoli, May 26 '15 at 04:50
Yes, that's correct. You can see this best answer here for more information: http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net — Tran Nguyen, May 26 '15 at 05:14

score 1 · Accepted Answer · answered May 26 '15 at 03:41

Better use IsDBNull .. just like this one :

                        RequestID = key,
                        PartDesc = reader.IsDBNull(17) ? null : reader.GetString(17),
                        PartNumber = reader.IsDBNull(23) ? null : reader.GetString(23),
                        SupplierID = reader.IsDBNull(18) ? null : reader.GetString(18),
                        FullName = reader.IsDBNull(7) ? null : reader.GetString(7),
                        AccountType = reader.IsDBNull(19) ? null :  reader.GetString(19),
                        CurrName = reader.IsDBNull(20) ? null :  reader.GetString(20),
                        PartQuantity = (double)reader.GetDecimal(21),
                        PiecePrice = (double)reader.GetDecimal(22),
                        Amount = dAmount

I hope it can help

Thanks @anaiah .. I have really met those kinds of explanations but you did elaborate it :) — Mijevoli, May 26 '15 at 03:51

Data is Null in my case in my List.Add

2 Answers2