Column '' does not allow nulls. But there is no null value

Question

I am doing this on VB.net:

I have a datatable named as 'dt' whose structure is like this:

4 columns: 'OptionId' 'ChoiceId' 'Choice' 'Description'

Datatable dt has roughly 800 records.

I am passing it to a function:

Process(dt,"VehicleChoice;VehicleChoice", "Choice;Description", "Choice;Description", "ChoiceId;ChoiceId")

Public Sub Process(ByRef dt As DataTable, ByVal TableNames As String, ByVal ColumnNames As String, ByVal TableColumnNames As String, ByVal IdentityColumns As String)
    Dim aTableNames As String() = TableNames.Split(";")
    Dim aColumnNames As String() = ColumnNames.Split(";")
    Dim aTableColumnNames As String() = TableColumnNames.Split(";")
    Dim aIdentityColumns As String() = IdentityColumns.Split(";")

    ' remove readonly from columns
     For Each Column As String In aTableColumnNames
         dt.Columns(Column).ReadOnly = False
         dt.Columns(Column).MaxLength = -1
     Next

    ' ProcessFurther
     For Each row As DataRow In dt.Rows
         For i As Integer = 0 To aTableColumnNames.Length - 1
             ProcessFurther(row(aTableColumnNames(i)), aTableNames(i), aColumnNames(i), (row(aIdentityColumns(i)))
         Next
     Next
End Sub

Public Function ProcessFurther(ByRef OriginalValue As Object, ByVal TableName As String, ByVal ColumnName As String, ByVal IdentityValue As Integer) As String
     Dim OriginalValue = Some value which is not even related with any of these codes, but uses the params to retrieve this value.
     Return OriginalValue
End Function

So the function here is plain simple, I don't know why I am getting this error: (From Log4Net)

* Error Message *

Column 'OptionId' does not allow nulls.

* Error Source *

System.Data

* Error Target Site *

Void CheckNullable(System.Data.DataRow)

* Stack Trace *

at System.Data.DataColumn.CheckNullable(DataRow row) 
at System.Data.DataColumn.CheckColumnConstraint(DataRow row, DataRowAction action) 
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) 
at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException) 
at System.Data.DataRow.EndEdit() 
at System.Data.DataRow.set_Item(DataColumn column, Object value) 
at ProcessFurther(DataTable& dt, String TableNames, String ColumnNames, String TableColumnNames, String IdentityColumns) in the For Loop (Stack trace has Line No of that For Loop, not the actual function.)

Biggest confusion for me here is, why/how is 'OptionId' even a problem here. It's not even involved during our processing, though it is just part of the datatable. This makes absolutely no sense to me at all. Also, this happens at random. Like it might happen for 1-2 minutes. And it will be gone for like few hours and will again behave the same in a while. There is no pattern for it, data is still the same when it errors/when it doesn't error out.

Please help if anyone might have a clue on this, Thanks

Answer 1

I think I see where the issue is and it's no shock to me that the problem is that you are using ByRef when you shouldn't be. Stop using ByRef and learn what it actually does, then use it only when it is specifically required. Your ProcessFurther method look like this:

Public Function ProcessFurther(ByRef OriginalValue As Object,
                               ByVal TableName As String,
                               ByVal ColumnName As String,
                               ByVal IdentityValue As Integer) As String

and here is how you call it:

ProcessFurther(row(aTableColumnNames(i)),
               aTableNames(i),
               aColumnNames(i),
               row(aIdentityColumns(i)))

By declaring that parameter ByRef, you are specifically saying whatever is assigned to that parameter inside the method should be assigned back to whatever you passed in in the first place. What you passed in in the first place was row(aTableColumnNames(i)) and therefore, when you assign to the parameter on the first line of the method, you are assigning to that specified column of the DataRow. Presumably what you're assigning is DBNull.Value and that's why you get an error telling you that the column doesn't accept NULL.

When I made the comment on your question about removing ByRef from the parameter, I was actually talking about the dt parameter of the Process method. That one's not hurting you but it shouldn't be there anyway. At that stage, I didn't notice that you were using it elsewhere too. In this case, it is hurting you. DO NOT use ByRef unless you specifically need to. If you don't know when you need to, learn. This is not VB6, where you use it to stop large objects being copied. In VB.NET, any object that is an instance of a class is a reference type and will therefore not be copied when passing by value anyway.