• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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?
0
Kaprice
Asked:
Kaprice
  • 8
  • 4
  • 4
  • +2
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now