Updating datagrid from SQL server - update process not working

Question

I have a SQL server and I want to show it in the main form of my app. I followed some guides and I succeeded in my attempt to link between the dataGrid and the SQL server table.

The problem is that when I want to update the datagrid and sync/reload the table from the SQL server again.

I have an event handler that calls a updateDatadrid() function each time I'm adding/editing rows in the database.

When I first start the app I'm calling this function and it's working, but after that if I'm calling it again with that handler I get some errors.

Cross-thread operation not valid: Control '' accessed from a thread other than the thread it was created on.

Outside the function (global variables):

SqlConnection sConDataGrid; 
DataSet dbDataSet;
SqlDataAdapter da;
BindingSource dbBind;
string sqlCommand;

EDIT - I forgot to add :
DataGridView _dbView;

This is my code:

private void updateDataGrid()
{
        using (sConDataGrid = new SqlConnection("Data Source=" + SettingsForm.getAddress + ";Initial Catalog=" + SettingsForm.getDatabase + ";Integrated Security=False;User Id=" + SettingsForm.getUser + ";Password=" + SettingsForm.getPassword + ";Connect Timeout=0;"))
        {
            sConDataGrid.Open();
            sqlCommand = "select top 200 * FROM cstPackages order by _dateTime desc"; //reading the db from end to start   
            using (da = new SqlDataAdapter(sqlCommand, sConDataGrid))
            {
                da.Fill(dbDataSet, "cstPackages");
                    dbBind = new BindingSource(dbDataSet, "cstPackages");
                    _dbView.DataSource = dbBind;
                sConDataGrid.Close();
            }
        }
}

I tried to play with it, call only to the da.Fill() function from after the first time i called this function, but it didn't refresh the datagrid. I tried to clear the dataset before I filled it again, but it made the program to crash. I tried just to do _dbView.Refresh() but it didn't work as well..

EDIT I don't understand, why if I make a button, and I put "click" event handler, and i'm calling my function from this event handler - it's working! And if i'm calling to this function from the SerialPort dataReceived event handler - I get this error! what is the difference? I thought maybe I created the event handler not in the proper way, so I just dragged a SerialPort control form the designer and double clicked on the event, the same happened.

I tried to make a timer, put it on enabled and at the end of the function call it to stop(), and in the serial received set it to start() but everything I do in the SerialPort handler is like "outside" the program. It doesn't start the timer.

I'm assuming _dbView is your DataGrid. Is that right? Have you tried _dbView.DataBind() after the line where you set the DataSource? — Melanie
@Melanie - Yes this is my DatGrid, and I didn't try that before, but I checked not and there is no such a function :/ — Itay
Cross-thread operation not valid: Control '' accessed from a thread other than the thread it was created on. What control and where this error occurs? Looks like you're trying to access something from different thread than that something was created. Probably dataGridView. The solution could be to access it via Invoke, but cannot tell you more unless you give more details about the error — a''
When this error occurs this is the error I get, with ' '. The debuger points on the code _dbView.DataSource = dbBind; code line. I have a "received" event handler that attached to a COM port, and when I get data to the COM port I'm adding/changing the database according to the string, and then I call this function again. — Itay
I think the problem is you created your DataGrid on your main thread. But the event handling thread, that runs in reaction to a COM port event, is trying to modify the GUI. Perhaps the event handling thread can send a message to the main thread to refresh the DataGrid. — Steve Wellens

Itay Itay · Accepted Answer · 2012-12-27T19:50:11

SOLVED After reading this : http://msdn.microsoft.com/en-us/library/ms171728%28VS.80%29.aspx I tried using Invokes, and it worked!

new code:
        delegate void SetDataGridCallback();

        private void updateDataGrid()
        {
            if (this._dbView.InvokeRequired)
            {
                SetDataGridCallback d = new SetDataGridCallback(updateDataGrid);
                this.Invoke(d, new object[] {  });
            }
            else
            {
                using (sCon2 = new SqlConnection("Data Source=" + SettingsForm.getAddress + ";Initial Catalog=" + SettingsForm.getDatabase + ";Integrated Security=False;User Id=" + SettingsForm.getUser + ";Password=" + SettingsForm.getPassword + ";Connect Timeout=0;"))
                {
                    sCon2.Open();
                    string sqlCommand = "select top 200 * FROM cstPackages order by _dateTime desc"; //reading the db from end to start   
                    using (da = new SqlDataAdapter(sqlCommand, sCon2))
                    {
                        dbDataSet.Clear();
                        da.Fill(dbDataSet, "cstPackages");
                        BindingSource dbBind = new BindingSource(dbDataSet, "cstPackages");
                        _dbView.DataSource = dbBind;
                        _dbView.Refresh();
                         sCon2.Close();
                    }
                }
            }
        }

Updating datagrid from SQL server - update process not working

3 Answers