Updating Datasets

[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

 

[William Ryan eMVP]

Hi Gary:

Yeah, this is a problematic approach. Use a DataRelation for this,
http://www.knowdotnet.com/articles/datarelation.html

 

[Gary]

William,

Thanks for the info. I'll give it a try.

Gary

Hi Gary:

Yeah, this is a problematic approach. Use a DataRelation for this,
http://www.knowdotnet.com/articles/datarelation.html

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

 

[Guest]

Hi, Gary

The reason is that you use SqlCommandBuilder to automatically generate command.
2 ways to solve this
1. Write your own UpdateCommand, DeleteCommand and InsertCommand for DA
http://msdn.microsoft.com/library/d...pconUpdatingDatabaseWithDataAdapterDataSet.as
O
2. Fill Contact into one table and Codes into another table. Add relation between the 2 table

Bin Song, MCP

 

[Gary]

Bin,

Thanks for the "heads up" on the updating command for the DA. I was just
wondering how to do this after reading William Ryan's article. If I have
two Data Adapters and fill the dataset with two tables, would I just bind
the Contacts table to the fields on my form and then do the update? Any
help and EXAMPLE code would be appreciated.

Thanks,

Gary

 

[William Ryan eMVP]

Bin,

Thanks for the "heads up" on the updating command for the DA. I was just
wondering how to do this after reading William Ryan's article. If I have
two Data Adapters and fill the dataset with two tables, would I just bind
the Contacts table to the fields on my form and then do the update?

Yes, when you load the form ,load your data and create the datarelations.
From there, set your databindings using a BindingContext. do your edits and
when you are done, update the parent table, then the child table.

HTH,

Bill


Any

 

[Gary]

Bill,

Thanks for the info. I will try what you suggested after dinner and see if
it works. If I need more help, I'll be writing

Thanks,

Gary