DataAdapter / Datagridview question.

[dotolee]

I have a question which might sound silly but I'm new to datagrids and data binding in vs 2005.
I have successfully created a datagridview which gets its data from a datatable / table adapter. The sql being executed in the fill method looks like this:

SELECT Contact.FName, Contact.LName, Contact.StreetNum, Street.Name AS Street, City.Name AS City, Contact.PostalCode,
Contact.PhoneNumber, Contact.ApartmentNum, Contact.DoNotCall, Contact.TerritoryID, Territory.Name
FROM Contact INNER JOIN
City ON Contact.CityID = City.CityID INNER JOIN
Street ON Contact.StreetID = Street.StreetID INNER JOIN
Territory ON Contact.TerritoryID = Territory.TerritoryID
WHERE (Territory.Name = @TerrName)​

The code to display the data in the grid looks like this:

Me.LookUpTerritoryByNameTableAdapter.Fill(Me._territor_eDataSet.LookUpTerritoryByName, txtTerritoryName.Text)​

I only want the user to be able to change the territory name. They must specify a name that already exists in the Territory table. The contacts table only has a FK to the Territory Table. How would i write the sql update method?

The code that I've written so far in vb for the SAVE button looks like this:

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
(Me._territor_eDataSet.LookUpTerritoryByName)
Dim table As DataTable
table = Me._territor_eDataSet.LookUpTerritoryByName.GetChanges
If (Not IsNothing(table)) Then
Me.LookUpTerritoryByNameTableAdapter.Update(table)
Me._territor_eDataSet.LookUpTerritoryByName.AcceptChanges()
End If
End Sub​

Please and thanks.

 

[dotolee]

attempted solution....

So far, i've come up with the following SQL Update command:


UPDATE Contact
SET register= @reg, Moved = @Moved, TerritoryID = @territoryID
FROM Contact INNER JOIN
Territory ON Contact.TerritoryID = Territory.TerritoryID
WHERE (@territoryID IN
(SELECT TerritoryID
FROM Territory AS Territory_1))

I know this isn't the final solution because when I attempt to change the territory name to a different that exists using the datagrid, nothing happens. Also if I change one of the other fields, like moved or register for ONE Record, the above command is changing all records included in the datagridview with the new value.
Please help!
Thanks.!

 

[jmcilhinney]

You're going about it the wrong way. You should retrieve each table separately. You would retrieve each of the parent tables and then bind them to combo box columns in the grid. The child table then gets bound to the grid itself. Because of the combo boxes the user will see the street city and territory names instead of the IDs. If you don't want a column to be editable you just make it read-only.

 

[cjard]

I second JMC's notion that the data should be downloaded separately. Consider the territory, city and street tables as lookup tables. The combobox will take responsibility for showing the related data, when it is bound up like:

DataSource = Territory
Display Member = Territory.TerritoryName
Value Member = Territory.TerritoryID
DataMemberName = Contact.TerritoryID


For more info, I think there is a section in the DW2 link in my signature, either about datagridviewcombo specifically, or lookup tables in general. I think also i've posted a movie ont he forums showing datagridviewcombo usage..