Synchronizing datagridview/listbox and textboxes

To answer your question, synchronization is automatic as

long as you work through one dataset.

Here is my code in VC++. Sorry for the layout, but it will almost be a copy and paste as long as you understand it.

The “->” (reference by pointer) should be replaced by a

dot (“.”) since C# has no pointers. Where dot is used (eg: pCpyCB.DataAdapter),

use a dot also.

There are two tables tblCompany and tblStatus. tblCompany has 5 columns one of which is a foreign key (tblStatus_idStatus) into tblStatus which has 3 columns. cbCpyStatus is a combobox. dgvCpyList is a DGV, and dgvCpyStatus is the combobox column in the DGV. A "tb" suffix usually means a textbox, while "rtb" means richtextbox.

A connection is created in the code at the end marked Form1Shown. Two DataAdapters take these tables into the one dataset.

Because two datasets are used, each requires its own command builder. These are needed by the Update method call during the FormClosing Event in cfrmCpy.h.

The Fill Schema and Fill must be in the order shown. Setting

the AutoIncrement property gets rid of the NonNullNotAllowed exception.

This all goes on one form. The SetUpCpyDataSet is in a

separate compilation unit cfrmCpy.cpp and cfrmCpy.h; use whatever is equivalent

in C#.

Here is cfrmCpy.h (designer code snipped out)

-

#pragma once

using namespace

System;

using namespace

System::ComponentModel;

using namespace

System::Collections;

using namespace

System::Windows::Forms;

using namespace

System::Data;

using namespace

System::Drawing;

using namespace

MySql::Data::MySqlClient;

namespace MDITest7 {

///

<summary>

///

Summary for cfrmCpy

<Snip>

<Snip>

#pragma region

//MDITest Database related code.

//For tblCompany

public:

MySqlDataAdapter^ pCpyDA; //Create DataAdpter

public:

MySqlCommandBuilder CpyCB;//Create Command Builder

//For tblStatus

public:

MySqlDataAdapter^ pStatusDA; //Create DataAdpter

public:

MySqlCommandBuilder StatusCB;//Create Command Builder

public:

void SetUpCpyDataSet(void);//Method

#pragma endregion

//end MDITest manually entered code

private: System::Void

cfrmCpy_Shown(System::Object^sender,

System::EventArgs^e) {

//MessageBox::Show("Company Form

Shown" );

SetUpCpyDataSet();

};

private: System::Void

cfrmCpy_Leave(System::Object^sender,

System::EventArgs^e) {

tbCpyName->DataBindings["Text"]->BindingManagerBase->EndCurrentEdit();

int

bb;//test

bb=pCpyDA->Update(dataSet1,"tblCompany"); //bb

is the number of rows updated

bb=pStatusDA->Update(dataSet1,"tblStatus"); //bb

is the number of rows updated

}//end

cfrmCpy_Leave

private: System::Void

cfrmCpy_FormClosing(System::Object^

sender, System::Windows::Forms::FormClosingEventArgs^e) {

tbCpyName->DataBindings["Text"]->BindingManagerBase->EndCurrentEdit();

tbCpyAddrEtc->DataBindings["Text"]->BindingManagerBase->EndCurrentEdit();

tbCpyPriority->DataBindings["Text"]->BindingManagerBase->EndCurrentEdit();

rtbCpyNotes->DataBindings["Text"]->BindingManagerBase->EndCurrentEdit();

cbCpyStatus->DataBindings["SelectedValue"]->BindingManagerBase->EndCurrentEdit();

int

bb;//test

bb=pCpyDA->Update(dataSet1,"tblCompany"); //bb

is the number of rows updated

bb=pStatusDA->Update(dataSet1,"tblStatus"); //bb

is the number of rows updated

}

//This handler is used to trap the problematic DataError exception

thrown by the //DataGridView, because of a problem in the DGVCombobox column.

Its not clear why //that error occurs, because it sometimes starts at stops at

reandom.

private: System::Void

dgvCpyList_DataError_1(System::Object^

sender,

System::Windows::Forms::DataGridViewDataErrorEventArgs^anError) {

MessageBox::Show(

anError->Exception->Message +

" - ColumnIndex=" +

anError->ColumnIndex.ToString() +

/*" - Data=" +

anError->Exception->Data->ToString()+ */" - "+

anError->Context.ToString(), "DGV

DataError");

if

(anError->Context == DataGridViewDataErrorContexts::Commit)

{

MessageBox::Show("Commit error");

}

if

(anError->Context == DataGridViewDataErrorContexts::CurrentCellChange)

{

MessageBox::Show("Cell change");

}

if

(anError->Context == DataGridViewDataErrorContexts::Parsing)

{

MessageBox::Show("parsing error");

}

if

(anError->Context == DataGridViewDataErrorContexts::LeaveControl)

{

MessageBox::Show("leave control error");

}

if

(dynamic_cast<ConstraintException^>(anError->Exception)

!= nullptr)

{

DataGridView^ view =

(DataGridView^)sender;

view->Rows[anError->RowIndex]->ErrorText

= "an error";

view->Rows[anError->RowIndex]->Cells[anError->ColumnIndex]->ErrorText

= "an error";

anError->ThrowException = false;

}

}

}; //end class cfrmCpy

}//end namespace

-Now cfrmCpy.cpp

#include "StdAfx.h"

#include "cfrmCpy.h"

#include "frmParent.h"

using namespace

MDITest7; //without this, cfrmCpy below is not the

scope name for SetUpDataSet.

using namespace

System::Data;

void cfrmCpy::SetUpCpyDataSet()

