Cannot find column [System.Data.DataRowView]

Question

Below is an image of my code and the error:

Further below is my entire code block:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

 

 

namespace WindowsFormsApplication1

{

   

    public partial class Form1 : Form

    {

        private DataTable _table;

 

        public Form1()

        {

            InitializeComponent();

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            var names = new List<string>();

            for (int i = 0; i < listBox1.SelectedItems.Count; i++)

                names.Add(listBox1.SelectedItems[i].ToString());

            // now set the filter

            string filter = (names.Count != 0)

                          ? "CompanyName in (" + string.Join(",", names) + ")"

                          : "0=1";

            var view = new DataView(_table);

            view.RowFilter = filter;

            dataGridView1.DataSource = view;

        }

 

        private void Form1_Load(object sender, EventArgs e)

        {

            SqlConnection cs = new SqlConnection("Data Source=EXCEL-PC;Initial Catalog=NORTHWIND.MDF;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("Select Distinct CompanyName From Customers Order By CompanyName", cs);

 

            _table = new DataTable();

            da.Fill(_table);

 

            listBox1.DataSource = _table;

            listBox1.DisplayMember = "CompanyName";

            listBox1.ValueMember = "CompanyName";

        }

    }

}

 

As you can probably tell, I am trying to pass several selected items from a ListBox to a DataGridView.  My 'source' data is the good old Northwind DB, in SQL Server.  Of course I don't have a Field named 'System.Data.DataRowView'.  I think that's the problem here.  Why would C# not convert the items in the ListBox to an actual Field name in the DB, and pass THAT item to the DataGridView???

Thanks everyone!!

---

Ryan Shuell

Answer 1

Your filter variable is not correct. Tell me what is that you want to filter exactly?

Check out this simple example:

            DataTable table = new DataTable();
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Age", typeof(int));
            table.Rows.Add("name 1", 1);
            table.Rows.Add("name 2", 2);
            table.Rows.Add("name 3", 3);

            DataView view = new DataView(table);
            view.RowFilter = "Age = '2'";

//it filters out 2nd row (name: name2, and age: 2)

You have to do the same, just accommodate it to your needs (data structure).

---

Mitja

Answer 2

Seems to me that you forgot to open your SqlConnection ("cs.Open()").

Regards,

wizend

Answer 3

Actually you've made two errors. First one is 

 names.Add(listBox1.SelectedItems[i].ToString());

You are taking DataRowView and not data itself. Second one you have to use quotation marks, for example

"CompanyName in ('Microsoft')"

Use this line of code:

names.Add(string.Format("'{0}'", ((DataRowView)(listBox1.SelectedItems[i]))[0]));

Answer 4

Thanks for the suggestion, milan.matejka

That looks good, but I still have an error!  Please see the image below, and recommend a next step:

I clicked one item in the ListBox, 'Bon app'.  I thought that would be passed to the DataGridView, and displayed accordingly.  But, it didn't work!!

---

Ryan Shuell

Answer 5

ok, i see. Problem is following. RowFilter supposed to be something like this (how i mentioned)

"CompanyName in ('Microsoft')"

But unluckily your selected company name is Bon app' with quotation mark at the end. RowFilter has SQL syntax so this quotation  mark destroyed previous syntax. There are more solutions, but the easiest one is this one

 for (int i = 0; i < listBox1.SelectedItems.Count; i++)
            {
                string companyName = ((DataRowView)(listBox1.SelectedItems[i]))[0].ToString();
                companyName = companyName.Replace("'", "''");
                names.Add(string.Format("'{0}'", companyName));
            }

I hope this could be proposed as a answer for you.

Answer 6

I can see you have issues with creating a decent filter string.

Since you are selecting multiple items from listBox, youu have to create a bit different filter string, to include all the items from selection.

This is how you can do it (I used example data: 2 columns: id and name - you only accommodate to your needs)!

    public partial class Form1 : Form
    {
        DataTable table;
        public Form1()
        {
            InitializeComponent();
            PopulatingDGV();
            listBox1.SelectionMode = SelectionMode.MultiSimple;
        }

        private string ConvertIntToString(int intParameter)
        {
            return intParameter.ToString();
        }

