Unable to search DataTable because row values are null after adding them

Question

I have a weird issue. I am adding columns and rows to a DataTable. I later want to edit a value in one of the rows, which I find by searching for the row by the value in the first column. When I do the search (via the Select() function) I get the error "Cannot find column [key1]". And when I evaluate the expression and look at the rows, the count is correct, but the list containing the row data is null.

If I comment out the code that is causing the error. The DataTable displays correctly in a DataGrid.

I have made a simplified version of the code that exhibits the symptom. In use the number of rows and columns will be unknown.

The code:

class DataGridLine : ViewModelObject
{
    #region local variables
    private DataTable testDT;

    #endregion

    #region properties
    public DataTable TestDT
    {
        get
        {
            return testDT;
        }
        set
        {
            testDT = value;
            OnPropertyChanged("TestDT");
        }
    }
    #endregion

    public ObservableDictionary<string, string> Eng
    {
        get;
        set;
    }

    public ObservableDictionary<string, string> Ger
    {
        get;
        set;
    }

    public ObservableDictionary<string, string> Fre
    {
        get;
        set;
    }

    public DataGridLine()
    {
        Eng = new ObservableDictionary<string, string>();
        Eng.Add("key1", "Eng Val1");
        Eng.Add("key2", "Eng Val2");
        Eng.Add("key3", "Eng Val3");
        Eng.Add("key4", "Eng Val4");

        Ger = new ObservableDictionary<string, string>();
        Ger.Add("key1", "dict2 Val1");
        Ger.Add("key2", "dict2 Val2");
        Ger.Add("key3", "dict2 Val3");
        Ger.Add("key4", "dict2 Val4");

        Fre = new ObservableDictionary<string, string>();
        Fre.Add("key1", "dict3 Val1");
        Fre.Add("key2", "dict3 Val2");
        Fre.Add("key4", "dict3 Val4");

        TestDT = new DataTable();

        TestDT.Columns.Add("Key");
        TestDT.Columns.Add("English");
        TestDT.Columns.Add("German");
        TestDT.Columns.Add("French");

        int numCols = 4;
        string[] rowData = new string[numCols];
        for (int i = 0; i < numCols; i++)
        {
            rowData[i] = null;
        }

        // populate the keys
        foreach (KeyValuePair<string, string> item in Eng)
        {
            rowData[0] = item.Key;

            // load the keys
            TestDT.Rows.Add(rowData);
        }

        // find the key and populate the cell
        foreach (KeyValuePair<string, string> item in Ger)
        {
            DataRow foundRow = TestDT.Select("Key = " + item.Key).FirstOrDefault(); // <- Give error: Cannot find column [key1].
            if (foundRow != null)
            {
                foundRow["German"] = item.Value;
            }
        }
    }
}

The error is happening on the line:

DataRow foundRow = TestDT.Select("Key = " + item.Key).FirstOrDefault();

Why would the variable TestDT show the Rows as null even though the count is correct? Is this why is it unable to find the value I am searching for? If not what is the correct way to search for the row by the value in the first column?

Richardissimo Richardissimo · Accepted Answer · 2018-08-06T18:10:54

I'd suggest using Linq rather than DataTable.Select, since it will give you strong type-checking, and would avoid what I suspect the issue here is, which is that you have not enclosed your value within apostrophes. You need:

DataRow foundRow = TestDT.Select("Key = '" + item.Key + "'").FirstOrDefault();

In Linq it would be:

DataRow foundRow = TestDT.Rows.OfType<DataRow>().FirstOrDefault(r => r["Key"] == item.Key);

Another reason for using the Linq approach is that it's faster.

Unable to search DataTable because row values are null after adding them

1 Answers