Bound TextBox and Updates in DB

[Guest]

I am building an app that requires related data (parent/child) to be displayed and updated via text boxes. I related the data in the DataSet XML designer. The parent info is a contacts table with addresses, etc. The child info is a locations table which has a foreign key referencing the contact ID. The user selects a location in a ListBox control. The details are then displayed in TextBoxes. All ListBox and TextBox controls are on a TabControl/TabPage on the form. I have bound the controls using what I think are the recommended methods (see below). If I edit the address in one of the text boxes and click on another location, then back to the original, the changed address is persisted. However it is NOT persisted in the database. If I close the app and reload it the change is not there. I have tried everything - calling EndCurrentEdit on the current selected row of the contacts table, etc. but I suspect this does not work because the current row has changed already by the time the Validate event is fired on the text box? I have also tried looping through all Bindings on the form and calling EndCurrentEdit. Iam very frustrated and at a loss at this point to understand what is going on

See code snippets below

In the form load I call
private void InitializeLocations(

string SQL = "SELECT * FROM Contacts"

// Set up SQL for loading location
OleDbCommand oleCom = new OleDbCommand(SQL, oleDbConnectionSA)
OleDbDataAdapter dAdapter = new OleDbDataAdapter(oleCom)

// Do the load from the D
tr

lstLocations.BeginUpdate()
dAdapter.Fill(dsSA, "Contacts")

oleCom.CommandText = "SELECT * FROM Locations"
dAdapter.Fill(dsSA, "Locations")
lstLocations.EndUpdate()

finall

// TODO: Error handlin


// --* Bind data to appropriate controls *-
// Location Name
dsSA.Tables["Locations"].DefaultView.Sort = "Name"
lstLocations.DataSource = dsSA.Tables["Locations"].DefaultView
lstLocations.DisplayMember = "Name"
// Location Note
txtLocNotes.DataBindings.Add("Text", dsSA.Tables["Locations"].DefaultView, "Comment")
// Location Address - From child address field
txtAddress.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "Address")
txtCity.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "City")
txtState.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "StateOrProvince")
txtZip.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "PostalCode")

// --* Make sure changing the Location updates the child data *-
// Create a PositionChanged event handler for Locations dat
BindingManagerBase currency
currency = this.BindingContext[lstLocations.DataSource]
currency.PositionChanged += new EventHandler(Locations_PositionChanged)

// --* Show correct contact info *-
string filter
DataRowView selectedRow
// Find current Locations ro
selectedRow = (DataRowView)this.BindingContext[lstLocations.DataSource].Current
// Create Filter on Contacts I
filter = "ContactID='" + selectedRow["ContactID"].ToString() + "'"
// Change the view into the Contacts tabl
dsSA.Tables["Contacts"].DefaultView.RowFilter = filter


