Deal of the Day

Home » Main » Manning Forums » 2007 » LINQ in Action

Thread: editing datagridview with combobox foreign key association

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 7 - Pages: 1 - Last Post: May 11, 2008 11:55 AM by: jwooley
Giocanni

Posts: 13
Registered: 3/5/08
editing datagridview with combobox foreign key association
Posted: Mar 7, 2008 3:14 AM
  Click to reply to this thread Reply

Hello,

Last time you helped me with the join query in linq and now I can visualize all I want in 1 table. But there is 1 field that needs to be updated very often, because it's a status field (shipped, waiting, delivered etc..). in the main table that is a foreign key, StatusID linked to another table with StatusID int, Status nvarchar

1) Is it possible to have a join view of the main table, with a combobox of statusID pointing to the other table and giving the chance to change status with 2 click?
I saw a video where it was very easy to do in ASP, but it doesn't seem so easy to me in VB http://weblogs.asp.net/scottgu/archive/2007/07/16/linq-to-sql-part-5-binding-ui-using-the-asp-linqdatasource-control.aspx

2) Is it possible at all to modify join query results, from the table itself? the whole datagridview doesn't accept input, why? shouldn't be all linked?


thanks

ps. I ordered the book

jwooley

Posts: 123
From: Atlanta, GA
Registered: 11/9/07
Re: editing datagridview with combobox foreign key association
Posted: Mar 7, 2008 6:50 AM   in response to: Giocanni in response to: Giocanni
  Click to reply to this thread Reply

I assume you are refering to a winform environment. There are several things to check. First, make sure you are projecting a complete object and not into an anonymous type in your select clause. Anonymous types are immutable which will cause the DataGridView to open in a read-only mode.

Second, bind your query to a BindingSource on the form and set the DataSource of the grid to that BindingSource.

In the status column on the grid, make it a DataGridViewComboBoxColumn. Set it's datasource to the source of the possible status items. Set the DisplayMember and ValueMember appropriately. Make sure to bind the source of this column before you bind the results of your query to the BindingSource you set in step 2.

Make sure to set a default value on the status column, otherwise when you bind you will get an exception because you are trying to bind to a value that isn't in your status list (the DataGridViewComboBoxColumn's datasource). You can set this in the OnCreated partial method of the class.

I hope this helps.
Jim

Giocanni

Posts: 13
Registered: 3/5/08
Re: editing datagridview with combobox foreign key association
Posted: Mar 7, 2008 10:35 AM   in response to: jwooley in response to: jwooley
  Click to reply to this thread Reply

I thank you for your answer, but there are things that I really don't understand...
when I select a DATASET as StatusBindingSource, and link the combobox to it it works! dont' ask me why. Anyway I don't want to use a dataset... I'm creating everything in linq

I've being trying to do this for days ans still doesn't work.

This is my little database:
http://img519.imageshack.us/img519/6225/dbmapgh9.gif

This is my Status table
http://img527.imageshack.us/img527/3504/statuspx7.gif

This is my datagridview set up.
http://img265.imageshack.us/img265/2617/gridjf0.gif
StatusBindingSource was created thru "add data source -> object -> selected only status table"
ShippingBindingSource is a linq database object with shipping + status table selected



This seems ok to me... maybe the problems are in the code itself?

this is the load form code:

Dim queryall = From s In db.Shippings _
Select s

'something is missing here I guess...

Me.ShippingBindingSource.DataSource = queryall
Me.ShippingDataGridView.DataSource = Me.ShippingBindingSource


1) You say "Make sure to bind the source of this column before you bind the results of your query to the BindingSource you set in step 2."... but what does that mean? I tried in many ways, with no luck


2) You say You can set this in the OnCreated partial method of the class..


I tried to, but I'm not sure that this is the was to do that.....

Partial Class Shipping
Private Sub OnCreated()
MyClass.StatusID = 1
End Sub
End Class


And I always get the same exception, DataGridViewComboBoxColumn value is not valid

:(

jwooley

Posts: 123
From: Atlanta, GA
Registered: 11/9/07
Re: editing datagridview with combobox foreign key association
Posted: Mar 7, 2008 12:45 PM   in response to: Giocanni in response to: Giocanni
  Click to reply to this thread Reply

It appears that you aren't filling the StatusBindingSource, only the ShippingBindingSource. You need to set the StatusBindingSource.DataSource = dc.Statuses on form load prior to Me.ShippingBindingSource.DataSource = queryall.

Jim

Giocanni

Posts: 13
Registered: 3/5/08
Re: editing datagridview with combobox foreign key association
Posted: Mar 7, 2008 3:48 PM   in response to: jwooley in response to: jwooley
  Click to reply to this thread Reply

wow jim... you're a demigod, thanks a lot

jwooley

Posts: 123
From: Atlanta, GA
Registered: 11/9/07
Re: editing datagridview with combobox foreign key association
Posted: Mar 8, 2008 9:47 AM   in response to: Giocanni in response to: Giocanni
  Click to reply to this thread Reply

Not a demigod, just a uber cool nerd god, At least, that's what nerdtests.com says (see http://www.thinqlinq.com/Default.aspx?PostId=21967). Glad to help out.

Jim

ajnatto

Posts: 1
From: Jeddah
Registered: 5/10/08
Re: editing datagridview with combobox foreign key association
Posted: May 10, 2008 3:42 PM   in response to: jwooley in response to: jwooley
  Click to reply to this thread Reply

The Information here was very helpful but i'm using devexress xtraGrid and i can't set the selected item to be an object, just as a text value, for example in the northwind database in the order details table if i update the productID i get an expception on updating the forgin key. Is there a way around this?

Thanks in advance

jwooley

Posts: 123
From: Atlanta, GA
Registered: 11/9/07
Re: editing datagridview with combobox foreign key association
Posted: May 11, 2008 11:55 AM   in response to: ajnatto in response to: ajnatto
  Click to reply to this thread Reply

I don't have any experience with that control. I would suggest you ask that part of the question in their support forums (http://www.devexpress.com/Support/Forums.xml).

Legend
Gold: 300 + pts
Silver: 100 - 299 pts
Bronze: 25 - 99 pts
Manning Author
Manning Staff
Manning Developmental Editor