System.Data.EvaluateException: 'Cannot find column [Name].'

  • (2 Pages)
  • +
  • 1
  • 2

29 Replies - 1602 Views - Last Post: 12 October 2018 - 07:43 AM Rate Topic: -----

#1 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 11:01 AM

Hey all,
I'm dipping my toes into dataGridView / using a .csv as a DataSource and running into some issues. I try to filter my dataGridView using

dv.RowStateFilter = DataViewRowState.ModifiedCurrent;



but when I do I get this exception System.Data.EvaluateException: 'Cannot find column [Name].'

The CSV file is automatically generated out of powershell using |export-csv and then I use a quick

                //readlines
                var lines = System.IO.File.ReadAllLines("C:\\AD_project\\test_.csv");

                //skip the first line and then rewrite the file
                System.IO.File.WriteAllLines("C:\\AD_project\\test.csv", lines.Skip(1).ToArray());



to remove the one col header (which messes up the following loop I use to generate the dataGridView from the .csv if not removed)

            string[] str = File.ReadAllLines("C:\\AD_project\\test.csv");

             //(I moved the declaration of datatable into the scope of the form)
            //DataTable dt = new DataTable();

            string[] temp = str[0].Split(',');

            foreach (string t in temp)
            {
                dt.Columns.Add(t, typeof(string));
            }

            for(int i=1;i<str.Length;i++)
            {
                string[] t = str[i].Split(',');

                dt.Rows.Add(t);
            }

            dataGridView1.DataSource = dt;

            


^^/>/>^ so that above creates the dataGridView from .csv and file and then assigns it to the DataTable in the last line. The Csv file has headers at the very top and it loads fine into the dataGridView / DataTable ... When I go into the CSV file using notepad or Excel, there is a col "Name" with no quotes around it. This code blocks works as intended.

The problem occurs when I run the following code block after creating the dataGridView above:

            DataView dv = new DataView(dt);

            dv.RowFilter = "Name = 'M*'";

       dv.RowStateFilter = DataViewRowState.ModifiedCurrent;  

            dt = dv.ToTable();

            dataGridView1.DataSource = dv;


error occurs on this line

 dv.RowStateFilter = DataViewRowState.ModifiedCurrent;


exception is:

System.Data.EvaluateException: 'Cannot find column [Name].'

... the weird thing is that if I put ' ' around Name { dv.RowFilter = "Name = 'M*'"; } in here I don't get the exception but it also doesn't show anything starting with M .. it just clears the dataGridView to blank. Also, in none of the examples that I've seen online is anyone using ' ' s around the column names. It's just XXXX = 'X*' I've spent two days trying to figure this out by searching DuckDuckGo and Google and I can't find any specific answers. please help

thank you! =]

This post has been edited by hexagod: 26 September 2018 - 11:24 AM


Is This A Good Question/Topic? 0
  • +

Replies To: System.Data.EvaluateException: 'Cannot find column [Name].'

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14577
  • View blog
  • Posts: 58,440
  • Joined: 12-June 08

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 11:27 AM

You shouldn't need much.

Your data table, data view, data grid.

My form has a grid, textbox, and button.

The route is dataset to view. View to grid.

Column names should not be put in tick marks else they become values.

 public partial class Form1 : Form
    {
        DataTable dtTemp;
        DataView dvTemp;

        public Form1()
        {
            InitializeComponent();


            dtTemp = new DataTable();
            dtTemp.Columns.Add("sVal");
            dtTemp.Columns.Add("lVal");

            dtTemp.Rows.Add(new Object[] { "a", "1" });//adding temp data
            dtTemp.Rows.Add(new Object[] { "b", "2" });//adding temp data

            dvTemp = new DataView(dtTemp);// view gets the table.
           
            dataGridView1.DataSource = dvTemp;//grid datasource gets the view
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //The $ is 'string interpoplation'.. it's new.  
            dvTemp.RowFilter = $"sVal = '{textBox1.Text}'";  //old way String.Format("sVal = '{0}'", textBox1.Text);
        }
}



As it is - the matching is like SQL. You would use % for wildcards and the keyword 'LIKE'

If the data was this:
            dtTemp.Rows.Add(new Object[] { "abc", "1" });
            dtTemp.Rows.Add(new Object[] { "bed", "2" });


.. and I wanted to find things that started with 'a' I would use: "sval like 'a%'"
            dvTemp.RowFilter = $"sVal like '{textBox1.Text}%'";  //old way String.Format("sVal = '{0}'", textBox1.Text);


More examples:
http://www.csharp-ex...view-rowfilter/
Was This Post Helpful? 1
  • +
  • -

#3 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 02:30 PM

I tried

dvTemp.RowFilter = $"sVal like '{textBox1.Text}%'"; 