private void txtAddress_Validating(object sender, System.ComponentModel.CancelEventArgs e

DataRowView selectedRow
// Find current Locations ro
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current
selectedRow.EndEdit()

// Tried this as well!
// OnValidating(e)
// foreach (Binding b in this.DataBindings
//
// PropertyManager pm =(PropertyManager)(b.BindingManagerBase)
// pm.EndCurrentEdit()
//
UpdateContacts()


private void UpdateContacts(

// Check for changes with the HasChanges method first
if(!dsSA.HasChanges()) return
// Create temporary DataSet variable
DataSet dsTemp
// GetChanges so we only update the changes
dsTemp = dsSA.GetChanges()
// Check the DataSet for errors
if(dsTemp.HasErrors

// TODO: Insert code to resolve Locations data errors
MessageBox.Show("Error: Could not get changes to Contacts table")

els
{
// Update the data source with the DataAdapter
// used to create the DataSet.
try
{
daContacts.Update(dsTemp);
}
catch (Exception e)
{
// TODO: Error during Update, add code to locate error, reconcile
// and try to update again.
MessageBox.Show("Error: Could not update Contacts table");
}
}
}

 

[Miha Markic [MVP C#]]

Hi,

Does dsTemp contain changes?
How is daContacts configured?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I am building an app that requires related data (parent/child) to be

displayed and updated via text boxes. I related the data in the DataSet XML
designer. The parent info is a contacts table with addresses, etc. The
child info is a locations table which has a foreign key referencing the
contact ID. The user selects a location in a ListBox control. The details
are then displayed in TextBoxes. All ListBox and TextBox controls are on a
TabControl/TabPage on the form. I have bound the controls using what I
think are the recommended methods (see below). If I edit the address in one
of the text boxes and click on another location, then back to the original,
the changed address is persisted. However it is NOT persisted in the
database. If I close the app and reload it the change is not there. I have
tried everything - calling EndCurrentEdit on the current selected row of the
contacts table, etc. but I suspect this does not work because the current
row has changed already by the time the Validate event is fired on the text
box? I have also tried looping through all Bindings on the form and calling
EndCurrentEdit. Iam very frustrated and at a loss at this point to
understand what is going on.

See code snippets below.

In the form load I call
private void InitializeLocations()
{
string SQL = "SELECT * FROM Contacts";

// Set up SQL for loading locations
OleDbCommand oleCom = new OleDbCommand(SQL, oleDbConnectionSA);
OleDbDataAdapter dAdapter = new OleDbDataAdapter(oleCom);

// Do the load from the DB
try
{
lstLocations.BeginUpdate();
dAdapter.Fill(dsSA, "Contacts");

oleCom.CommandText = "SELECT * FROM Locations";
dAdapter.Fill(dsSA, "Locations");
lstLocations.EndUpdate();
}
finally
{
// TODO: Error handling
}

// --* Bind data to appropriate controls *--
// Location Names
dsSA.Tables["Locations"].DefaultView.Sort = "Name";
lstLocations.DataSource = dsSA.Tables["Locations"].DefaultView;
lstLocations.DisplayMember = "Name";
// Location Notes
txtLocNotes.DataBindings.Add("Text", dsSA.Tables["Locations"].DefaultView, "Comment");
// Location Address - From child address fields
txtAddress.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "Address");
txtCity.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "City");
txtState.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "StateOrProvince");
txtZip.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView, "PostalCode");

// --* Make sure changing the Location updates the child data *--
// Create a PositionChanged event handler for Locations data
BindingManagerBase currency;
currency = this.BindingContext[lstLocations.DataSource];
currency.PositionChanged += new EventHandler(Locations_PositionChanged);

// --* Show correct contact info *--
string filter;
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[lstLocations.DataSource].Current;
// Create Filter on Contacts ID
filter = "ContactID='" + selectedRow["ContactID"].ToString() + "'";
// Change the view into the Contacts table
dsSA.Tables["Contacts"].DefaultView.RowFilter = filter;
}


private void txtAddress_Validating(object sender,

System.ComponentModel.CancelEventArgs e)

{
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current;
selectedRow.EndEdit();

// Tried this as well!!
// OnValidating(e);
// foreach (Binding b in this.DataBindings)
// {
// PropertyManager pm =(PropertyManager)(b.BindingManagerBase);
// pm.EndCurrentEdit();
// }
UpdateContacts();
}

private void UpdateContacts()
{
// Check for changes with the HasChanges method first.
if(!dsSA.HasChanges()) return;
// Create temporary DataSet variable.
DataSet dsTemp;
// GetChanges so we only update the changes.
dsTemp = dsSA.GetChanges();
// Check the DataSet for errors.
if(dsTemp.HasErrors)
{
// TODO: Insert code to resolve Locations data errors.
MessageBox.Show("Error: Could not get changes to Contacts table");
}
else
{
// Update the data source with the DataAdapter
// used to create the DataSet.
try
{
daContacts.Update(dsTemp);
}
catch (Exception e)
{
// TODO: Error during Update, add code to locate error, reconcile
// and try to update again.
MessageBox.Show("Error: Could not update Contacts table");
}
}
}

 

[Guest]

Thanks for replying
No dsTemp is never set - single stepping through it the "if(!dsSA.HasChanges()) return;" line always returns. Very strange since the DataSet is persisting the change when I click back and forth. So why does HasChanges always return false

daContacts was left over from a previous attempt to get this to work. It was a "wizard generated" DA so the update SQL is fairly complex. But the daContacts.Update(dsTemp); line never gets called since the HasChanges always returns false

