Force a dataGridView to Refresh

I have a datagridview that is initially populated with the results of a stored procedure. I have a comboBox where users can select a different set of data and the data in the datagridview refreshes using the correct stored procedure when the comboBox selected index is changed. This all works perfectly.

I have fields available for the users to select a row, modify what they need to modify, click on Update, and the database is updated. The goal here is to force the datagridview to update with the modified data, based upon the view they were using when they updated the data. The data is getting updated correctly, but the datagridview is not refreshing.

On page load, I set a variable, currSelected, to 0. Then anytime the comboBox is changed for a different view, I update currSelected to whichever index they selected. in the try statement of the update code, I have the different comboBox indexes explicitly tied to the correct stored procedure to display that data.

This is the code on page load:
            comboBox1.SelectedIndex = 0;

            DataTable dt = new DataTable();
            con.Open();

            SqlCommand cmd = new SqlCommand("aertDocktoday", con)
            {
                CommandType = CommandType.StoredProcedure
            };

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            con.Close();

Open in new window


This is the code in the button update click event:
            if (txtOrder.Text != "")
            {
                cmd = new SqlCommand("UPDATE AER_DOCK_APPOINTMENTS SET [ORDER_NUMBER] = @order,[DATE] = @date,[PAPERWORK] = @paperwork,[CARRIER] = @carrier,[PICK_UP_NUMBER] = @pknum,[LOAD_TYPE] = @loadtype,[WHS] = @warehouse,[SHIP_NOTES] = @shipnotes,[DESTINATION] = @dest,[NOTES] = @notes,[MODIFIED]=GETDATE(),[MODIFIED_BY]=@user WHERE ID=@id", con);
                con.Open();

                cmd.Parameters.Add("@id", SqlDbType.Int).Value=ID;
                cmd.Parameters.Add("@order", SqlDbType.VarChar).Value=txtOrder.Text;
                cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = txtLoadtime.Text;
                cmd.Parameters.Add("@paperwork", SqlDbType.Bit).Value = checkBoxPaperwork.Checked;
                cmd.Parameters.Add("@carrier", SqlDbType.VarChar).Value = txtCarrier.Text;
                cmd.Parameters.Add("@pknum", SqlDbType.VarChar).Value = txtPickupnum.Text;
                cmd.Parameters.Add("@loadtype", SqlDbType.VarChar).Value = comboBoxLoadtype.SelectedItem.ToString();
                cmd.Parameters.Add("@warehouse", SqlDbType.VarChar).Value = comboBoxWarehouse.SelectedItem.ToString();
                cmd.Parameters.Add("@shipnotes", SqlDbType.VarChar).Value = txtShipnotes.Text;
                cmd.Parameters.Add("@dest", SqlDbType.VarChar).Value = txtDestination.Text;
                cmd.Parameters.Add("@notes", SqlDbType.VarChar).Value = txtLoadnotes.Text;
                cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = Environment.UserName;

                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Record Updated Successfully.");
                    
                    if (currSelected == 0)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDocktoday", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 1)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDocktomorrow", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 2)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDocktwodays", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 3)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDockthreedays", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 4)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDockfourdays", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 5)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDockfivedays", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 6)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDockfuture", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 7)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDocklast30", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 8)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDocklast12mos", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 9)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("aertDockneedspaperwork", con)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 10)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("SELECT * FROM AER_DOCK_APPOINTMENTS WHERE DATE BETWEEN '" + dateTimePickerStart.Value + "' and '" + dateTimePickerEnd.Value + "' ORDER BY DATE", con);
                        cmd.CommandType = CommandType.Text;
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    if (currSelected == 11)
                    {
                        DataTable dt = new DataTable();
                        SqlCommand cmd = new SqlCommand("SELECT * FROM AER_DOCK_APPOINTMENTS WHERE ORDER_NUMBER='" + txtOrderSearch.Text + "'", con);
                        cmd.CommandType = CommandType.Text;
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }

                    ClearData();
                    con.Close();
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    con.Close();
                }

            }
            else
            {
                MessageBox.Show("Please Select Record to Update.");
            }

Open in new window


The update is happening correctly, but the datagridview does not get refreshed. If I change the comboBox to another view and go back, the datagridview refreshes correctly and shows the modified data. I have tried adding dataGridView1.EndEdit(); as well as dataGridView1.Refresh(); and neither of those do anything.

How can I force the datagridview to refresh the currently selected view  to include the updated information?
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Sorry about that.

Can you try adding the statement, dataGridView1.DataSource = dt; in all your if statements as shown below.
if (currSelected == 0)
{
    // Do this here before changing it below. 
    dataGridView1.DataSource = null;
    DataTable dt = new DataTable();
    SqlCommand cmd = new SqlCommand("aertDocktoday", con)
    {
        CommandType = CommandType.StoredProcedure
    };
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    dataGridView1.DataSource = dt;
}

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi Carla;

Have you tried using the dataGridView1.Refresh() method as shown below?
    ...
    dataGridView1.Refresh();
    ClearData();
    con.Close();
}

catch (Exception ex)
{
    ...

Open in new window

0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Yes, I stated in the original question that I've tried dataGridView1.Refresh() as well as dataGridView1.EndEdit() and neither of those refresh the dataGridView.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Carla RomereDirector of Information TechnologyAuthor Commented:
It is already there. It is the last line in each of the if statements.
0
 
Fernando SotoConnect With a Mentor RetiredCommented:
No the last line of the if statement is assigning a new DataTable object to the dataGridView1.DataSource what I would like to do is reset the dataGridView1.DataSource to null before doing that.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Oh I missed the setting it to null part at the first - stand by.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
No, that didn't work either. This is just bizarre.
0
 
Fernando SotoRetiredCommented:
You are updating the database first before running the above code you posted?
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
In the original code posted, it runs through line 22 because I get the message box that says the record was updated successfully.
0
 
Fernando SotoRetiredCommented:
But after you acknowledge the message box where and all the if statements run where do you end up?
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
It just stops. If I then change the comboBox to another view and back again, it shows the record changed. I'm going to put something in there AFTER all the if statements to see if they are actually running.
0
 
Fernando SotoRetiredCommented:
You state, "It just stops", so clicking on the OK button on the MessageBox does nothing?
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Oh, sorry. The message box works perfectly. Click on OK and it goes right back to the main screen. The grid just doesn't update to reflect the changes that were just made. I added code AFTER the if statements to change the button colors back to default, and they run and do what they are supposed to. It's almost like it's just skipping the if statements. So, what I need to see now is what is the value of currSelected after the update is performed. Maybe it's getting set to NULL or something - which would invalidate all the if statements.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Aha!! The currSelected variable is getting set to the ID of the row that was selected. So, after the update, can I set the currSelected value back equal to the index of the currently selected item in the comboBox?
0
 
Fernando SotoRetiredCommented:
Is it possible to post the project to a website where I can download it to see. Also if you have a test database with dummy data in it can you post that as well?
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
I got it - right after the successful message box, I set currSelected back to the comboBox1.SelectedIndex and now it's working like it's supposed to. I just need to find where currSelected is getting set to the rowID now just so I understand.
0
 
Fernando SotoConnect With a Mentor RetiredCommented:
You can try that but be aware that if someone else adds or removes a row from the database it may not be placed on the row it was on.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Found the line where I set currSelected = ID but I have no earthly idea why I did. It isn't necessary and I removed that line and it's back to working as it should now. Thank you for your help!
0
 
Fernando SotoRetiredCommented:
Not a problem Carla glad all is now working, and as always glad to help.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Thanks for getting me on the right track to get this error resolved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.