Revisiting the datagrid data relation issue

[Earl]

Been down in Tunica on vacation trying to win a poker tournament, but I'm
back to enjoy struggling with the datagrid data relation issue.

What I'm trying to do is relate the ZipCityState ID to the corresponding
name columns in the ZipCityState table. This works fine for displays
purposes, but I cannot do an Insert. Delete works as advertised.

With Insert, I get the error, "Cannot change ReadOnly property for the
Expression column".

In the code below, Form Load calls FillSuppliersGrid()

**********************************************
Private Sub FillSuppliersGrid()

DataGrid1.DataSource = Nothing
dsWinMatrix.Reset()

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmdSuppliers As New SqlCommand("GetAllSuppliers", strSQLServer)

Dim cmdZipMatches As New SqlCommand("GetAllSupplierZips", strSQLServer)
cmdSuppliers.CommandType = CommandType.StoredProcedure
cmdZipMatches.CommandType = CommandType.StoredProcedure
cmdSuppliers.Connection = strSQLServer
cmdZipMatches.Connection = strSQLServer
strSQLServer.Open()

Dim da As New SqlDataAdapter(cmdSuppliers)
Dim daZip As New SqlDataAdapter(cmdZipMatches)
da.Fill(dsWinMatrix, "dtSuppliers")
daZip.Fill(dsWinMatrix, "dtSupplierZips")
dsWinMatrix.Tables("dtSuppliers").Columns("SupplierID").ColumnMapping =
MappingType.Hidden
dsWinMatrix.Tables("dtSuppliers").Columns("Tstamp").ColumnMapping =
MappingType.Hidden
dsWinMatrix.Tables("dtSuppliers").Columns("SupplierZipCityID").ColumnMapping
= MappingType.Hidden
dsWinMatrix.Tables("dtSupplierZips").Columns("ZipCityStateID").ColumnMapping
= MappingType.Hidden

Dim rel As DataRelation
rel = New DataRelation("CityStateZipSupplier", _
dsWinMatrix.Tables("dtSupplierZips").Columns("ZipCityStateID"), _
dsWinMatrix.Tables("dtSuppliers").Columns("SupplierZipCityID"), False)
dsWinMatrix.Relations.Add(rel)

dsWinMatrix.Tables("dtSuppliers").Columns.Add("City", GetType(String),
"Parent(CityStateZipSupplier).City")
dsWinMatrix.Tables("dtSuppliers").Columns.Add("State", GetType(String),
"Parent(CityStateZipSupplier).State")
dsWinMatrix.Tables("dtSuppliers").Columns.Add("Zip", GetType(String),
"Parent(CityStateZipSupplier).Zip")
DataGrid1.DataSource = dsWinMatrix.Tables("dtSuppliers")
cmSuppliers = CType(Me.BindingContext(dsWinMatrix.Tables("dtSuppliers")),
CurrencyManager)
dtSuppliers = dsWinMatrix.Tables("dtSuppliers")
AdjustTableStyle(dtSuppliers)

End Sub

Private Sub AddNewSupplier()
Dim strSQLServer As New SqlConnection(strConnString)
strSQLServer.Open()
dtSuppliers = dsWinMatrix.Tables("dtSuppliers")
dsWinMatrix.EnforceConstraints = False
Dim drNew As DataRow
Try
drNew = dtSuppliers.NewRow
If txtSupplier.Text <> "" Then
drNew("SupplierName") = txtSupplier.Text
Else
drNew("SupplierName") = DBNull.Value
End If
If txtAddress.Text <> "" Then
drNew("SupplierAddress") = txtAddress.Text
Else
drNew("SupplierAddress") = DBNull.Value
End If
If txtPhone.Text <> "" Then
drNew("SupplierPhone") = txtPhone.Text
Else
drNew("SupplierPhone") = DBNull.Value
End If
If txtTollFree.Text <> "" Then
drNew("SupplierPhone2") = txtTollFree.Text
Else
drNew("SupplierPhone2") = DBNull.Value
End If
If txtFax.Text <> "" Then
drNew("SupplierFax") = txtFax.Text
Else
drNew("SupplierFax") = DBNull.Value
End If
If txtRepresentative.Text <> "" Then
drNew("SupplierRep") = txtRepresentative.Text
Else
drNew("SupplierRep") = DBNull.Value
End If
If txtRepPhone.Text <> "" Then
drNew("SupplierRepPhone") = txtRepPhone.Text
Else
drNew("SupplierRepPhone") = DBNull.Value
End If
If txtURL.Text <> "" Then
drNew("SupplierWebsite") = txtURL.Text
Else
drNew("SupplierWebsite") = DBNull.Value
End If
If txtEmail.Text <> "" Then
drNew("SupplierEmail") = txtEmail.Text
Else
drNew("SupplierEmail") = DBNull.Value
End If