----- Miha Markic [MVP C#] wrote: ----

Hi

Does dsTemp contain changes
How is daContacts configured

--
Miha Markic [MVP C#] - RightHand .NET consulting & developmen
miha at rthand co
www.rthand.co

I am building an app that requires related data (parent/child) to b

displayed and updated via text boxes. I related the data in the DataSet XM
designer. The parent info is a contacts table with addresses, etc. Th
child info is a locations table which has a foreign key referencing th
contact ID. The user selects a location in a ListBox control. The detail
are then displayed in TextBoxes. All ListBox and TextBox controls are on
TabControl/TabPage on the form. I have bound the controls using what
think are the recommended methods (see below). If I edit the address in on
of the text boxes and click on another location, then back to the original
the changed address is persisted. However it is NOT persisted in th
database. If I close the app and reload it the change is not there. I hav
tried everything - calling EndCurrentEdit on the current selected row of th
contacts table, etc. but I suspect this does not work because the curren
row has changed already by the time the Validate event is fired on the tex
box? I have also tried looping through all Bindings on the form and callin
EndCurrentEdit. Iam very frustrated and at a loss at this point t
understand what is going on

See code snippets below
In the form load I cal

private void InitializeLocations(

string SQL = "SELECT * FROM Contacts"

// Set up SQL for loading location

OleDbCommand oleCom = new OleDbCommand(SQL, oleDbConnectionSA)
OleDbDataAdapter dAdapter = new OleDbDataAdapter(oleCom)

// Do the load from the D

tr

lstLocations.BeginUpdate()
dAdapter.Fill(dsSA, "Contacts")

oleCom.CommandText = "SELECT * FROM Locations"

dAdapter.Fill(dsSA, "Locations")
lstLocations.EndUpdate()

finall

// TODO: Error handlin

// --* Bind data to appropriate controls *-

// Location Name
dsSA.Tables["Locations"].DefaultView.Sort = "Name"
lstLocations.DataSource = dsSA.Tables["Locations"].DefaultView
lstLocations.DisplayMember = "Name"
// Location Note
txtLocNotes.DataBindings.Add("Text", dsSA.Tables["Locations"].DefaultView "Comment")
// Location Address - From child address field
txtAddress.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView "Address")
txtCity.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView "City")
txtState.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView "StateOrProvince")
txtZip.DataBindings.Add("Text", dsSA.Tables["Contacts"].DefaultView "PostalCode")

// --* Make sure changing the Location updates the child data *-

// Create a PositionChanged event handler for Locations dat
BindingManagerBase currency
currency = this.BindingContext[lstLocations.DataSource]
currency.PositionChanged += new EventHandler(Locations_PositionChanged)

// --* Show correct contact info *-

string filter
DataRowView selectedRow
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[lstLocations.DataSource].Current;
// Create Filter on Contacts ID
filter = "ContactID='" + selectedRow["ContactID"].ToString() + "'";
// Change the view into the Contacts table
dsSA.Tables["Contacts"].DefaultView.RowFilter = filter;
}

System.ComponentModel.CancelEventArgs e)

{
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current;
selectedRow.EndEdit();

// Tried this as well!!

// OnValidating(e);
// foreach (Binding b in this.DataBindings)
// {
// PropertyManager pm =(PropertyManager)(b.BindingManagerBase);
// pm.EndCurrentEdit();
// }
UpdateContacts();
}

private void UpdateContacts()

{
// Check for changes with the HasChanges method first.
if(!dsSA.HasChanges()) return;
// Create temporary DataSet variable.
DataSet dsTemp;
// GetChanges so we only update the changes.
dsTemp = dsSA.GetChanges();
// Check the DataSet for errors.
if(dsTemp.HasErrors)
{
// TODO: Insert code to resolve Locations data errors.
MessageBox.Show("Error: Could not get changes to Contacts table");
}
else
{
// Update the data source with the DataAdapter
// used to create the DataSet.
try
{
daContacts.Update(dsTemp);
}
catch (Exception e)
{
// TODO: Error during Update, add code to locate error, reconcile
// and try to update again.
MessageBox.Show("Error: Could not update Contacts table");
}
}
}

 

