2

My datatable salary column is string column and user gives input value in string format, like userInput="1000". But data stored in salary column is as shown in below image. How do I compare user input with data stored in data table?

enter image description here

My current code is

DataRows[] rows = dt.Select("Salary='"+userInput+"'");
Arghya C
  • 9,805
  • 2
  • 47
  • 66
Kiran Malgi
  • 97
  • 1
  • 1
  • 6

2 Answers2

5

to avoid formatting issues you should better compare numeric values. DataTable select filter supports Convert function:

var dt = new DataTable();
dt.Columns.Add("Salary", typeof(string));

// test values
dt.Rows.Add("0.10000");
dt.Rows.Add(".1");
dt.Rows.Add("-.1");
dt.Rows.Add("1.1");

decimal salary;
string userInput = "0.10";
if (decimal.TryParse(userInput, out salary))
{           
    DataRow[] matchedRows = dt.Select("Convert(Salary, 'System.Decimal') = " + salary.ToString());
    foreach(var r in matchedRows)
        Console.WriteLine(r["Salary"]);
}

output is:

.1
0.10000
ASh
  • 34,632
  • 9
  • 60
  • 82
3

I would use Linq-To-DataTable:

decimal value;
if(decimal.TryParse(userInput, out value))
{
   var filteredRows = from row in dt.AsEnumerable()
                      where row.Field<decimal>("Salary") == value
                      select row;
   // if you want an array:
   DataRow[] rows = filteredRows.ToArray();
}

If you insist on using DataTable.Select you have to remove the apostrophes:

DataRow[] rows = table.Select("Salary=" + userInput);

But this presumes that the input value is using the english format(so f.e. not using a different decimal separator like comma that even causes a SyntaxErrorException).

If the type of the column is actually string you need to parse it to double or decimal:

var filteredRows = from row in dt.AsEnumerable()
                   where decimal.Parse(row.Field<string>("Salary")) == value
                   select row;

But in this cae you should better fix the wrong datatype instead.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 1
    I understood OP that his`Salary` column is of type string too. So does `row.Field` really work? – René Vogt Dec 07 '15 at 10:38
  • @RenéVogt: i think it's double or decimal, that's why OP's code doesn't work. If he insisted on using `DataTable.Select` he would need to remove the apostrophes like in: `dt.Select("Salary=" +userInput)`. But then it depends on the current culture if the string conversion doesn't cause another issue. – Tim Schmelter Dec 07 '15 at 10:40