Home > Mobile >  How to solve error "System.InvalidCastException - column contains NULL data"
How to solve error "System.InvalidCastException - column contains NULL data"

Time:11-15

When I run the application and search I got this error

System.InvalidCastException - column contains NULL data

at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal

This is my code DB repository code :

public List<LabResult> Search(string term)
{
    return db.LabResults
             .Where(a => a.PatientNo.ToString() == term)
             .ToList(); // error on this line 
}

This is the view markup:

@model IEnumerable<OracleHIS.Models.LabResult>

@{
    ViewData["Title"] = "Index";
}

<table >
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.PatientNo)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.LabOrderNo)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.PatientNameE)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.LongForiegnDesc)
            </th>
           <th>
                @Html.DisplayNameFor(model => model.ServNumResult)
            </th>
        </tr>
    </thead>
    <tbody>
        @if (Model != null)
        {
            foreach (var item in Model) 
            {
            <tr>
              <td>
                @Html.DisplayFor(modelItem => item.PatientNo)
              </td>
              <td>
                 @Html.DisplayFor(modelItem => item.LabOrderNo)
              </td>
              <td>
                 @Html.DisplayFor(modelItem => item.PatientNameE)
              </td>
              <td>
                 @Html.DisplayFor(modelItem => item.LongForeignDesc)
              </td>
              <td>
                 @Html.DisplayFor(modelItem => item.ServNumResult)
              </td>
              <td>
                @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
              </td>
           </tr>
        }
    }
    </tbody>
</table>

And this is the model class:

namespace OracleHIS.Models
{
    public partial class LabResult
    {
        public decimal PatientNo { get; set; } 
        public decimal LabOrderNo { get; set; }
        public string PatientNameE { get; set; } = null!;
        public string LongForiegnDesc { get; set; } = null!;
        public decimal ServNumResult { get; set; }
    }
}

I found this solution

https://stackoverflow.com/questions/26024722/handle-null-values-when-reading-through-oracledatareader

but where I will use the IsDBNull() in my code?

OracleDataReader provides a IsDBNull() method.