[Miha Markic [MVP C#]]

Hi,

I think that you are getting wrong BindingManagerBase.
Try replacing the row:
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current;
with
selectedRow =
(DataRowView)txtLocNotes.DataBindings["Text"].BindingManagerBase.Current;

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Thanks for replying.
No dsTemp is never set - single stepping through it the

"if(!dsSA.HasChanges()) return;" line always returns. Very strange since
the DataSet is persisting the change when I click back and forth. So why
does HasChanges always return false?

daContacts was left over from a previous attempt to get this to work. It

was a "wizard generated" DA so the update SQL is fairly complex. But the
daContacts.Update(dsTemp); line never gets called since the HasChanges
always returns false.

----- Miha Markic [MVP C#] wrote: -----

Hi,

Does dsTemp contain changes?
How is daContacts configured?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I am building an app that requires related data (parent/child) to

be
displayed and updated via text boxes. I related the data in the DataSet XML
designer. The parent info is a contacts table with addresses, etc. The
child info is a locations table which has a foreign key referencing the
contact ID. The user selects a location in a ListBox control. The details
are then displayed in TextBoxes. All ListBox and TextBox controls are on a
TabControl/TabPage on the form. I have bound the controls using what I
think are the recommended methods (see below). If I edit the address in one
of the text boxes and click on another location, then back to the original,
the changed address is persisted. However it is NOT persisted in the
database. If I close the app and reload it the change is not there. I have
tried everything - calling EndCurrentEdit on the current selected row of the
contacts table, etc. but I suspect this does not work because the current
row has changed already by the time the Validate event is fired on the text
box? I have also tried looping through all Bindings on the form and calling
EndCurrentEdit. Iam very frustrated and at a loss at this point to
understand what is going on.

See code snippets below.
In the form load I call

private void InitializeLocations()
{
string SQL = "SELECT * FROM Contacts";

// Set up SQL for loading locations

OleDbCommand oleCom = new OleDbCommand(SQL, oleDbConnectionSA);
OleDbDataAdapter dAdapter = new OleDbDataAdapter(oleCom);

// Do the load from the DB

try
{
lstLocations.BeginUpdate();
dAdapter.Fill(dsSA, "Contacts");

oleCom.CommandText = "SELECT * FROM Locations";

dAdapter.Fill(dsSA, "Locations");
lstLocations.EndUpdate();
}
finally
{
// TODO: Error handling
}

// --* Bind data to appropriate controls *--

// Location Names
dsSA.Tables["Locations"].DefaultView.Sort = "Name";
lstLocations.DataSource = dsSA.Tables["Locations"].DefaultView;
lstLocations.DisplayMember = "Name";
// Location Notes
txtLocNotes.DataBindings.Add("Text",

dsSA.Tables["Locations"].DefaultView,

"Comment");
// Location Address - From child address fields
txtAddress.DataBindings.Add("Text",

dsSA.Tables["Contacts"].DefaultView,

"Address");

dsSA.Tables["Contacts"].DefaultView,

"City");

dsSA.Tables["Contacts"].DefaultView,

"StateOrProvince");

dsSA.Tables["Contacts"].DefaultView,

"PostalCode");

// --* Make sure changing the Location updates the child data *--

// Create a PositionChanged event handler for Locations data
BindingManagerBase currency;
currency = this.BindingContext[lstLocations.DataSource];
currency.PositionChanged += new EventHandler(Locations_PositionChanged);

// --* Show correct contact info *--

string filter;
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[lstLocations.DataSource].Current;
// Create Filter on Contacts ID
filter = "ContactID='" + selectedRow["ContactID"].ToString() + "'";
// Change the view into the Contacts table
dsSA.Tables["Contacts"].DefaultView.RowFilter = filter;
}

private void txtAddress_Validating(object sender,

System.ComponentModel.CancelEventArgs e)

{
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current;
selectedRow.EndEdit();

// Tried this as well!!

// OnValidating(e);
// foreach (Binding b in this.DataBindings)
// {
// PropertyManager pm =(PropertyManager)(b.BindingManagerBase);
// pm.EndCurrentEdit();
// }
UpdateContacts();
}

private void UpdateContacts()

{
// Check for changes with the HasChanges method first.
if(!dsSA.HasChanges()) return;
// Create temporary DataSet variable.
DataSet dsTemp;
// GetChanges so we only update the changes.
dsTemp = dsSA.GetChanges();
// Check the DataSet for errors.
if(dsTemp.HasErrors)
{
// TODO: Insert code to resolve Locations data errors.
MessageBox.Show("Error: Could not get changes to Contacts table");
}
else
{
// Update the data source with the DataAdapter
// used to create the DataSet.
try
{
daContacts.Update(dsTemp);
}
catch (Exception e)
{
// TODO: Error during Update, add code to locate error, reconcile
// and try to update again.
MessageBox.Show("Error: Could not update Contacts table");
}
}
}

 

[Guest]

That didn't work and neither did:
selectedRow =
(DataRowView)txtAddress.DataBindings["Text"].BindingManagerBase.Current;
Which seemed to make more sense since it is the txtAddress field that I edited.
In both cases the dsSA dataset claims that there are no changes even though it
is persisting them in memory.

