Any way to edit related columns in the datagrid?

[Earl]

I have two tables set up in a parent-child relationship that I display in
the datagrid. I can edit and update the child data columns directly in the
grid -- but not the parent data, as those parent columns are read-only.

Private Sub FillGrid()

DataGrid1.DataSource = Nothing

dsWinMatrix.Reset()
dsWinMatrix.Relations.Clear()
Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("OilCompaniesSelect", strSQLServer)

'a join query that DISTINCTLY matches up all zip IDs
Dim cmdZipMatches As New SqlCommand("OilCompanyZips", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmdZipMatches.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
cmdZipMatches.Connection = strSQLServer

strSQLServer.Open()

Dim da As New SqlDataAdapter(cmd)
Dim daZip As New SqlDataAdapter(cmdZipMatches)
da.Fill(dsWinMatrix, "dtOilCompanies")
daZip.Fill(dsWinMatrix, "dtOilCompanyZips")

dsWinMatrix.Tables("dtOilCompanies").Columns("OilCompanyID").ColumnMapping =
MappingType.Hidden
dsWinMatrix.Tables("dtOilCompanies").Columns("Tstamp").ColumnMapping =
MappingType.Hidden
dsWinMatrix.Tables("dtOilCompanies").Columns("ZipCityStateID").ColumnMapping
= MappingType.Hidden
dsWinMatrix.Tables("dtOilCompanyZips").Columns("ZipCityStateID").ColumnMapping
= MappingType.Hidden

rel = New DataRelation("ZipCityState_OilCompanies", _
dsWinMatrix.Tables("dtOilCompanyZips").Columns("ZipCityStateID"), _
dsWinMatrix.Tables("dtOilCompanies").Columns("ZipCityStateID"), False)
dsWinMatrix.Relations.Add(rel)

dsWinMatrix.Tables("dtOilCompanies").Columns.Add("City", GetType(String),
"Parent(ZipCityState_OilCompanies).City")
dsWinMatrix.Tables("dtOilCompanies").Columns.Add("State", GetType(String),
"Parent(ZipCityState_OilCompanies).State")
dsWinMatrix.Tables("dtOilCompanies").Columns.Add("Zip", GetType(String),
"Parent(ZipCityState_OilCompanies).Zip")

DataGrid1.DataSource = dsWinMatrix.Tables("dtOilCompanies")
cm = CType(Me.BindingContext(dsWinMatrix.Tables("dtOilCompanies")),
CurrencyManager)

AdjustTableStyle()

End Sub

 

[Kevin Yu [MSFT]]

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you have added some new columns
with expressions, they are read-only in DataGrid. If there is any
misunderstanding, please feel free to let me know.

As far as I know, if we set an expression for a DataColumn, its ReadOnly
property will be set to true automatically. Since the value of the column
is get from other columns, they cannot be edited. This is by design.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks Kevin, that answered the question.

 

[Kevin Yu [MSFT]]

You're welcome, Earl,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks Kevin. This situation is baffling though -- what would be the general
approach for editing this parent information? The Sceppa method of using a
separate form to bring the data back into text boxes and so forth? In my
case, lets say the company had a new address, or a zip/city/state was added
that was incorrect. What would be the best approach to editing that
information?

 

[Kevin Yu [MSFT]]

Hi Earl,

That's a good question! If we can edit an expression column in the child
table, for example, the value is from a parent column. If we change the
value for a row, what shall the DataGrid do for other rows with the same
foreign key? Since rows with the same foreign key should have the same
value in that expression column.

The best approach I think is to use Master-Detail tables. That is to put
the parent and child table in two grids. The following article provides us
with a good example.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbtskcreatingmasterdetailslistwithdatagrid.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks for the idea Kevin. While this would be an excellent choice for a
Customers/Order relationship, I'm not sure that is the answer for my
particular situation, as only the zip, city, and state would be in the
parent table, and would look quite forlorn sitting in a grid by
themselves(although that would certainly give me the ability to edit the
data).

 

[Kevin Yu [MSFT]]

Hi Earl,

If so, I think we can put city/state/zip in comboboxes, if they are to
simply to be put in a DataGrid. Could you let me know the content of the
Child table? Also, we can use table styles of a DataGrid to add drop down
list columns to a table, which makes the foreign key column editable with a
combobox. And makes it display the name instead of ID. Here is a good
article on this. HTH.

http://msdn.microsoft.com/msdnmag/issues/03/08/datagrids/

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Kevin, the user initially enters a zip code to trigger the query that
returns city and state also from the ZipCityState table. That table will
itself never be edited in this context, but rather the Customer's
zip/city/state might change. Using combos is a thought, but they would have
to remain tied together. I will let this gell for a few days, as I'm headed
down to the river to play poker this weekend. Thanks for the ideas.

 

[Kevin Yu [MSFT]]

Hi Earl,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."