        private void PopulatingDGV()
        {
            table = new DataTable();
            table.Columns.Add("id", typeof(int));
            table.Columns.Add("name", typeof(string));
            for (int i = 1; i <= 10; i++)
            {
                table.Rows.Add(i, "name " + i);
                listBox1.Items.Add(i.ToString());
            }
            dataGridView1.DataSource = table;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<string> selected = new List<string>();
            for (int i = 0; i < listBox1.SelectedItems.Count; i++)
                selected.Add(listBox1.SelectedItems[i].ToString());
    
            string filter = "";
            for (int i = 0; i < selected.Count; i++)
            {
                filter += string.Format("id = '{0}'", selected[i]);  //Creating filter is self
                if (i + 1 < selected.Count)
                    filter += " OR ";
            }
            DataView view = new DataView(table);
            view.RowFilter = filter;
            //dataGridView1.DataSource = null;
            dataGridView1.DataSource = view;
        }
    }

---

Mitja

Answer 7

Milan is correct with diagnosing the problem (a single quote is contained in the data) and has the proper solution to your problem. Just to clarify though, since it's not obvious from this line of code:

companyName.Replace("'", "''");

The replace is replacing a single quote with 2 single quotes.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 8

I was busy with a few different things over the weekend; now jumping back into this.  Bonnie, I incorporated your code snippet into my project.  Now, it seems like I'm getting the right values passed to the view.RowFilter, but it's still not working and I really don't understand the error now.  Please take a look at the screen shot below. 

Does anyone here have a suggestion as to what I need to do to make this work?

Thanks for everything!!!

---

Ryan Shuell

Answer 9

Ryan, I think you missed the point. Milan's code was only to set the list of names from the SelectedItems (taking care of the single quote problem). You still had to have the rest of the filter set as you had it originally. So, you'd need it to be like this:

// Milan's code
for (int i = 0; i < listBox1.SelectedItems.Count; i++)
{
    string companyName = ((DataRowView)(listBox1.SelectedItems[i]))[0].ToString();
    companyName = companyName.Replace("'", "''");
    names.Add(string.Format("'{0}'", companyName));
}
// Your original code
string filter = (names.Count != 0) 
              ? "CompanyName in (" + string.Join(",", names) + ")"
              : "0=1";
view.RowFilter = filter;

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 10

Hi ryguy72:)

Take a close look at ur codes and there's something wrong with that——

>>string filter = (names.Count != 0)

                         ? "CompanyName in (" + string.Join(",", names) + ")"

                         : "0=1";

If names.Count!=0, this will execute "0=1"——But this isn't right——Have u got a column whose name is 0？ And this isn't an expression ……:(

So please change "0=1" to a correct "columnName=value"

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 11

I guess that setting the filter to "0=1" the intent is to have no rows in the view.

I would have used "false" as filter instead.

Answer 12

Yeah, it doesn't matter ... "0=1" or "false" ... the effect of either one causes no rows in the DataView, which is obviously Ryan's intention.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 13

Hi Ryan, try code, what i posted before.

for (int i = 0; i < listBox1.SelectedItems.Count; i++)
{
	string companyName = ((DataRowView)(listBox1.SelectedItems[i]))[0].ToString();
        companyName = companyName.Replace("'", "''");
        names.Add(string.Format("'{0}'", companyName));
}

i've downloaded northwind database and everything worked fine...

Answer 14

Yeah, it doesn't matter ... "0=1" or "false" ... the effect of either one causes no rows in the DataView, which is obviously Ryan's intention.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

First many thanks to tell me that "0=1" will never return a back result, I thought it was wrong……

Then after experiencing for ages, I found that "In" statement should be do with this：

class MainTest
    {
        static void Main()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(string));
            dt.Rows.Add("a");
            dt.Rows.Add("b");
            List<string> names = new List<string>() { "a","b"};
            DataView dv = new DataView(dt);
            dv.RowFilter = 
           "id in ('"+string.Join("','",names)+"')";
            //Do checking
            Console.WriteLine(dv.RowFilter);
        }
    }

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 15

Whew!  I've got a bit of a headache now; goign to bed soon.  First, I want to say thanks to

Milan!  Your solution makes sense now; didn't really understand what you meant before.  Bonnie, of course your explanation makes sense.

 

I pretty much got this working, only thing it, the DataGridView shows ONLY the CompanyName, and nothing else.  Image below:

Ugh!  I think I need to take a break from this for a day or so.  If someone has a simple explanation as to how to ensure ALL related records are displayed, I’d love to hear it.  In the meantime, I’ll fiddle with the code and see what kind of progress I can make.

 

I’ll share this with the group.  Access and SQL - List boxes can offer the user the ability to choose more than one item, a property known as Multi Select (you can download a working sample file at the bottom of the page).

http://www.fontstuff.com/access/acctut18.htm

This was my original inspiration for wanting to learn how to do this with C# and SQL Server. 

Here is a way to do almost the same thing using ComboBoxes (code sample at the bottom of the second page):

http://www.fontstuff.com/access/acctut17.htm

http://www.fontstuff.com/access/acctut17a.htm

Thanks for the help with this, everyone!!!

---

Ryan Shuell

Answer 16

