Why does my DataGridview refuse to refresh?

B. Clay Shannon picture B. Clay Shannon · Mar 20, 2012 · Viewed 8.7k times · Source

I'm updating a row in a table. A subset of the table is shown in a DataGridView. When I update the row, the change is not reflected in the DataGridView. Even though I'm calling DataGridView.Invalidate() and DataGridView.Refresh() after committing the change, I have to shut down the app, restart, and re-run the query before the change can be seen.

The pertinent code is:

private void buttonUpdate_Click(object sender, EventArgs e)
{
    const int TICKETID_COLUMN = 0;

    String _ticketID = dataGridView1.CurrentRow.Cells[SOME_COLUMN].Value.ToString();

    UpdateRecord(_ticketID, textBoxTicketSource.Text,
                textBoxAboutSomeID.Text, textBoxCategoryID.Text, textBoxContactEmail.Text);
}

private void UpdateRecord(string ATicketID, string ATicketSource, string 
    AAboutSomeID, string ACategoryID, string AContactID)
{
    oracleConnection1.Open();
    OracleCommand ocmd = new OracleCommand();
    OracleTransaction ot;
    // Start a local transaction 
    ot = oracleConnection1.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
    // Assign transaction object for a pending local transaction 
    ocmd.Transaction = ot;
    ocmd.Connection = oracleConnection1;
    try
    {
        ocmd.CommandText = @"UPDATE ABC.CONCERTTICKETS 
                                     SET TICKETSOURCE = :p_TICKETSOURCE, 
                                     ABOUTSOMEID = :p_ABOUTSOMEID, 
                                     CATEGORYID = :p_CATEGORYID, 
                                     CONTACTEMAIL = :p_CONTACTEMAIL 
                                     WHERE TICKETID = :p_TICKETID";
        ocmd.Parameters.Add("p_TICKETSOURCE", ATicketSource);
        ocmd.Parameters.Add("p_ABOUTSOMEID", Convert.ToInt32(AAboutSOMEID));
        ocmd.Parameters.Add("p_CATEGORYID", Convert.ToInt32(ACategoryID));
        ocmd.Parameters.Add("p_CONTACTEMAIL", AContactID);
        ocmd.Parameters.Add("p_TICKETID", ATicketID);
        ocmd.ExecuteNonQuery();
        ot.Commit();

        Popul8TheGrid();

        dataGridView1.Invalidate();
        dataGridView1.Refresh();
    }
    catch (Exception e)
    {
        ot.Rollback();
        throw;
    }
    finally
    {
        oracleConnection1.Close();
    }
}

private void Popul8TheGrid()
{
    int iFromYear = dateTimePickerFrom.Value.Year;
    int iFromMonth = dateTimePickerFrom.Value.Month;
    int iFromDay = dateTimePickerFrom.Value.Day;
    int iToYear = dateTimePickerTo.Value.Year;
    int iToMonth = dateTimePickerTo.Value.Month;
    int iToDay = dateTimePickerTo.Value.Day;

    oracleCommand1.Parameters.Clear();
    oracleCommand1.Parameters.Add("iStartDate", new DateTime(iFromYear, iFromMonth, 
        iFromDay));
    oracleCommand1.Parameters.Add("iEndDate", new DateTime(iToYear, iToMonth, 
        iToDay));
    oracleCommand1.Parameters.Add("iCATEGORYID", 114);
    // OracleRef is apparently like OracleDbType.RefCursor;
    OracleRef or = new OracleRef("_or");
    oracleCommand1.Parameters.Add("cref", or);

    oracleConnection1.Open();

    oracleDataAdapter1.SelectCommand = oracleCommand1;
    oracleDataAdapter1.GetFillParameters();
    oracleDataAdapter1.Fill(oracleDataTable1);
    dataGridView1.DataSource = oracleDataTable1;

    oracleConnection1.Close();
}

Updated:

Based on Hall's suggestion (I tried to respond with a Comment, but it seems to be hung):

OK, I've got this now:

        oracleDataAdapter1.SelectCommand = oracleCommand1;
        oracleDataAdapter1.GetFillParameters();
        oracleDataAdapter1.Fill(oracleDataTable1);
        // I don't see a "Clear" method or some such...
        dataGridView1.DataSource = null;
        //dataGridView1.DataSource = oracleDataTable1;

        BindingSource b = new BindingSource(); 
        b.DataSource = oracleDataTable1; 
        dataGridView1.DataSource = b;
        b.ResetBindings(false);

        oracleConnection1.Close();

...and it still works the same - updates, but the DataGridView doesn't know it until I restart the app.

Answer

David Hall picture David Hall · Mar 20, 2012

The reason Invalidate() and Refresh() don't requery the data source is that they are intended to only work with the graphical side of things - they both invalidate the client area of the control and force a repaint but the problem is that the underlying control thinks that nothing has changed in its data source since it relies upon the data source to tell it when that happens.

What you need is your DataSource to be something which will tell the DataGridView what is going on such as the BindingList<T> or a BindingSource, both of which have the ListChanged event which the DataGridView subscribes to.

I had thought the DataTable also informed the grid when it changed but I was either mistaken or the OracleDataTable is different.

What should fix the problem is introducing a BindingSource and making this the data source for the DataGridView. Then make your OracleDataTable the data source of the binding source. If this doesn't work you can then call the ResetBindings() method on the binding source.

BindingSource b = new BindingSource();
b.DataSource = oracleDataTable1;
dataGridView1.DataSource = b;