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.
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;