3

I got a problem with filtering datatables. One Column is calles BANF-Nummer. Using a filter, I got a error message. I wrote the following code for that:

FilterExpression = "(Kontierungsobjekt = 'Kostenstelle' OR Kontierungsobjekt = 'Co-Auftrag') AND BANF-Nummer > '-1'";
dv.RowFilter = FilterExpression; //ERROR!

The error message is:

Cannot find column [Nummer]

There is another post with a similar problem linked below: Unable to Have (-) Dash in DataView Filter C#

This fix doesn't solve my problem. If I try this:

FilterExpression = "(Kontierungsobjekt = 'Kostenstelle' OR Kontierungsobjekt = 'Co-Auftrag') AND [BANF-Nummer] > '-1'";

dv.RowFilter = FilterExpression; //ERROR!

The error message is then:

Cannot find column [BANF-Nummer]

If I test this function with another column, for example Kostenstelle, it works perfectly. So the error is definitive in the name BANF-Nummer, I cannot change.

Here a screenshot of my problem: enter image description here

Would be grateful for every advice.

Community
  • 1
  • 1
CJens
  • 39
  • 8
  • 2
    The escape characters (`[` and `]`) probably depend on the database type. – Uwe Keim May 02 '17 at 13:31
  • @UweKeim: it has nothing to do with databases because a DataTable is an in memory collection. Documentation is here(remarks section): https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx – Tim Schmelter May 02 '17 at 13:35
  • 1
    What does `dv.Table.Columns.Contains("BANF-Nummer")` return? I suspect `false` – Tim Schmelter May 02 '17 at 13:38
  • No Uwe, thats also not working... thank you. – CJens May 02 '17 at 13:39
  • 1
    Your screenshot says `AND ([BANF-Nummer]) > '-1'` whereas your question says `AND [BANF-Nummer] > '-1'`. (note the `(` and `)` around the square braces). – Uwe Keim May 02 '17 at 13:40
  • You're right Tim, it returns `false` – CJens May 02 '17 at 13:40
  • @CJens: then you know where to look, a typo or other reason why the column is not part of the table (f.e. wrong sql query with other alias). What columns it contains? – Tim Schmelter May 02 '17 at 13:41
  • You're right uwe, but it doesn't make a difference. I changed the picture... – CJens May 02 '17 at 13:43
  • @Tim: Oh, there are 104 columns. I cannot really change that. I'm a developer at Daimler and you can imagine, what it would mean to change such a database in such a huge company... Is there any other way? I think, they sourced it by a excel table and didn't formate it as text in this cell. Greetings form berlin to aachen – CJens May 02 '17 at 13:45
  • @CJens: well, is there any column that could be the one you are looking for, for example `BANF_Nummer`. You have to look at the 104 columns. There is a DataTable visualizer in the debugger. You know the sql query that filled the DataTable? You could look at this to find out if your column is there: `var colList=dv.Table.Columns.Cast().Where(c=>c.ColumnName.StartsWith("BANF", StringComparison.OrdinalIgnoreCase)).ToList();` – Tim Schmelter May 02 '17 at 13:48
  • @Tim: Thank you, I looped all the columns and this column is just called "BANF". On our sharepoint it is written as "BANF-Nummer" Think, I'll contact the person, creating this list and telling him, that is should format that table. – CJens May 02 '17 at 13:50
  • Ok, looping all the columns, it is called *BANF_x002d_Nummer_x0020__x0028_A*; Another field called *Co-Auftrag* is listed as *Co_x002d_Auftrag* Damn it! – CJens May 02 '17 at 14:01

2 Answers2

1

You have to wrap them in squared brackets:

FilterExpression = "(Kontierungsobjekt = 'Kostenstelle' OR Kontierungsobjekt = 'Co-Auftrag') AND [BANF-Nummer] > '-1'";

Thats mentioned in the documentation here

Since you have tried it but it still didn't work:

What does dv.Table.Columns.Contains("BANF-Nummer") return? I suspect false.

You're right Tim, it returns false

Then the table doesn't contain this column, maybe the sql-query that was used to fill this table used an alias or you have a typo.

Use following LINQ query to find your column(your table contains 104 columns as commented):

List<DataColumn> candidates = dv.Table.Columns.Cast<DataColumn>()
   .Where(c=> c.Colum‌​nName.StartsWith("BA‌​NF", StringComparison.OrdinalIgnoreCase))
   .ToList();
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • The OP said that didn't work. Likely this isn't Sql Server. – juharr May 02 '17 at 13:34
  • @juharr: it's not sql at all :) – Tim Schmelter May 02 '17 at 13:56
  • I found the problem. The columnname is - why ever - partly displayed in Unicode Character. My column is named *BANF-Nummer* but listed as *BANF_x002d_Nummer_x0020__x0028_A*. 002d is '-', 0020 for a blank and 0028 for '(' (why ever) and _A. So, there is a little bit a system in it. I just cannot change the source table now, I have to confer with its originator. I think, in the source table, the current cell was not formated as "text". – CJens May 02 '17 at 16:03
  • The source is a sharepoint list (based on a excel worksheet). I puted all the contents to a datatable, because the program, I'm writing this code has still implemented the datatables (RPA from BluePrism). – CJens May 02 '17 at 16:05
  • Tim, it seems, that you're well known with this dataview, or datatable stuff and rowfiltering functions. Is there a possibility, to filter all numbers in a column? Like `string FilterExpression = "Column = 'Any Number'"` but functional. – CJens May 02 '17 at 16:10
  • @CJens yes, you could use linqtodatatable instead. Then it's easy. But I now haven't time to show you. Tomorrow – Tim Schmelter May 02 '17 at 16:12
  • Thank you, I'll google it first and try to get it my self. Thank you. – CJens May 02 '17 at 16:40
  • Have i understood correctly, the column name is: `BANF_x002d_Nummer_x0020__x0028_A`? What is the reason, what does it mean, is there only one column like this? Why you can't use it as column name in your filter-expression? – Tim Schmelter May 03 '17 at 07:47
  • I don't know. x002d is the unicode for '-'. So that is correct. But can I change the encoding type for the sharepoint access? There is not only one column like that. I got a column called *Co-Auftrag* which is loaded as *Co_x002d_Auftrag* This is quite strange. I cannot find any setting like charset or similar to define the encoding. – CJens May 03 '17 at 10:42
  • Ok, I got it - and it's really shitty. In SharePoint 2010, all filed names are internaly stored in unicode format. That means, a blank is _x0020_ In SharePoint2010, a fieldname cannot be longer than 32 character. That means, that this unicode name is very often not stored complettely. In SharePoint 2013, this is fixed. Thank you for all your advices... I have to deal with it. – CJens May 03 '17 at 12:17
0

Ok, I figured out, that internal the name in sharepoint is stored in unicode. https://social.msdn.microsoft.com/Forums/sharepoint/en-US/ea47e82b-273e-48bc-a6cb-c961e2c7c06a/how-to-set-and-get-item-if-the-sharepoint-column-name-has-space?forum=sharepointdevelopmentprevious

And I can convert it like this:

string Key = Field.Key; //Co_x002D_Auftrag
Key = System.Xml.XmlConvert.DecodeName(Key); //Co-Auftrag
Key = System.Xml.XmlConvert.EncodeName(System.Xml.XmlConvert.DecodeName(Key)); //Co_x002D_Auftrag

Now I have to figure out, if - and how - I can use that for my filtering function.

CJens
  • 39
  • 8