Datagridview - Trigger Immediate Update to Database

Hello,

I have a dataGridView which I have being populated by a connection to MSSQL. The code is extremely simple:
GlobalClass.adap = new SqlDataAdapter(query, con);
bui = new SqlCommandBuilder(GlobalClass.adap);
GlobalClass.dt = new DataTable();
GlobalClass.adap.Fill(GlobalClass.dt);
dataGridView1.DataSource = GlobalClass.dt;
dataGridView1.ReadOnly = false;

Open in new window


In the code below it I set certain columns to readOnly values and others to allow updating. You can edit a cell and the changes will take. However, the update to the database seems to lag. One either has to exit the application or leave the row and come back to it before the update gets to the database.

I've tried everything under the son to get it to commit earlier. For some reason it's not getting there unless one of the two events above takes place.

Any suggestions?
street9009Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You can add an event to the controls in the grid.

The following code came from here:
stackoverflow.com/questions/5430156/attaching-events-to-an-textbox-underlying-for-a-datagridview-cell

void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
    var txtBox = e.Control as TextBox;
    if (txtBox != null)
    {
        // Remove an existing event-handler, if present, to avoid 
        // adding multiple handlers when the editing control is reused.
        txtBox.KeyDown -= new KeyEventHandler(underlyingTextBox_KeyDown);

        // Add the event handler. 
        txtBox.KeyDown += new KeyEventHandler(underlyingTextBox_KeyDown);
    }
}

void underlyingTextBox_KeyDown(object sender, KeyEventArgs e)
{
    // ...
}

Open in new window


You don't necessarily need keydown, but you can do TextChanged for a textbox, selectedindex changed for a drop down, and then do "mini writes" to the database.
0
street9009Author Commented:
There's no textbox though. I'm doing updates directly to/from the grid.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
If you're using windows forms you can use this:
http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.commitedit.aspx

in the CellValueChanged you can update the DB by determining the name of the column and getting the cell value.
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

street9009Author Commented:
I'm not having trouble getting the database to edit. Somehow, that is being taken care of automatically by defining the primary key in the dataGridView. The updates do eventually get back to the database, but don't right away. In order to get them to go through, you have to either exit the program or leave the row and come back to it (which isn't always possible because sometimes the filter only displays one row). I need it to trigger an update to the database immediately.

I have tried code like this but it actually makes matters worse (the updates never make it to the DB when this code is uncommented):

 void dt_rowChanging(object sender, DataRowChangeEventArgs e)
        {
            /*MessageBox.Show("Action=" + e.Action.ToString());
            if (e.Action.ToString() == "Change")
            {
                GlobalClass.dt.AcceptChanges();
                GlobalClass.adap.Update(GlobalClass.dt);
            }*/
        }

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
are you using asp.Net or windows forms?
0
street9009Author Commented:
Windows Forms
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Try this from here: http://www.devexpress.com/Support/Center/Question/Details/Q489016:
private void gridView1_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
{
    ColumnView view = gridControl1.FocusedView as ColumnView;
    if(view.UpdateCurrentRow())
         GlobalClass.adap.Update(myDataSet, "MyTable");
}

Open in new window

0
mlmccCommented:
Why do you feel the need to update the database until all edits on the record are done?

If you need to make more than 1 edit to the record why tie up the network and database making each update individually

The grid doesn't know you are done with the EDIT mode until you "leave" the record.

I would avoid using the KeyPress event since each keypress will cause an update to the database.  Similarly for the ChangeValue.  While you and I see the change as being when I finish entering the value, the grid event will fire for each key you enter so changing a value to 1234 will cause 4 updates to the database (more if you make errors entering the value).  Just think how many updates a text field might cause.

Do the cells have a LostFocus event?  You could use that to update the value through code as you tab or click out of that cell into another cell

DO you have room on the form for and update button?

mlmcc
0
street9009Author Commented:
mlmcc: I do have room for an update button I was just hoping to be able to avoid it.

The odd thing is the edit doesn't happen until you leave and come back. If you can't leave the row, the edit doesn't happen until the application exits. I would like it to happen when you leave the cell you're in. I can't get that to work.

There is a dataGridView.cellLeave event.
0
mlmccCommented:
Try using that event to do an update.  You may want to check if the value has changed rather than updating the record with no changes.

As I recall how it works, the grid doesn't "know" to do the update unless you move to another record or close the form.  If you need to do it at other times it has to be done in code.  This is because you may have several edits to do or if you can add records in the grid there may be several columns which are required before a record can be added, so just leaving one cell would result in an error until all required columns had appropriate values.

mlmcc
0
street9009Author Commented:
Gotcha. Okay I'll try that. One last question- what object should I be using to call the update? There are several (dataGridView, SqlDataAdapter, DataTable, etc.).
0
mlmccCommented:
I don't know.  I am not a .Net programmer but as I recall from VB6 we used the recordset that controlled the grid.  

mlmcc
0
street9009Author Commented:
Tried this and it isn't working:

void dataGridView1_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            //GlobalClass.dt.AcceptChanges();
            GlobalClass.adap.Update(GlobalClass.dt);
        }

