How to loop through a row in each column even the data is NULL or empty string?

Question

I have a VB.NET program which runs as a Windows Service. All it does is connect to a table in SQL Server 2016 and reads data (if any exist) and does some transaction posting to an ERP System. Now in that table (ALL_LABOR_DETAILS) I have 29 columns and some of them could be 'NULL' or empty string value per given row. You can see I've used IsDBNull function for those columns only in my code.

Following is my code which I'm moving through the rows and columns in the dataset but I've noticed whenever it comes to a row with a 'NULL' value for operationComplete or strDefaultBin or strLotNo column it does not read is as a Empty String, instead it tries to move to another row with no NULL record and read that row instead.

Also it throws me this error in my log file whenever it encounters a NULL value in

operationComplete
Error: Conversion from type 'DBNull' to type 'String' is not valid.

Code:

    Dim postCount3 As SqlDataReader

    Dim newServer3 As String = ConfigurationManager.AppSettings("newServer")

    Try
        Using cn3 As New SqlConnection(newServer3),
            cmd3 As New SqlCommand(Sql3, cn3)
            cn3.Open()
            postCount3 = cmd3.ExecuteReader()

            If postCount3.HasRows Then

                While postCount3.Read()

                    Try
                        SourceID = Trim(postCount3.Item(0))
                        strSourceName = Trim(postCount3.Item(1))
                        strOrderNumber = Trim(postCount3.Item(4))
                        strItemNumber = Trim(postCount3.Item(11))
                        strOperationNumber = Trim(postCount3.Item(7))
                        strCurrentStatus = Trim(postCount3.Item(2))
                        strDepartment = Trim(postCount3.Item(5))
                        strWorkCenter = Trim(postCount3.Item(6))
                        strOperator = Trim(postCount3.Item(8))
                        strTrxDate = Trim(postCount3.Item(3))
                        strPosted = Trim(postCount3.Item(14))
                        totalLaborHrs = Trim(postCount3.Item(9))
                        laborFeetProduced = Trim(postCount3.Item(10))

                        If Not IsDBNull(Trim(postCount3.Item(22))) Then
                            operationComplete = Trim(postCount3.Item(22))
                        Else
                            operationComplete = Trim(postCount3.Item(22)).ToString()
                        End If

                        If Not IsDBNull(Trim(postCount3.Item(13))) Then
                            strDefaultBin = Trim(postCount3.Item(13))
                        Else
                            strDefaultBin = Trim(postCount3.Item(13)).ToString()
                        End If

                        If Not IsDBNull(Trim(postCount3.Item(12))) Then
                            strLotNo = Trim(postCount3.Item(12))
                        Else
                            strLotNo = Trim(postCount3.Item(12)).ToString()
                        End If

                        FileIO.WriteLog("")
                        'Print Source  ID
                        FileIO.WriteLog(SourceID)
                        'Print Source Name
                        FileIO.WriteLog(strOrderNumber & " Source Name = " & strSourceName)
                        'Print Order Number
                        FileIO.WriteLog(strOrderNumber & " Order Number = " & strOrderNumber)
                        'Print Item Number
                        FileIO.WriteLog(strOrderNumber & " Item Number = " & strItemNumber)
                        'Print Operation Number
                        FileIO.WriteLog(strOrderNumber & " Operation Number = " & strOperationNumber)
                        'Print Department
                        FileIO.WriteLog(strOrderNumber & " Department = " & strDepartment)
                        'Print Work Center
                        FileIO.WriteLog(strOrderNumber & " Work Center = " & strWorkCenter)
                        'Print Operator
                        FileIO.WriteLog(strOrderNumber & " Operator = " & strOperator)
                        'Print Current Status
                        FileIO.WriteLog(strOrderNumber & " Current Status = " & strCurrentStatus)
                        'Print Total Labor Hours
                        FileIO.WriteLog(strOrderNumber & " Total Labor Hours = " & totalLaborHrs)
                        'Print Labor Feet Produced
                        FileIO.WriteLog(strOrderNumber & " Labor Feet Produced = " & laborFeetProduced)
                        'Print Operation Complete
                        FileIO.WriteLog(strOrderNumber & " Operation Complete = " & operationComplete)
                        'Print Default Bin
                        FileIO.WriteLog(strOrderNumber & " Default Bin = " & strDefaultBin)
                        'Print Lot No
                        FileIO.WriteLog(strOrderNumber & " Lot No = " & strLotNo)

                    Catch ex As Exception
                        FileIO.WriteLog("Error : " & ex.Message)
                    End Try

                   Continue While
                   End While
              postCount3.Close()
            cn3.Close()
        End Using
    Catch ex As Exception
        FileIO.WriteLog(strOrderNumber & " There was an error getting the laborDetails FROM ALL_LABOR_DETAILS table WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID. Error Message : " & ex.Message)
    End Try

Answer 1

You are trying to Trim DBNull. Try changing this(the Else)

operationComplete = Trim(postCount3.Item(22)).ToString()

to

operationComplete = postCount3.Item(22).ToString()

Same for the others.