DataTable Select function cannot perform "=" operation on System.Double and System.String

Help
1

Hi all,

I am trying to select rows to delete from a data table using the dt.Select(query) method. However, the system throws out error message “Cannot perform ‘=’ operation on System.Double and System.String”.

Any idea how to overcome this issue?

  1. I have assigned {“B012345”, “B012346”} into a String array
  2. When I loop through each code inside the String array, I assigned strQuery = String.Format(“Agent=‘{0}’”, code)
  3. When trying to select rows using rowsToDelete = dt.Select(strQuery), I got the above error message.

The “Agent” data column in my Excel consist of both numeric only and alphanumeric codes.

Thanks in advance for any response to my question.

Regards,

Tew

2

Hi @leepoo,

Try to remove the single quotes from the query string.

strQuery = String.Format("Agent={0}")

Or

strQuery = String.Format("Agent LIKE '%{0}%'")

2 Likes
3

Hi acaciomelo,

Thanks for your suggestions.

However, both methods not working too.

The first one is throwing out "Cannot find column [System.Data.Datarow]

The second one is giving me the same error as I get earlier “Cannot perform ‘=’ operation on System.Double and System.String”

4

Can you try this way? Attached is solution

deleteRows.xaml (10.1 KB)

List<DataRow> deletedRows = new List<DataRow>(); --- Create a DataRow List

foreach (DataRow dataRow in dataTable.Rows)  // Add rows to List
{
    if(dataRow(0)='B012345') 
    deletedRows.Add(dataRow);
}

foreach(DataRow dataRow in deletedRows) // Delete list
{
    dataRow.Delete();
}
2 Likes
5

That’s because of how internally ReadRange works.

Generated DataTable has types set for all columns as Object, but individual cell types are determined by what type Excel assigned.
This means that a value of “B012345” will be stored as a String, but “12345” as a number (so Double).
You can check it by iterating and checking the Type of each.

Try something like this:
string[] agentList = { "B012345", "B012346" } DataRow[] selectedRows = yourDT.AsEnumerable().Where(Function(x) agentList.Contains(x("Agent").ToString()).ToArray() // proceed to delete selectedRows

4 Likes
6

Hi vvaidya,

Thanks for the response.

Yes, I have changed to the same method as your suggestion and it’s working.

However, I need to save those deleted rows into another Excel file for audit purpose. Is there a way to directly output “deletedRows” datarow into an Excel file the same way we did with datatable?

Thanks,

Tew

7

Hi Andrzej,

Thanks for the explanation on how Excel assign individual cell type based on the data.

If my agentList is in a datatable, not a string array, can I still using the “yourDT.AsEnumerable()” method?

If yes, how would it like?

Regards,

Tew

8

You can create a new datatable and add deleted rows just before you delete the row.

List deletedRows = new List(); — Create a DataRow List

foreach (DataRow dataRow in dataTable.Rows) // Add rows to List
{
if(dataRow(0)=‘B012345’)
deletedRows.Add(dataRow);
}

foreach(DataRow dataRow in deletedRows) // Delete list
{
Add Data Row to new datatable here
dataRow.Delete();
}

Write range to write deleted rows to excel

9

Hi vvaidya,

I tried to perform add data row prior to delete row as per your suggestion. However, I got another error saying “This row already belongs to another table.” when it is trying to execute Delete datarow.

Do you have any idea how to overcome this issue?

Thanks,

Lee Poo

10

foreach (DataRow dataRow in dataTable.Rows) // Add rows to List
{
if(dataRow(0)=‘B012345’)
PLEASE ADD HERE AND CHECK IF IT WORKS
Add Data Row to new datatable here
deletedRows.Add(dataRow);
}

foreach(DataRow dataRow in deletedRows) // Delete list
{

dataRow.Delete();
}

11

I got the same error

12

Check below sample, you should use ImportRow instead of Add Row Item

delRows.xaml (13.8 KB)

2 Likes
13

Hi vvaidya,

Thank you very much for the sample on using Invoke Method, it works.

Regards,

Lee Poo

14

Hello,

I cannot perform “[Sub-Cuenta]<>‘COMPRA/PRODUCCION’”
Assign : Cannot perform ‘<>’ operation on System.Double and System.String.

Because under sub cuenta there is a 511/611 account which is let’s say double.
so this this what I did:

15

This is an interesting problem. Im also encountering this when my source data excel has both Strings and Numbers. Is there a way to force the read range to treat the value as a string? I am using the .ToString method on the datatable cell.