Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Binding Linq to grid in Visual Studio 2010

Posted on 2012-03-23
18
Medium Priority
?
319 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

885 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