{

/*

Create 2 DataAdapters each with a select statement for

one table

The two CommandBuilders are required for the

DataAdapter.Update method calls later.

*/

pCpyDA=gcnew MySqlDataAdapter("SELECT * from tblCompany",

Form1::XConn);//Create Data Adapter

CpyCB.DataAdapter=pCpyDA;//Create

command builder

pStatusDA=gcnew

MySqlDataAdapter("Select * from tblStatus",

Form1::XConn);//Create Data Adapter

StatusCB.DataAdapter=pStatusDA;//Create

command builder

/*

Now we call the DataAdapter's FillSchema method for

each table.

That method pulls in constriants about the columns

from the database.

However, FillSchema does not set a column's

AutoIncrement property

even if the database says it is. So we have to set

that property to avoid

NonNullNotAllowed exceptions. This property must be

set before the dataset

is filled by the DataAdapter's Fill method.

Uhmm...its not quite clear what the

MissingSchemaAction is for, but that may come later.

*/

array<DataTable^>^ dta; //temp DataTable array returned by FillSchema

pCpyDA->MissingSchemaAction=System::Data::MissingSchemaAction::Add;

dta=pCpyDA->FillSchema(dataSet1,System::Data::SchemaType::Source,

"tblCompany");

dta[0]->Columns[0]->AutoIncrement=true;

int kk; //test - get the number of rows read into the dataset.

kk=pCpyDA->Fill(dataSet1, "tblCompany");//Fill the dataset from the table

pStatusDA->MissingSchemaAction=System::Data::MissingSchemaAction::AddWithKey;

dta=pStatusDA->FillSchema(dataSet1,System::Data::SchemaType::Source,"tblStatus");

dta[0]->Columns[0]->AutoIncrement=true;

kk=pStatusDA->Fill(dataSet1, "tblStatus");//Fill the dataset from the table

//Set Data relation - this doesn't

work. The Dataset reports that a column doesn't have unique values.

//Its a foreign key into a lookup

table. It never has unique values.

//DataRelation^ dr;//test

//dataSet1->Tables["tblCompany"]->Columns["tblStatus_idStatus"]->DataType=System::Type::UInt32;

//dataSet1->Tables["tblStatus"]->Columns["idStatus"]=System::UInt32;

//dr=gcnew

DataRelation("relCpyStatus",

//dataSet1->Tables["tblCompany"]->Columns["tblStatus_idStatus"],

//

dataSet1->Tables["tblStatus"]->Columns["idStatus"]);

//dataSet1->Relations->Add(dr);

bindingSource1->DataSource=dataSet1->Tables->default["tblCompany"];

//For textboxes and combobox

tbCpyName->DataBindings->Add(gcnew

Binding("Text",

bindingSource1, "CpyName",

true));

tbCpyAddrEtc->DataBindings->Add(gcnew

Binding("Text",

bindingSource1, "CpyAddrEtc",

true));

tbCpyID->DataBindings->Add(gcnew

Binding("Text",

bindingSource1, "idCompany",

true));

rtbCpyNotes->DataBindings->Add(gcnew

Binding("Text",

bindingSource1, "CpyNotes",

true));

tbCpyPriority->DataBindings->Add(gcnew

Binding("Text",

bindingSource1, "CpyPriority",

true));

cbCpyStatus->DataSource=dataSet1->Tables->default["tblStatus"];

cbCpyStatus->DataBindings->Add(gcnew

Binding("SelectedValue",

bindingSource1, "tblStatus_idStatus",

true));

cbCpyStatus->DisplayMember="StatusName";

cbCpyStatus->ValueMember="idStatus";

//Below is for the DGV with Status Combobox column

//dgvCpyList->DataSource =

bindingSource1;

dgvCpyName->DataPropertyName="CpyName";

dgvCpyPriority->DataPropertyName="CpyPriority";

dgvCpyID->DataPropertyName="idCompany";

// XXX - These next 4 lines make the DGV Combobox

column work

//dgvCpyStatus->DisplayStyle =

System::Windows::Forms::DataGridViewComboBoxDisplayStyle::Nothing;dgvCpyID->DataPropertyName="idCompany";

dgvCpyStatus->DataSource=dataSet1->Tables->default["tblStatus"];

//The next line ties the

DisplayMember to the foreign key

dgvCpyStatus->DataPropertyName ="tblStatus_idStatus";

dgvCpyStatus->DisplayMember="StatusName";

//Textual status column

dgvCpyStatus->ValueMember="idStatus";

//primary key column in tblStatus

// XXX

};//End SetUpCpyDataSet

now Form1_Shown

private: System::Void Form1_Shown(System::Object^ sender, System::EventArgs^ e) {
{ //Start of DB Connection open
XConn=gcnew MySqlConnection("server=shoe;user id=louarnold;password=;database=CRMX4");
XConn->Close();//This takes care of a past problem of closing the database.
try {
XConn->Open();
//MessageBox::Show("DB Open Successful!" );
}
catch (MySqlException^ myerror)
{
MessageBox::Show(myerror->Message,"Database Open Failed! Application will exit." );
Application::Exit();
}
} //End of DB Connection open
//MessageBox::Show("Form1 Shown" );
};//end Form1 Shown event handler

LouArnold at 2007-8-31 >

Could you please point out where the syntax errors are in the code i have posted - I have run it through the debugger and it doesn't seem to complain about anything. What do you mean "looking inside the dataset so you can see landmarks"? The steps i have included are all the steps. The only stuff missing the designer relevant code - creating the controls, positioning them etc...

If you do stumble upon the relevant article it would be helpful. I have read many walkthroughs and probably also the relevant one but just haven't realised it would do what i want since none of them explicitely state that they synchronize DataGridViews with simple bound control - By default, as i originally stated, they don't.

mat106 at 2007-8-31 >