dtSuppliers.Rows.Add(drNew)
Dim da As New SqlDataAdapter
Dim dsChanges As DataSet
dsChanges = dsWinMatrix.GetChanges
da.InsertCommand = CreateSupplierInsert()
da.Update(dsChanges, "dtSuppliers")
dsWinMatrix.AcceptChanges()

Catch e_InsertException As System.Exception
MsgBox(e_InsertException.Message)
Throw e_InsertException
Finally
strSQLServer.Close()
End Try

End Sub

Private Function CreateSupplierInsert() As SqlCommand

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("SupplierInsertCommand", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As SqlParameterCollection = cmd.Parameters

System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SupplierName",
System.Data.SqlDbType.VarChar, 50, "SupplierName"))
cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SupplierAddress",
System.Data.SqlDbType.VarChar, 50, "SupplierAddress"))
cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SupplierZipCityID",
System.Data.SqlDbType.Int, 4, "SupplierZipCityID"))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SupplierPhone",
System.Data.SqlDbType.VarChar, 50, "SupplierPhone"))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SupplierPhone2",
System.Data.SqlDbType.VarChar, 50, "SupplierPhone2"))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SupplierFax",
System.Data.SqlDbType.VarChar, 50, "SupplierFax"))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SupplierRep",
System.Data.SqlDbType.VarChar, 50, "SupplierRep"))
cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SupplierRepPhone",
System.Data.SqlDbType.VarChar, 50, "SupplierRepPhone"))
cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SupplierWebsite",
System.Data.SqlDbType.VarChar, 50, "SupplierWebsite"))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SupplierEmail",
System.Data.SqlDbType.VarChar, 50, "SupplierEmail"))

Return cmd

End Function

 

[Kevin Yu [MSFT]]

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're getting an exception which says
"Cannot change ReadOnly property for the Expression column". If there is
any misunderstanding, please feel free to let me know.

Based on my experience, this exception is thrown when you're trying to
modify the value or ReadOnly property of a column that has an expression
set. The column value is auto generated when the Expression property is set
and is set to ReadOnly. Could you please set a breakpoint in your code and
use F10 to step through each line of code to see which line throws this
exception? It will be more helpful to debug this with these information.

If anything is unclear, please feel free to reply to the post.

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

 

[Earl]

Kevin,

It's clearly creating the exception at the point where I try to submit the
changes to the datatable dtSuppliers via the dataadapter. What is not so
clear is why. The rows I'm submitting are not submitting expression columns,
but merely the same columns that exist in the original dtSuppliers table (as
well as the actual database tables). This is why I'm stumped -- I see no
reason for the exception. Thanks for any thoughts you can share on this.

 

[Earl]

Kevin,

Here is a shorter code snippet. If I rem out the 3 lines where I add the
relational columns to the datatable dtSuppliers, I eliminate the exception
later on when I try to add rows. However, the code shown below is needed to
display the data in the grid -- relating the zipcitystate table to the main
suppliers table. I'm either overlooking something or there is something I do
not understand about data relations. Note that I've tried this with
contstraints set to both true and false, and no joy.

Dim rel As DataRelation
rel = New DataRelation("CityStateZipSupplier", _
dsWinMatrix.Tables("dtSupplierZips").Columns("ZipCityStateID"), _
dsWinMatrix.Tables("dtSuppliers").Columns("SupplierZipCityID"), False)
dsWinMatrix.Relations.Add(rel)

'adding columns that do not really exist in the supplier table
'this will be populated from the parent Zip table
'no exception without these 3 lines of code when adding rows
'and this works fine for the datagrid display
'but throws an exception when adding rows

dsWinMatrix.Tables("dtSuppliers").Columns.Add("City", GetType(String),
"Parent(CityStateZipSupplier).City")
dsWinMatrix.Tables("dtSuppliers").Columns.Add("State", GetType(String),
"Parent(CityStateZipSupplier).State")
dsWinMatrix.Tables("dtSuppliers").Columns.Add("Zip", GetType(String),
"Parent(CityStateZipSupplier).Zip")

 

[Kevin Yu [MSFT]]

Hi Earl,

Based on your description, I think the exception is thrown when you call
SqlDataAdapter.Update method. Generally, when an exception is thrown during
update method, we can try to handle SqlDataAdapter.RowUpdating event for
troubleshooting.

We can get the executing command object through
SqlRowUpdatingEventArgs.Command property. Please try to check the
CommandText to see whether the updating command is correct.

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

 

[Kevin Yu [MSFT]]

Hi Earl,

Please check the following link for more information.

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

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

 

[Earl]

Thanks for the insight Kevin. I've added the handlers and looked at the
messages, both with the 3 lines that create the error and also without those
3 lines.

In both cases, OnUpdating returns the same status of 0

In the case of the lines that create the error, OnUpdated returns a status
of 1 whereas without those 3 lines, the status is 0.

I take all of this to mean that OnUpdated is throwing the exception. What is
not clear is how I can use this information to isolate the problem.

 

[Earl]

Kevin, I've resolved this with a phone incident. The solution is to remove
the rows before submitting the dataAdapter Update. Thanks for all your help.