Open in new window


If I uncomment the dt.AcceptChanges() line, it gets into an infinite loop (stack overflow). I apparently don't have access to the dataGridView in this function (oddly enough).
0
Bob LearnedCommented:
If I understand your problem correctly, when you update the DataGridView rebinds, which calls CellLeave again, so I could possibly see a stack overflow.

You might want to define a module-level flag variable "_isUpdating", and exit the method when the flag is true, and reset to false after the update.
0
street9009Author Commented:
That makes sense. I do have one question on that though- how would it get set back to true? Like I know to put _isUpdating = false; in the dataGridView1_CellLeave() function, and to check to make sure it's true before doing the accept and update, but when does it get set to true?

I tried putting _isUpdating=true; in the rowChanged event, but that led to the same stack overflow.
0
Bob LearnedCommented:
I forgot to mention that if you use AcceptChanges before an Update, then nothing happens, because it resets all the rows to Unmodified state, which is why you weren't getting a stack overflow exception.

I must have missed something when I read the comments, do you have an Update call in the RowChanged event also?
0
street9009Author Commented:
I do. I thought that's where I would place the _isUpdating = true; I had actually commented it out (it was one of the failed methods I tried before posting this question). Basically I have this:

GlobalClass.dt.RowChanged += new DataRowChangeEventHandler(dt_rowChanging);
dataGridView1.CellLeave += new DataGridViewCellEventHandler(dataGridView1_CellLeave);

 void dt_rowChanging(object sender, DataRowChangeEventArgs e)
        {
            /*MessageBox.Show("Action=" + e.Action.ToString());
            if (e.Action.ToString() == "Change")
            {
                GlobalClass.dt.AcceptChanges();
                GlobalClass.adap.Update(GlobalClass.dt);
            }*/
            _isUpdating = true;
        }

        void dataGridView1_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            if (_isUpdating)
            {
                GlobalClass.dt.AcceptChanges();
                GlobalClass.adap.Update(GlobalClass.dt);
                _isUpdating = false;
            }
        }

Open in new window

0
Bob LearnedCommented:
Try this:

GlobalClass.dt.RowChanged += new DataRowChangeEventHandler(dt_rowChanging);
dataGridView1.CellLeave += new DataGridViewCellEventHandler(dataGridView1_CellLeave);

private bool _isUpdating;

 void dt_rowChanging(object sender, DataRowChangeEventArgs e)
        {
            if (_isUpdating)
                return;

            if (e.Action.ToString() == "Change")
            {
                _isUpdating = true;
                GlobalClass.adap.Update(GlobalClass.dt);
            }
            _isUpdating = false;
        }

        void dataGridView1_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            if (_isUpdating)
                return;

            _isUpdating = true;
            GlobalClass.adap.Update(GlobalClass.dt);
            _isUpdating = false;
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
street9009Author Commented:
By George I think that's got it! I can type in the cell and hit enter and it goes to the DB immediately. Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.