Ryan -- of course your grid only shows CompanyName. You're binding it to the same DataTable for which you did a SELECT DISTINCT CompanyName FROM Customers from your database. You'll need to either SELECT into a second DataTable with a new database query or get all your Customers first, then filter into a DataView for your grid, and a second DataTable for your ListBox. Something like this:

// Your SELECT from the database would get all Customers and all Columns, not just DISTINCT:
SqlDataAdapter da = new SqlDataAdapter("Select * From Customers Order By CompanyName", cs);
_table = new DataTable();
da.Fill(_table);

// To get a DataTable to bind your ListBox, with Distinct CompanyName:
DataTable dtCompany = this._table.DefaultView.ToTable("true", "CompanyName");
listBox1.DataSource = dtCompany;
listBox1.DisplayMember = "CompanyName";
listBox1.ValueMember = "CompanyName"

// Use exactly the same code you used before for the grid:
for (int i = 0; i < listBox1.SelectedItems.Count; i++)
{
    string companyName = ((DataRowView)(listBox1.SelectedItems[i]))[0].ToString();
    companyName = companyName.Replace("'", "''");
    names.Add(string.Format("'{0}'", companyName));
}
string filter = (names.Count != 0) 
              ? "CompanyName in (" + string.Join(",", names) + ")"
              : "0=1";
view.RowFilter = filter;

// and of course, your grid is bound to your view, which contains all Customer columns
dataGridView1.DataSource = view;

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 17

BonnieB：）

"CompanyName in (" + string.Join(",", names) + ")"
This expression will cause the problem！Because it will render the output result as：
CompanyName in (a,b,c)
But as a string, we must add a pair of single quote——
"CompanyName in ('" + string.Join("','", names) + "')"

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 18

I guess you don't read code so well   ;0)  The line to add those single quotes is already in the code that has been posted above:

names.Add(string.Format("'{0}'", companyName));

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 19

Thanks, once again.  I still seem to have a few issues with my code.  Here is what I'm working with right now:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace WindowsFormsApplication1
{

    public partial class Form1 : Form
    {
        private DataTable _table;

        public Form1()
        {
            InitializeComponent();
        }


        private void button1_Click(object sender, EventArgs e)
        {
            var names = new List<string>();

            // Use exactly the same code you used before for the grid:
            for (int i = 0; i < listBox1.SelectedItems.Count; i++)
            {
                string companyName = ((DataRowView)(listBox1.SelectedItems[i]))[0].ToString();
                companyName = companyName.Replace("'", "''");
                names.Add(string.Format("'{0}'", companyName));
            }
            string filter = (names.Count != 0)
                          ? "CompanyName in (" + string.Join(",", names) + ")"
                          : "0=1";
            var view = new DataView(_table);
            view.RowFilter = filter;

            // and of course, your grid is bound to your view, which contains all Customer columns
            dataGridView1.DataSource = view;

        }



        private void Form1_Load(object sender, EventArgs e)
        {

            // Your SELECT from the database would get all Customers and all Columns, not just DISTINCT:
            SqlDataAdapter da = new SqlDataAdapter("Select * From Customers Order By CompanyName", cs);
            _table = new DataTable();
            da.Fill(_table);

            // To get a DataTable to bind your ListBox, with Distinct CompanyName:
            DataTable dtCompany = this._table.DefaultView.ToTable("true", "CompanyName");
            listBox1.DataSource = dtCompany;
            listBox1.DisplayMember = "CompanyName";
            listBox1.ValueMember = "CompanyName";

        }
    }
}

These are the errors:

The name 'cs' does not exist in the current context

The best overloaded method match for 'System.Data.DataView.ToTable(string, bool, params string[])' has some invalid arguments

Argument 2: cannot convert from 'string' to 'bool'

---

Ryan Shuell

Answer 20

Hi,

Try this——

this._table.DefaultView.ToTable("tableName",true,new string{"col1","col2",……});

See：http://msdn.microsoft.com/en-us/library/h2b6ehaa

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 21