----- Miha Markic [MVP C#] wrote: -----

Hi,

I think that you are getting wrong BindingManagerBase.
Try replacing the row:
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current;
with
selectedRow =
(DataRowView)txtLocNotes.DataBindings["Text"].BindingManagerBase.Current;

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Thanks for replying.
No dsTemp is never set - single stepping through it the

"if(!dsSA.HasChanges()) return;" line always returns. Very strange since
the DataSet is persisting the change when I click back and forth. So why
does HasChanges always return false?was a "wizard generated" DA so the update SQL is fairly complex. But the
daContacts.Update(dsTemp); line never gets called since the HasChanges
always returns false.

----- Miha Markic [MVP C#] wrote: -----
Hi,
Does dsTemp contain changes? How is daContacts configured?
--

Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I am building an app that requires related data (parent/child) to

be
displayed and updated via text boxes. I related the data in the DataSet XML
designer. The parent info is a contacts table with addresses, etc. The
child info is a locations table which has a foreign key referencing the
contact ID. The user selects a location in a ListBox control. The details
are then displayed in TextBoxes. All ListBox and TextBox controls are on a
TabControl/TabPage on the form. I have bound the controls using what I
think are the recommended methods (see below). If I edit the address in one
of the text boxes and click on another location, then back to the original,
the changed address is persisted. However it is NOT persisted in the
database. If I close the app and reload it the change is not there. I have
tried everything - calling EndCurrentEdit on the current selected row of the
contacts table, etc. but I suspect this does not work because the current
row has changed already by the time the Validate event is fired on the text
box? I have also tried looping through all Bindings on the form and calling
EndCurrentEdit. Iam very frustrated and at a loss at this point to
understand what is going on.

See code snippets below.
In the form load I call

private void InitializeLocations()
{
string SQL = "SELECT * FROM Contacts";

// Set up SQL for loading locations

OleDbCommand oleCom = new OleDbCommand(SQL, oleDbConnectionSA);
OleDbDataAdapter dAdapter = new OleDbDataAdapter(oleCom);

// Do the load from the DB

try
{
lstLocations.BeginUpdate();
dAdapter.Fill(dsSA, "Contacts");

oleCom.CommandText = "SELECT * FROM Locations";

dAdapter.Fill(dsSA, "Locations");
lstLocations.EndUpdate();
}
finally
{
// TODO: Error handling
}

// --* Bind data to appropriate controls *--

// Location Names
dsSA.Tables["Locations"].DefaultView.Sort = "Name";
lstLocations.DataSource = dsSA.Tables["Locations"].DefaultView;
lstLocations.DisplayMember = "Name";
// Location Notes
txtLocNotes.DataBindings.Add("Text",

dsSA.Tables["Locations"].DefaultView,

"Comment");
// Location Address - From child address fields
txtAddress.DataBindings.Add("Text",

dsSA.Tables["Contacts"].DefaultView,

"Address");

dsSA.Tables["Contacts"].DefaultView,

"City");

dsSA.Tables["Contacts"].DefaultView,

"StateOrProvince");

dsSA.Tables["Contacts"].DefaultView,

"PostalCode");

// --* Make sure changing the Location updates the child data *--

// Create a PositionChanged event handler for Locations data
BindingManagerBase currency;
currency = this.BindingContext[lstLocations.DataSource];
currency.PositionChanged += new EventHandler(Locations_PositionChanged);

// --* Show correct contact info *--

string filter;
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[lstLocations.DataSource].Current;
// Create Filter on Contacts ID
filter = "ContactID='" + selectedRow["ContactID"].ToString() + "'";
// Change the view into the Contacts table
dsSA.Tables["Contacts"].DefaultView.RowFilter = filter;
}

private void txtAddress_Validating(object sender,

System.ComponentModel.CancelEventArgs e)

{
DataRowView selectedRow;
// Find current Locations row
selectedRow = (DataRowView)this.BindingContext[dsSA, "Contacts"].Current;
selectedRow.EndEdit();

// Tried this as well!!

// OnValidating(e);
// foreach (Binding b in this.DataBindings)
// {
// PropertyManager pm =(PropertyManager)(b.BindingManagerBase);
// pm.EndCurrentEdit();
// }
UpdateContacts();
}

private void UpdateContacts()

{
// Check for changes with the HasChanges method first.
if(!dsSA.HasChanges()) return;
// Create temporary DataSet variable.
DataSet dsTemp;
// GetChanges so we only update the changes.
dsTemp = dsSA.GetChanges();
// Check the DataSet for errors.
if(dsTemp.HasErrors)
{
// TODO: Insert code to resolve Locations data errors.
MessageBox.Show("Error: Could not get changes to Contacts table");
}
else
{
// Update the data source with the DataAdapter
// used to create the DataSet.
try
{
daContacts.Update(dsTemp);
}
catch (Exception e)
{
// TODO: Error during Update, add code to locate error, reconcile
// and try to update again.
MessageBox.Show("Error: Could not update Contacts table");
}
}
}