this is the Model in DBset context its a VIEW not TABLE include columns from multiple tables :

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("TRNGKAASH")
                .UseCollation("USING_NLS_COMP");

            modelBuilder.Entity<LabResult>(entity =>
            {
                entity.HasNoKey();

                entity.ToView("LAB_RESULTS");

                entity.Property(e => e.AbnormalFlag)
                    .HasColumnType("NUMBER")
                    .HasColumnName("ABNORMAL_FLAG");

                entity.Property(e => e.ApprovingDateG)
                    .HasColumnType("DATE")
                    .HasColumnName("APPROVING_DATE_G");

                entity.Property(e => e.CancelBy)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("CANCEL_BY");

                entity.Property(e => e.CancelDateG)
                    .HasColumnType("DATE")
                    .HasColumnName("CANCEL_DATE_G");

                entity.Property(e => e.CancelDateH)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("CANCEL_DATE_H");

                entity.Property(e => e.CancelReason)
                    .HasPrecision(6)
                    .HasColumnName("CANCEL_REASON");

                entity.Property(e => e.DateOfBirth)
                    .HasPrecision(8)
                    .HasColumnName("DATE_OF_BIRTH");

                entity.Property(e => e.EndResult)
                    .HasPrecision(6)
                    .HasColumnName("END_RESULT");

                entity.Property(e => e.EventNo)
                    .HasPrecision(4)
                    .HasColumnName("EVENT_NO");

                entity.Property(e => e.GramStain)
                    .HasMaxLength(3000)
                    .IsUnicode(false)
                    .HasColumnName("GRAM_STAIN");

                entity.Property(e => e.GroupNo)
                    .HasPrecision(6)
                    .HasColumnName("GROUP_NO");

                entity.Property(e => e.HeparinFlag)
                    .HasPrecision(1)
                    .HasColumnName("HEPARIN_FLAG");

                entity.Property(e => e.HospitalNo)
                    .HasMaxLength(10)
                    .IsUnicode(false)
                    .HasColumnName("HOSPITAL_NO");

                entity.Property(e => e.InitDiagnisis)
                    .HasMaxLength(300)
                    .IsUnicode(false)
                    .HasColumnName("INIT_DIAGNISIS");

                entity.Property(e => e.LabNo)
                    .HasPrecision(6)
                    .HasColumnName("LAB_NO");

                entity.Property(e => e.LabOrderNo)
                    .HasPrecision(12)
                    .HasColumnName("LAB_ORDER_NO");

                entity.Property(e => e.LastUpdateDate)
                    .HasColumnType("DATE")
                    .HasColumnName("LAST_UPDATE_DATE");

                entity.Property(e => e.LastUpdateTransaction)
                    .HasMaxLength(1)
                    .IsUnicode(false)
                    .HasColumnName("LAST_UPDATE_TRANSACTION");

                entity.Property(e => e.LastUpdateUser)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("LAST_UPDATE_USER");

                entity.Property(e => e.LongForiegnDesc)
                    .HasMaxLength(40)
                    .IsUnicode(false)
                    .HasColumnName("LONG_FORIEGN_DESC");

                entity.Property(e => e.MachineId)
                    .HasColumnType("NUMBER")
                    .HasColumnName("MACHINE_ID");

                entity.Property(e => e.MedicalCheck)
                    .HasPrecision(1)
                    .HasColumnName("MEDICAL_CHECK");

                entity.Property(e => e.MrMerge)
                    .HasPrecision(12)
                    .HasColumnName("MR_MERGE");

                entity.Property(e => e.Nationality)
                    .HasPrecision(6)
                    .HasColumnName("NATIONALITY");

                entity.Property(e => e.PanicFlag)
                    .HasColumnType("NUMBER")
                    .HasColumnName("PANIC_FLAG");

                entity.Property(e => e.PatientCategory)
                    .HasPrecision(6)
                    .HasColumnName("PATIENT_CATEGORY");

                entity.Property(e => e.PatientHospital)
                    .HasMaxLength(10)
                    .IsUnicode(false)
                    .HasColumnName("PATIENT_HOSPITAL");

                entity.Property(e => e.PatientNameA)
                    .HasMaxLength(150)
                    .IsUnicode(false)
                    .HasColumnName("PATIENT_NAME_A");

                entity.Property(e => e.PatientNameE)
                    .HasMaxLength(150)
                    .IsUnicode(false)
                    .HasColumnName("PATIENT_NAME_E");

                entity.Property(e => e.PatientNo)
                    .HasPrecision(12)
                    .HasColumnName("PATIENT_NO");

                entity.Property(e => e.PatientSourceInd)
                    .HasPrecision(6)
                    .HasColumnName("PATIENT_SOURCE_IND");

                entity.Property(e => e.PrioFlag)
                    .HasPrecision(6)
                    .HasColumnName("PRIO_FLAG");

                entity.Property(e => e.ProvidingResource)
                    .HasPrecision(6)
                    .HasColumnName("PROVIDING_RESOURCE");

                entity.Property(e => e.Reason)
                    .HasMaxLength(300)
                    .IsUnicode(false)
                    .HasColumnName("REASON");

                entity.Property(e => e.RefSourceNo)
                    .HasMaxLength(10)
                    .IsUnicode(false)
                    .HasColumnName("REF_SOURCE_NO");

                entity.Property(e => e.RefType)
                    .HasPrecision(6)
                    .HasColumnName("REF_TYPE");

                entity.Property(e => e.ResultNotes)
                    .IsUnicode(false)
                    .HasColumnName("RESULT_NOTES");

                entity.Property(e => e.SampleCollectedBy)
                    .HasPrecision(5)
                    .HasColumnName("SAMPLE_COLLECTED_BY");

                entity.Property(e => e.SampleCollectedDateG)
                    .HasColumnType("DATE")
                    .HasColumnName("SAMPLE_COLLECTED_DATE_G");

                entity.Property(e => e.SampleCollectedDateH)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SAMPLE_COLLECTED_DATE_H");

                entity.Property(e => e.SampleNo)
                    .HasPrecision(12)
                    .HasColumnName("SAMPLE_NO");

                entity.Property(e => e.SampleNote)
                    .HasMaxLength(300)
                    .IsUnicode(false)
                    .HasColumnName("SAMPLE_NOTE");

                entity.Property(e => e.SampleReceivedDateG)
                    .HasColumnType("DATE")
                    .HasColumnName("SAMPLE_RECEIVED_DATE_G");

                entity.Property(e => e.SampleReceivedDateH)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SAMPLE_RECEIVED_DATE_H");

                entity.Property(e => e.SampleRecievedBy)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SAMPLE_RECIEVED_BY");

                entity.Property(e => e.SampleType)
                    .HasPrecision(6)
                    .HasColumnName("SAMPLE_TYPE");

                entity.Property(e => e.ServCancelBy)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SERV_CANCEL_BY");

                entity.Property(e => e.ServCancelDateG)
                    .HasColumnType("DATE")
                    .HasColumnName("SERV_CANCEL_DATE_G");

                entity.Property(e => e.ServCancelDateH)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SERV_CANCEL_DATE_H");

                entity.Property(e => e.ServCancelReason)
                    .HasColumnType("NUMBER")
                    .HasColumnName("SERV_CANCEL_REASON");

                entity.Property(e => e.ServNo)
                    .HasPrecision(6)
                    .HasColumnName("SERV_NO");

                entity.Property(e => e.ServNumResult)
                    .HasColumnType("NUMBER")
                    .HasColumnName("SERV_NUM_RESULT");

                entity.Property(e => e.ServRequestDateG)
                    .HasColumnType("DATE")
                    .HasColumnName("SERV_REQUEST_DATE_G");

                entity.Property(e => e.ServRequestDateH)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SERV_REQUEST_DATE_H");

                entity.Property(e => e.ServRequestDoctorName)
                    .HasMaxLength(150)
                    .IsUnicode(false)
                    .HasColumnName("SERV_REQUEST_DOCTOR_NAME");

                entity.Property(e => e.ServRequestDoctorNo)
                    .HasPrecision(5)
                    .HasColumnName("SERV_REQUEST_DOCTOR_NO");

                entity.Property(e => e.ServRequestUserId)
                    .HasMaxLength(8)
                    .IsUnicode(false)
                    .HasColumnName("SERV_REQUEST_USER_ID");

                entity.Property(e => e.ServTextResult)
                    .HasMaxLength(500)
                    .IsUnicode(false)
                    .HasColumnName("SERV_TEXT_RESULT");

                entity.Property(e => e.ServType)
                    .HasPrecision(6)
                    .HasColumnName("SERV_TYPE");

                entity.Property(e => e.Sex)
                    .HasPrecision(1)
                    .HasColumnName("SEX");

                entity.Property(e => e.SpecialCase)
                    .HasPrecision(6)
                    .HasColumnName("SPECIAL_CASE");
            });

