Date: 02/07/06 (C Sharp)    Keywords: database, sql, web

C# 2005 Express Edition.

Okay, quickest way to explain this situation: I have a DataGridView attached to a local Access database. The DGV pulls in three columns. My goal here is to get changes in the columns to update to the table automatically a la an open Access table window (or SQL Server Entierprise Manager or FoxPro or... you know). I started with the code in this CodeProject article.

The first change I've had to make to it is that it seems I have to instantiate the tableadapter before calling its Update() method. All well and good, though I'm not sure I did it correctly. Make the change, run again, and same result: I can change stuff to my heart's content in the DataGridView, but none of the changes are propagated to the database. I added the MessageBox you see in the UpdateRowToDatabase method; it never fires.

My question (again assuming I instantiated the tableadapter correctly): do I also need to instantiate the bindingsource? If so, how, because I can't find anything about it anywhere online or in the help files?

If not, can someone give me some idea of what it is I'm doing wrong? Thanks.

Problem solved, thanks to 109. Except for one oddity I'm going to go squash now...

EDIT2: bloody hell. I THOUGHT I had it...

sequence of events.
1. Fire up proggy.
2. Change a few rows, varying what I change in any given row.
3. With each change, event fires. (This is a major step forward, anyway.)
4. Close form.
5. Open form again (from the main form that calls it).
6. Look for changes. They are still there.
7. Exit program.
8. Start program again.
9. Open form.
10. Check for changes made when program was previously running... and they're not there.
(I've also gone in through Access and looked; the changes are not propagating.)

It looks as if the ThisCategoriesTableAdapter.Update(LastDataRow) command is updating the DataSet rather than the database itself. But everything I've seen on MS' website says specifically that the Update command will, in fact, update the database.

Changes to the program code integrated below.

Thanks!

public partial class BrowseCategories : Form
{
    categoriesDataSetTableAdapters.CategoriesTableAdapter ThisCategoriesTableAdapter = new categoriesDataSetTableAdapters.CategoriesTableAdapter();
    //tracks PositionChanged event for last row
    private DataRow LastDataRow = null;

    public BrowseCategories
    {
        InitializeComponent();
    }

    private void BrowseCategories_Load (object sender, EventArgs e)
    {
        this.parentsTableAdapter.Fill(this.parents._Parents);
        this.categoriesTableAdapter.Fill(this.categoriesDataSet.Categories);
        this.dgCategories.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
        //Is there a way to add an event handler from the design screen?
        categoriesBindingSource.PositionChanged += new EventHandler(categoriesBindingSource.PositionChanged);
        //Set LastDataRow initially, or if you try to change the first row, the event handler won't fire
        BindingSource thisBindingSource = (BindingSource)categoriesBindingSource;
        LastDataRow = ((DataRowView)thisBindingDource.Current)>Row;
    }

    private void UpdateRowToDatabase();
    {
        if (LastDataRow != null)
        {
            if (LastDataRow.RowState == DataRowState.Modified)
            {
                ThisCategoriesTableAdapter.Update(LastDataRow);
            }
        }
    }

    private void categoriesBindingSource_PositionChanged(object sender, EventArgs e)
    {
        BindingSource thisBindingSource = (BindingSource)sender;
        DataRow thisDataRow = ((DataRowView)thisBindingSource.Current).Row;
        if (thisDataRow == LastDataRow)
        {
            throw new ApplicationException("It seems the PositionChanged event has fired twice for the same row.";
        }
        UpdateRowToDatabase();
        LastDataRow = thisDataRow;
    }

    private void btnParent_Click(object sender, EventArgs e)
    {
        BrowseParent frmParent = new BrowseParent();
        frmParent.Show();
    }

    private void BrowseCategories_FormClosed(object sender, EventArgs e)
    {
        UpdateRowToDatabase();
    }
}
}
//I seem to have added an extra bracket... keeping it in case I screwed up the formatting somewhere.
As I retyped it all, assume any typos are unique to this rendition. The code does run (it just doesn't update the database).

EDIT:: as requested by 109 the InitializeComponent() method:

private void InitializeComponent()
{
    this.components = new System.ComponentModel.Container();
    this.dgCategories = new System.Windows.Forms.DataGridView();
    this.parentsBindingSource = new system.Windows.Forms.BindingSource(this.components);
    this.parents = new HospitalRates.Parents();
    this.categoriesBindingSource = new System.Windows.Forms.BindingSource(this.components);
    this.categoriesDataSet = new HospitalRates.categoriesDataSet();
    this.btnParent = new System.Windows.Forms.Button();
    this.btnCorrect = new System.Windows.Forms.Button();
    this.btnDone = new System.Windows.Forms.Button();
    this.btnSave = new System.Windows.Forms.Button();
    this.categoriesTableAdapter = new HospitalRates.categoriesDataTableSetAdapters.CategoriesTableAdapter();
    this.parentsTableAdapter = new HospitalRates.ParentsTableAdapters.ParentsTableAdapter();
    ///a word of explanation: the control below started out life as a text box column because
    ///I'm a forgetful bastard, and name hasn't changed because I'm lazy, as well.
    this.ParentsDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewComboBoxColumn();
    this.CategoryDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
    this.descriptionDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
    ((System.ComponentModel.ISupportInitialize)(this.dgCategories)).BeginInit();
    ((System.ComponentModel.ISupportInitialize)(this.parentsBindingSource)).BeginInit();
    ((System.ComponentModel.ISupportInitialize)(this.parents)).BeginInit();
    ((System.ComponentModel.ISupportInitialize)(this.categoriesBindingSounrce)).BeginInit();
    ((System.ComponentModel.ISupportInitialize)(this.categoriesDataSet)).BeginInit();
    this.SuspendLayout();
    //
    //dgCategories
    //
    this.dgCategories.AutoGenerateColumns = false;
    this.dgCategories.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
    ///note: I swear, there's no extra indentation here!
    this.dgCategories.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
    this.parentDataGridViewTextBoxColumn,
    this.categoryDataGridViewTextBoxColumn,
    this.descriptionDataGridViewTextBoxColumn});
    this.dgCategories.DataSource = this.categoriesBindingSource;
    this.dgCategories.Location = new System.Drawing.Point(14, 14);
    this.dgCategories.Name = "dgCategories";
    this.dgCategories.Size = new System.Drawing.Size(739, 393);
    this.dgCategories.TabIndex = 0;
    //
    //parents
    //
    this.parents.DataSetName = "Parents";
    this.parents.DataSchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
    //
    //categoriesBindingSource
    //
    this.categoriesBindingSource.DataMember = "categoriesDataSet";
    this.categoriesBindingSource.DataSource = this.categoriesDataSet;
    //
    //categoriesDataSet
    //
    this.categoriesDataSet.DataSetName = "categoriesDataSet";
    this.categoriesDataSet.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
    //
    //btnParent
    //
    this.btnParent.Location = new System.Drawing.Point(14, 432);
    this.btnParent.Name = "btnParent";
    this.btnParent.Size = new System.Drawing.Size(135, 36);
    this.btnParent.TabIndex = 1;
    this.btnParent.Text = "Add/Edit Parent";
    this.btnParent.UseVisualStyleBackColor = true;
    this.btnParent.Click += new System.EventHandler(this.btnParent.Click);
    //
    //btnCorrect
    //
    this.btnCorrect.Location = new System.Drawing.Point(215, 432);
    this.btnCorrect.Name = "btnCorrect";
    this.btnCorrect.Size = new System.Drawing.Size(135, 36);
    this.benCorrect.TabIndex = 2;
    this.btnCorrect.Text = "Correct Existing Data";
    this.btnCorrect.UseVisualStyleBackColor = true;
    //
    //btnDone
    //
    this.btnDone.Location = new System.Drawing.Point(617, 432);
    this.btnDone.Name = "btnDone";
    this.btnDone.Size = new System.Drawing.Size(1356, 36);
    this.btnDone.TabIndex = 3;
    this.btnDone.Text = "Done";
    this.btnDone.UseVisualStyleBackColor = true;
    //
    //categoriesTableAdapter
    //
    this.categoriesTableAdapter.ClearBeforeFill = true;
    //
    //parentsTableAdapter
    //
    this.parentsTableAdapter.ClearBeforeFill = true;
    //
    //parentsDataGridViewtextBoxColumn
    //
    this.parentsDataGridViewTextBoxColumn.DataPropertyName = "Parent";
    this.parentsDataGridViewTextBoxColumn.DataSource = this.parentsBindingSource'
    this.parentsDataGridViewTextBoxColumn.HeaderText = "Parent";
    this.parentsDataGridViewTextBoxColumn.Name = "parentDataGridViewTextBoxColumn";
    this.parentsDataGridViewTextBoxColumn.Resizable = System.Windows.Forms.DataGridViewTriState.True;
    this.parentsDataGridViewTextBoxColumn.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
    this.parentsDataGridViewTextBoxColumn.ValueMember = "Parent";
    //
    //categoryDataGridViewTextBoxColumn
    //
    this.categoryDataGridViewTextBoxColumn.DataPropertyName = "Category";
    this.categoryDataGridViewTextBoxColumn.HeaderText = "Category";
    this.categoryDataGridViewTextBoxColumn.Name = "categoryDataGridViewTextBoxColumn";
    //
    // descriptionDataGridViewTextBoxColumn
    //
    this.descriptionDataGridViewTextBoxColumn.DataPropertyName = "Description";
    this.descriptionDataGridViewTextBoxColumn.HeaderText = "Description";
    this.descriptionDataGridViewTextBoxColumn.Name = "descriptionDataGridViewTextBoxColumn";
    //
    //BrowseCategories
    //
    this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
    this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
    this.ClientSize = new System.Windows.Drawing.Size(772, 515);
    this.Controls.Add(this.btnSave);
    this.Controls.Add(this.btnDone);
    this.Controls.Add(this.btnCorrect);
    this.Controls.Add(this.btnParent);
    this.Controls.Add(this.dgCategories);
    this.Load += new System.EventHandler(this.BrowseCategories_Load);
    ((System.ComponentModel.ISupportInitialize)(this.dgCategories)).EndInit();
    ((System.ComponentModel.ISupportInitialize)(this.parentsBindingSource)).EndInit();
    ((System.ComponentModel.ISupportInitialize)(this.parents)).EndInit();
    ((System.ComponentModel.ISupportInitalize)(this.categoriesBindingSource)).EndInit();
    ((System.ComponentModel.ISupportInitalize)(this.categoriesDataSet)).EndInit();
    this.ResumeLayout(false);
}

Ah, the problem becomes clear... the event handler never registered...

Source: http://community.livejournal.com/csharp/49005.html