Thanks so much, Bonnie.  I probably couldn't have done this without you.  For the benefit of others who come here after me, here is the final solution:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace WindowsFormsApplication1
{

    public partial class Form1 : Form
    {
        private DataTable _table;

        public Form1()
        {
            InitializeComponent();
        }


        private void button1_Click(object sender, EventArgs e)
        {
            var names = new List<string>();

            // Use exactly the same code you used before for the grid:
            for (int i = 0; i < listBox1.SelectedItems.Count; i++)
            {
                string companyName = ((DataRowView)(listBox1.SelectedItems[i]))[1].ToString();
                companyName = companyName.Replace("'", "''");
                names.Add(string.Format("'{0}'", companyName));
            }
            string filter = (names.Count != 0)
                          ? "CompanyName in (" + string.Join(",", names) + ")"
                          : "0=1";
            var view = new DataView(_table);
            view.RowFilter = filter;

            // and of course, your grid is bound to your view, which contains all Customer columns
            dataGridView1.DataSource = view;

        }



        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source=EXCEL-PC;Initial Catalog=NORTHWIND.MDF;Integrated Security=True");
            SqlDataAdapter da = new SqlDataAdapter("Select * From Customers Order By CompanyName", cs);

            _table = new DataTable();
            da.Fill(_table);

            listBox1.DataSource = _table;
            listBox1.DisplayMember = "CompanyName";
            listBox1.ValueMember = "CompanyName";

        }
    }
}

---

Ryan Shuell

Answer 22

Sorry Ryan, I had a little typo with the code I posted to create a new DataTable. I put "true" in quotes for some stupid reason (been working too much with config files lately I guess). It should have been this:

DataTable dtCompany = this._table.DefaultView.ToTable(true, "CompanyName");

But you're not using that in the final code you posted! You have to use it, otherwise you don't have DISTINCT CompanyNames in your ListBox. Or did you inadvertantly leave that out in the last bit of code that you posted?

Anyway, I'm glad I could help!! BTW ... you shouldn't mark every single reply here as an answer. Only mark the replies that actually were the answer or contributed to helping you discover an answer. It makes it easier for the next person that comes along and reads this thread to know which specific replies actually led to the answer. You should probably unmark a few of these ... ;0)

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 23

Last night, I was just excited to see the code working . . . finally.  I didn't even check to see if the results were indeed distinct, or not.  I'll try your suggestions when I get to the library today.  I need my laptop; I don't have Visual Studio installed on my office computer.

Although some responses were more helpful than others, I think pretty much all of the responses were at least somewhat helpful, so I marked all as 'Answer'.  I do this most of the time.  I post a response whenever I see something that I know the answer to.  I think about 10% of the time people mark my responses as 'Answer'.  As far as I know, all of my responses are correct, but nevertheless, about 90% of the time I don't get any 'points' for my responses.  I try to give other people 'points' whenever I can.

---

Ryan Shuell

Answer 24

>> but nevertheless, about 90% of the time I don't get any 'points' for my responses.

Sorry to hear that but why？many contributors are answering u here:D

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 25

I just got around to trying that new line of code.  It works perfect! 

I'm coming to C# from a VBA background.  I've been using C# as a hobby for the past 4 years.  I'm now trying to devote much more time to learning this stuff. 

Thanks for all the help!!

---

Ryan Shuell

Answer 26

Yay! Glad you got it working, Ryan!!  Have fun learning more stuff!

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 27

C# is more difficult than VBA; orders of magnitudes more difficult (in my opinion).  Nevertheless, I think it is orders of magnitudes more powerful, so I'm committed to this cause.  I think you get out of it what you put into it. 

When I was learning VBA, I recall putting in a lot of time.  There was no substitute for time invested in learning all kinds of different processes.  I'd make a lot of mistakes, learn from those mistakes, and move on.  I think this is going to be similar, with the only major difference being an even larger investment of time. 

I have already completed several projects in VB.NET.  I don't really want to do that anymore VB work, because I'm not learning that much really.  I'd rather spend my time in the C# world; I'll make some mistakes but learn a lot in the process.

Thanks, again, Bonnie.

 

---

Ryan Shuell

Answer 28

For VBA，U should post ur questions at：

http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 29

For VBA，U should post ur questions at：

http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

Wow, that came out of left field!! He's not asking questions about VBA, in fact, he's probably a VBA expert! Don't know what prompted you to post that ...

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 30

Hi bonneB:)

Sorry but I don't know about that……Just see——

>>When I was learning VBA, I recall putting in a lot of time. There was no substitute for time invested in learning all kinds of different processes. I'd make a lot of mistakes, learn from those mistakes, and move on. I think this is going to be similar, with the only major difference being an even larger investment of time.

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 31

Yeap, Yeap.  I'm fine with the VBA stuff,   Let me if you have any questions in the area of VBA. 

I think I'd be making about $180k/year if I knew C# really well.  Instead, I'm making about t$100k/yer with the VBA-stuff. 

---

Ryan Shuell

Answer 32

Yeap, Yeap.  I'm fine with the VBA stuff,   Let me if you have any questions in the area of VBA. 

I think I'd be making about $180k/year if I knew C# really well.  Instead, I'm making about t$100k/yer with the VBA-stuff. 

---

Ryan Shuell

Ah haha……Just do to request for MVP in ur very familiar field！ ：D

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处