And this is the VIEW declaration in SQL :

enter image description here

enter image description here

I opened view error details and this is the details :

 at Oracle.ManagedDataAccess.Client.OracleDataReader.GetInt32(Int32 i)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at OracleHIS.Models.Repository.LabDbRepository.Search(String term) in D:\HIS\OracleHIS\OracleHIS\Models\Repository\LabDbRepository.cs:line 59
   at OracleHIS.Controllers.LabController.Search(String term) in D:\HIS\OracleHIS\OracleHIS\Controllers\LabController.cs:line 59
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

CodePudding user response:

It is clear that one of the rows have a null for one of the decimal values. To confirm, execute the following sql statement:

Select * from labresults where PatientNo is null or LabOrderNo is null or ServNumResult is null

CodePudding user response:

Your model should match your table/view types including the nullability.

As the table structure shows - all columns can contain null's so you need to mark all properties that are value types(i.e. decimals, ints, DateTimes, etc.) as nullable value types, as you done with decimal's (note that actual exception has changed the problem datatype after changing decimals to descimal?).

If you have nullable reference types enabled in your project you may want to mark reference type properties to.

CodePudding user response:

Change the PatientNo to be:

public decimal? PatientNo { get; set; }

also Don't use .ToString() in where clause

    public List<LabResult> Search(string term)
    {
        var decm = Convert.ToDecimal(term)

        return db.LabResults
                 .Where(a => a.PatientNo == decm)
                 .ToList();
    }
  • Related