Updating Datasets when join involved

[Gary]

I have a form in which I would like to update one record out of a Contacts
Database. Since I join one other field from another table, I can't update
the data tables. I really want to display the Code_Desc field but there is
no need to update that field. When I populate the form, I use the following
code:

*****************************************
Dim strSQL As String

strSQL = "Select contact.*, cd.[desc] as Code_Desc from Contact
contact "
strSQL &= " INNER JOIN codes cd ON contact.ccode = cd.ccode and
cd.type = 'C' "
strSQL &= " WHERE contact.sysID = '" & g_sysID & "'"

objDS.Clear()

Dim selectCommand As New SqlClient.SqlCommand(strSQL)
DA.SelectCommand = selectCommand
DA.SelectCommand.Connection = CN

Dim cmb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(DA)

DA.Fill(objDS, "Contact")

Me.txtFirstName.DataBindings.Add("Text", objDS,
"Contact.first_name")
Me.txtLastName.DataBindings.Add("Text", objDS, "Contact.Last_name")
Me.txtFullName.DataBindings.Add("Text", objDS, "Contact.full_name")
Me.txtAddress1.DataBindings.Add("Text", objDS, "Contact.con1_02_03")
Me.txtPhone.DataBindings.Add("Text", objDS, "Contact.Phone1")
Me.txtCity.DataBindings.Add("Text", objDS, "Contact.con1_02_05")
Me.txtState.DataBindings.Add("Text", objDS, "Contact.con1_02_06")
Me.txtZip.DataBindings.Add("Text", objDS, "Contact.con1_02_07")
Me.txtEMail.DataBindings.Add("Text", objDS, "Contact.con1_03_01")

***************** Here is the statement that causes trouble **************
Me.cmbCode.DataBindings.Add("Text", objDS, "Contact.Code_Desc")

***************************************************************

Here is the code to do the update:
*************************
BindingContext(objDS.Tables("Contact")).EndCurrentEdit()

Try
'Debug.WriteLine("ObjDS= " &
objDS.Tables(0).Rows(0).Item(1))
DA.Update(objDS, "Contact")

Me.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try
***************************

When I don't have the "Contact.Code_Desc" field to worry about, everything
works with a single table. I don't even need to update the Code_Desc field,
I just want it displayed. How can I update the Contact record without
dealing with the Code_Desc field? Do I need another dataadapter or
something?

Any help would be appreciated.

Thanks,

Gary

 

[Herfried K. Wagner [MVP]]

I have a form in which I would like to update one record out of a Contacts
Database. Since I join one other field from another table, I can't update
the data tables. I really want to display the Code_Desc field but there is
no need to update that field. When I populate the form, I use the following
code:

You will more likely get an answer here:

<URL:
Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

 

[Gary]

Thanks. I placed the "copy" over to the adonet newsgroup and received a
reply.