Updating Datasets


G

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
 
Ad

Advertisements

G

Gary

William,

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

Gary

William Ryan eMVP said:
Hi Gary:

Yeah, this is a problematic approach. Use a DataRelation for this,
http://www.knowdotnet.com/articles/datarelation.html
Gary said:
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
 
G

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
 
W

William Ryan eMVP

Gary said:
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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DataSet Update 9
Updating a dataset 0
Dataset NOT Updating 15
Dataset updating. 1
DataSet and updating 9
Updating Datasets 1
Update a dataset 9
Dataset update 0

Top