Solved

Binding Linq to grid in Visual Studio 2010

Posted on 2012-03-23
18
275 Views
Last Modified: 2012-03-24
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
Comment
Question by:Kaprice
  • 8
  • 4
  • 4
  • +2
18 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
 

Author Comment

by:Kaprice
Comment Utility
But, my Select asks for the Product Name. Why am I getting a Length column?
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Silly question time:  Are you sure that query is the one being bound to the GV?
0
 
LVL 7

Expert Comment

by:tipsybroom
Comment Utility
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
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
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
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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
 

Author Comment

by:Kaprice
Comment Utility
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
 

Author Comment

by:Kaprice
Comment Utility
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
P.S.

If that works for you, I'll explain why it is necessary  = )
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 500 total points
Comment Utility
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
 

Author Comment

by:Kaprice
Comment Utility
Kaufmed, your code causes nothing to show in the grid at all.
0
 

Author Comment

by:Kaprice
Comment Utility
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
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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
 

Author Comment

by:Kaprice
Comment Utility
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
 

Author Closing Comment

by:Kaprice
Comment Utility
Thank goodness for EE and experts like you who are willing to help.
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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
 

Author Comment

by:Kaprice
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now