Binding Linq to grid in Visual Studio 2010

I've got 17 years VBA/Access experience, but I'm fairly new to Visual Studio and Linq.

I've been going through various tutorials and I'm stuck trying to bind Linq to a GridView.

When I use this code, it works:

		Dim nwindDataContext As New DBContextNW

		' Products that start with A
		Dim aProducts = _
		 From product In nwindDataContext.Products

		DataGridView1.DataSource = aProducts.ToList()

Open in new window


I get this.
Without a Select
But, when I add a Select clause (and/or a where)

		Dim nwindDataContext As New DBContextNW

		' Products that start with A
		Dim aProducts = _
		 From product In nwindDataContext.Products _
		 Select prodName = product.ProductName

		DataGridView1.DataSource = aProducts.ToList()

Open in new window


I get this.
With Select
What am I doing wrong?

Anyone got know of a good tutorial that makes this stuff make sense?
KapriceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
When you do a Select with an alias (the "prodName =" part of your Select) you are only selecting the "columns" from the data source as listed in the Select. It's the same as when you do a SELECT in the database. The reason you get everything without using a Select is that by default your Linq query will select every "column" in the datasource. (Note that this is VB specific. In C#, if you ever switch, you will always have to include a Select clause.)

The way to achieve the same output with your Select addition would be to either Select the iteration variable (e.g. Select product) or select each "column" you are interested in (e.g. Select prodName = product.ProductName, supId = product.SupplierID, etc.).
0
KapriceAuthor Commented:
But, my Select asks for the Product Name. Why am I getting a Length column?
0
käµfm³d 👽Commented:
Silly question time:  Are you sure that query is the one being bound to the GV?
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

tipsybroomCommented:
you can do it like this:
dataGridView1.DataSource=( from customer in db.Customers
                          where customer.Country == "USA"
orderby customer.CompanyName
select new { customer.CustomerID,
customer.City }).ToBindingList();
dataGridView1.DataBind();

Open in new window


in your example:
DataGridView1.DataSource = (From product In nwindDataContext.Products from { _
 product.ProductName,product.ProductID  _
} )
DataGridView1.DataBind()

Open in new window

0
nepaluzCommented:
This will select products that start with A
Dim aProducts = From product In nwindDataContext.ProductsSelect Where product.ProductName.ToString.StartsWith("A") Select product

Open in new window

This will select only the ProductName column if the product starts with A
Dim aProducts = From product In nwindDataContext.ProductsSelect Where product.ProductName.ToString.StartsWith("A") Select product.ProductName

Open in new window

And finally,this will select all products
Dim aProducts = From product In nwindDataContext.ProductsSelect Select product

Open in new window

PS. Add a .ProductName to the end of the last one and only that column will be returned
0
PaulHewsCommented:
The problem is that you are binding to a list of type string.  That doesn't work well with the datagrid, because the string type is too simple.  The datagrid is looking for a container object with properties for columns.  You can see this behaviour if you create a new list(of String) and bind to that.

You can fix it like so, assuming you don't change your code from the question:

 
DataGridView1.DataSource = aProducts.Select(Function(x) New With {.Value = x}).ToList

Open in new window

0
KapriceAuthor Commented:
Kaufmed,

Silly question time:  Are you sure that query is the one being bound to the GV?

As far as I know. I posted the code in my OP. Does it look like the grid is bound to my query to you?
0
KapriceAuthor Commented:
Paul, here's my code with your suggestion integrated.

	Dim Prods = _
		 From Product In dbContext.Products _
		 Where Product.CategoryID = 1

		DataGridView1.DataSource = Prods.Select(Function(x) New With {.Value = x}).ToList

Open in new window


Here's the result. I'm baffled.
It looks to me like I get the table data when I omit the WHERE but I get an object definition when I include it.
0
käµfm³d 👽Commented:
As far as I know. I posted the code in my OP. Does it look like the grid is bound to my query to you?
You can disregard my question. PaulHews is absolutely right. (I actually feel a bit ashamed that I didn't see that since I've run into it before myself!) Can you try putting the select against the Linq query rather than on the line where you assign to the DataSource?

e.g.

Dim Prods = From Product In dbContext.Products _
            Where Product.CategoryID = 1 _
            Select New With { .Value = Product }

DataGridView1.DataSource = Prods

Open in new window

0
käµfm³d 👽Commented:
P.S.

If that works for you, I'll explain why it is necessary  = )
0
PaulHewsCommented:
This is the code from your original post that I intended you to integrate with mine:
Dim nwindDataContext As New DBContextNW

		' Products that start with A
		Dim aProducts = _
		 From product In nwindDataContext.Products _
		 Select prodName = product.ProductName

		DataGridView1.DataSource = Prods.Select(Function(x) New With {.Value = x}).ToList

Open in new window


When you have this select clause, the query brings back Ienumerable(Of String)  Converting that to a simple list of strings, gives you the length column.  Converting it to a list of objects with a value field, should show the value in the grid.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KapriceAuthor Commented:
Kaufmed, your code causes nothing to show in the grid at all.
0
KapriceAuthor Commented:
Paul, thank you so much! Your code works. And, it works when I add criteria:

		Dim Prods = _
		From product In nwindDataContext.Products _
		Where product.CategoryID = 2 _
		Select prodName = product.ProductName

Open in new window


How can I replace the CategoryID value with a control from the form?

Neither of these work.

This causes a compile error:


		Dim Prods = _
		From product In nwindDataContext.Products _
		Where product.CategoryID = txtCategory.Value _
		Select prodName = product.ProductName

Open in new window


This causes a generic runtime error.

		Dim Prods = _
		From product In nwindDataContext.Products _
		Where product.CategoryID = DirectCast(txtCategory.Value, Integer?) _
		Select prodName = product.ProductName

Open in new window


Thanks again for you help.
0
PaulHewsCommented:
DirectCast is only used when the underlying type is the same.
Ctype should work

Dim Prods = _
            From product In nwindDataContext.Products _
            Where product.CategoryID = CType(txtCategory.Value, Integer?) _
            Select prodName = product.ProductName
0
KapriceAuthor Commented:
Paul, you're my hero! Thanks so much.

I got to DirectCast by trusting the suggestion by resharper.

I never suspected moving from VBA/Access to Visual Studio would be so HARD. My learning curve is KILLING me!
0
KapriceAuthor Commented:
Thank goodness for EE and experts like you who are willing to help.
0
PaulHewsCommented:
The learning curve has definitely gotten more difficult as VB.NET diverges from previous versions of Visual Basic.  I really recommend getting a book to learn LINQ by itself as it's really quite a different language than C# or VB, and the different flavors (LINQ to object, LINQ to XML, LINQ to dataset, LINQ to SQL, Entity Framework) have lots of little gotchas.
0
KapriceAuthor Commented:
Oh, I HAVE, Paul. I've done nothing this week but go through video tutorials (AppDev and TekPub) as well as books on Entity Framework and Linq.

Sadly, some of the stuff is in C#, which I have a hard time following. And, some of it just isn't explained well. For example, I still don't understand lamda expression or why they're needed. The courses I've gone through just haven't explained them well enough for me. All I got was that they're functions without a name that return a single value. That doesn't tell me near enough.

So, I'm trying to learn. I'm just glad I can come here when something doesn't make sense or I get stuck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.