but got the same exception System.Data.EvaluateException: 'Cannot find column [sVal].' (same if I replace sVal with Name (which is one of the column heads in my powershell generated CSV)

I think it's because my dataTable builder from array isn't naming the columns. Is there any way to assign the name of the columns automatically since they're in the first row? I would have thought and from the examples that I've seen in practice, C# would automatically consider the first row of my dataTable (even if generated with loops from an array) the column [ ]

In your example, you're explicitly naming your columns whereas I generate them with a looping array. Could this be part of the issue?

Thanks for the response modi, appreciate you helping here. I'm going on three days no idea what I'm doing wrong. My format is the exact same as that link you posted in-fact I used it to write the code above (for querying)

This post has been edited by hexagod: 26 September 2018 - 02:31 PM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14577
  • View blog
  • Posts: 58,440
  • Joined: 12-June 08

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 02:37 PM

Are you absolutely certain that is the column name? There are no extra funky characters, spaces, tick marks, illegal characters, etc?

No.. the split isn't the issue. Again. - most likely bad characters. Put a breakpoint and see what's being added.

            string foo = "sVal, lVal";
            string[] bar = foo.Split(',');

            dtTemp = new DataTable();
            foreach (string item in bar)
            {
                dtTemp.Columns.Add(item.Trim());

            }
            //dtTemp.Columns.Add("sVal");
            //dtTemp.Columns.Add("lVal");


Was This Post Helpful? 1
  • +
  • -

#5 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 03:37 PM

"RenderingMode","PrinterStatus","Type","DeviceType","Caption","Description","ElementName","InstanceID","CommunicationStatus","DetailedStatus","HealthState","InstallDate","Name","OperatingStatus",


^^ from the raw unedited CSV

... (there are more but don't want to crowd, you get the idea for format

^^^ that's a sample... but I've tried with Type, DeviceType, ... I don't see any weird characters in there.

See the attached dataTable as-well.. works fine until I try to sort then I get that exception can't find Column Name, despite there clearly being a column named name.


Attached Image

I'm not familiar with breakpoints, so I'll have to research, but I'm sure I can figure it out. I'm perplexed lol, still appreciate the help tho modi =]
Was This Post Helpful? 0
  • +
  • -

#6 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 03:46 PM

Ok so I just removed the quotes from around "Name" in the CSV file and the exception is gone. However, it's still not filtering properly. hmmm
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14577
  • View blog
  • Posts: 58,440
  • Joined: 12-June 08

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 05:25 PM

https://www.dreaminc...4249-debugging/
https://www.dreaminc...ugging-express/


I am pretty certain something else is going wrong. Say I take your file, make my own csv, with that header, and fill it.
            // get file
            StreamReader sr = new StreamReader("C:\\code\\WindowsFormsApp1\\WindowsFormsApp1\\bin\\Debug\\test.txt.csv");
            string foo = sr.ReadLine();

            string[] bar = foo.Split(',');
            dtTemp = new DataTable();

            // get the header
            foreach (string item in bar)
            {
                dtTemp.Columns.Add(item.Trim().Replace("\"", string.Empty)); // remove the quotes
            }

            // the rest of the text
            while((foo = sr.ReadLine()) != null)
            {
                bar = foo.Split(',');
                dtTemp.Rows.Add(bar);
            }

            dvTemp = new DataView(dtTemp);// view gets the table.
            dataGridView1.DataSource = dvTemp;//grid datasource gets the view



Filtering still takes place per normal.
        private void button1_Click(object sender, EventArgs e)
        {
            //The $ is 'string interpoplation'.. it's new.  
            dvTemp.RowFilter = $"Name like '{textBox1.Text}%'";  //old way String.Format("sVal = '{0}'", textBox1.Text);

        }


You may need to remove the quotes around the actual text rows as well.
Was This Post Helpful? 1
  • +
  • -

#8 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6562
  • View blog
  • Posts: 22,523
  • Joined: 05-May 12

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 05:33 PM

hexagod: Please be conscious that WinForms has both DataGrid and DataGridView. It's very confusing when you call something a data grid view, when you are actually playing with a data grid.

Differences Between the Windows Forms DataGridView and DataGrid Controls
Was This Post Helpful? 1
  • +
  • -

#9 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6562
  • View blog
  • Posts: 22,523
  • Joined: 05-May 12

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 05:44 PM

As for parsing the CSV, I suggest using the TextFieldParser that also is in the .NET Framework.

What's a nice class like TextFieldParser doing in a namespace like Microsoft.VisualBasic?

It'll take care of those quoted column names and column values. Even better, it almost completely complies with the RFC regarding CSV files. Pay no attention to the dotNetPearls page saying that the TextFieldParser is slow compared to Split(). Of course, it's slower because it follows the RFC including escaping rules. All Split() knows is that it found a string to split on. Woe to you if you have a Name column that has the value "Donald Trump, Jr.".
Was This Post Helpful? 1
  • +
  • -

#10 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 149
  • View blog
  • Posts: 962
  • Joined: 05-December 13

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 06:10 PM

View Postmodi123_1, on 27 September 2018 - 01:25 AM, said:

            // get file
            StreamReader sr = new StreamReader("C:\\code\\WindowsFormsApp1\\WindowsFormsApp1\\bin\\Debug\\test.txt.csv");
            

Do you not find it easier to call on some reflection rather than all the back slashes and case sensitive typing
            string eAssemblyPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string fPath = Path.Combine(eAssemblyPath, "test.txt.csv");
OR a one liner
            string eAssemblyPathC = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\test.txt.csv";

Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14577
  • View blog
  • Posts: 58,440
  • Joined: 12-June 08

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 26 September 2018 - 06:23 PM

No, i did not think reflection isn't needed and would only complicate things.
Was This Post Helpful? 1
  • +
  • -

#12 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 01 October 2018 - 08:40 AM

Ok all, thank you so much (as always for the help). Here is the complete code of how I got it working. I used multiple sources to put this together. This is how you can sort data from a CSV in C# WinForms using a textBox

some of the code has been removed, such as the initialization of the form itself.

//Assemblies

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
using System.Collections.ObjectModel;



Add these right after the initialization of your Form class
       
//initialize our static dataTable 
static DataTable dt = new DataTable();
//initialize our dataView
        DataView dv = new DataView(dt);


Read from the CSV file inside a button click
       private void button1_Click(object sender, EventArgs e)
        {
//declare a string array and read all lines of the CSV to it
            string[] str = File.ReadAllLines("C:\\AD_project\\test.csv");
//create a temporary string array and split by commas
            string[] temp = str[0].Split(',');

//initialize for loop for every string in the split array

            foreach (string t in temp)
            {
//for every string in the temp array, add a column
                dt.Columns.Add(t, typeof(string));
            }
//initialize for loop, for each addition of i where the string length is greater than //i
            for(int i=1;i<str.Length;i++)
            {

//declare string array for t and assign from split of our CSV array
                string[] t = str[i].Split(',');
//add a row for every string in t
                dt.Rows.Add(t);
            }
//assign dataGridView UI element to the value of our final dt (dataTable)
            dataGridView1.DataSource = dt;

            
        }



Sort the data and re-assign the table to our view
        private void button2_Click(object sender, EventArgs e)
        {
            
//filter the data
            dv.RowFilter = $"Name like '{textBox1.Text}%'";
//turn the dataView into a table
            dt = dv.ToTable();
//assign the dataGridView1 UI element to the new dataTable we created
            dataGridView1.DataSource = dt;

        }




It's working perfectly now.

BTW, you will need two buttons OBV, a dataGridView1 and also a textBox1

This will filter your data table to remove all rows that don't contain the textBox string.

Also, be careful with " and ' marks as they are often inside CSV files and will cause exceptions if not taken into account. That was the source of my initial problem.

This post has been edited by hexagod: 01 October 2018 - 09:09 AM

Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14577
  • View blog
  • Posts: 58,440
  • Joined: 12-June 08

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 01 October 2018 - 08:50 AM

Why would you be converting the view to a table and assigning that table to the grid? Why not table -> view -> grid?

Did you ever actually figure out why your column name was not showing up from your data, but was from mine?
Was This Post Helpful? 0
  • +
  • -

#14 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 01 October 2018 - 09:28 AM

I'm not exactly sure why all of that filter code is necessary, but I wasn't successful when any single one of those lines were removed.

The initial exception was because I wasn't taking into account the " " around CSV file items and I wasn't using the rowFilter command syntax properly. I was mostly viewing the document in Excel and excel automatically removes those quotes. When I removed the " "s around column "Name" , the exception went away.

This post has been edited by hexagod: 01 October 2018 - 09:29 AM

Was This Post Helpful? 0
  • +
  • -

#15 hexagod   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 359
  • Joined: 29-October 16

Re: System.Data.EvaluateException: 'Cannot find column [Name].'

Posted 03 October 2018 - 02:18 PM

View PostSkydiver, on 26 September 2018 - 05:44 PM, said:

As for parsing the CSV, I suggest using the TextFieldParser that also is in the .NET Framework.

What's a nice class like TextFieldParser doing in a namespace like Microsoft.VisualBasic?

It'll take care of those quoted column names and column values. Even better, it almost completely complies with the RFC regarding CSV files. Pay no attention to the dotNetPearls page saying that the TextFieldParser is slow compared to Split(). Of course, it's slower because it follows the RFC including escaping rules. All Split() knows is that it found a string to split on. Woe to you if you have a Name column that has the value "Donald Trump, Jr.".


Interesting you mention this.. I actually already ran into a case where I have commas inside fields. I'm trying to find a similar method for splitting up that CSV file, but now I need the quotes, because I think that's how parsers are going to read the file. I've been searching for an easy way to split the CSV and load into my dataGridView and all of the examples are not working for me. I'll post the updated code block when I've got it.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2