Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Binding Linq to grid in Visual Studio 2010

Posted on 2012-03-23
18
Medium Priority
?
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 4
  • +2
18 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37758762
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
ID: 37758832
But, my Select asks for the Product Name. Why am I getting a Length column?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37758865
Silly question time:  Are you sure that query is the one being bound to the GV?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:tipsybroom
ID: 37758925
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
ID: 37758942
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
ID: 37759324
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
ID: 37759900
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
ID: 37759904
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 75

Expert Comment

by:käµfm³d 👽
ID: 37760579
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37760582
P.S.

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

Accepted Solution

by:
PaulHews earned 2000 total points
ID: 37760672
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
ID: 37761093
Kaufmed, your code causes nothing to show in the grid at all.
0
 

Author Comment

by:Kaprice
ID: 37761114
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
ID: 37761120
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
ID: 37761123
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
ID: 37761125
Thank goodness for EE and experts like you who are willing to help.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 37761144
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
ID: 37761252
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 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