C# Datatable.select fails with # hash character in data

Question

I have a datatable containing a list of service calls placed each month. Column F4 contains the serial number of the machine, now all I wanted to do was find the number of service calls placed for each serial number and add the count to a new a column. I wrote the code below and all was working fine but then a problem arose when a hash sign '#' appeared in the data to reflect an unknown or yet assigned serial number. This caused the error "Cannot perform '=' operation on System.String and System.Int32." on the line "DataRow[] FoundRow = dt.Select("[F4] =" + chkStr); The F4 column is a text/string and I even tried cloning the datatable and manually assigning the column to .DataType = typeof(String); I know its the # sign giving the issue as if I remove from the data for testing purposing all is fine. Can anyone shed some light on this. Many thanks.

// datatable dt contains all my monthly call data
dt.Columns.Add("Counter", typeof(System.Int32)); // add a count column to the datatable            

for (int i = 0; i < dt.Rows.Count; i++) //loop through the datatable
{
string chkStr = dt.Rows[i]["F4"].ToString(); // get 1st serial num, then each other one in loop
DataRow[] FoundRows = dt.Select("[F4] ="  + chkStr); // OK now select all the table rows containing this serial, place in FoundRows               
Int32 count = FoundRows.Length; // get the length which is a count of the same serial num / service calls                
dt.Rows[i]["Counter"] = count;
}

Michael Humelsine Michael Humelsine · Accepted Answer · 2015-01-12T20:32:01

Don't worry about counting the value as in Int32. Who cares; you can still "use" the number when it is a string. Read it as a string, "#" will be valid and numbers will be valid. Here is a simple solution that will even tell you how many have not been assigned yet.

public class GroupCounter {
    private Dictionary<string, int> serialNumbers;

    public GroupCounter() {
        this.serialNumbers = new Dictionary<string, int>();
    }

    public Dictionary<string, int> Count(DataTable table) {
        foreach (DataRow row in table.Rows) {
            int counter;
            string serial = row.Field<string>("COLUMN");
            if(!this.serialNumbers.TryGetValue(serial, out counter)){
                this.serialNumbers.Add(serial, 1);
            }
            counter++;
        }
        return this.serialNumbers;
    }
}

Since this returns a Dictionary you can use dictionary[key] (your serial number) to fetch to count for any serial number.

C# Datatable.select fails with # hash character in data

2 Answers