Update datagridview from SQL in the form close event

Question

I have a form with a dataGridView1 that is updated from SQL database.

What i am trying to do is: when the user presses the Close button I am checking a variable and if condition is true then I cancel the from close (e.Cancel = true;) and i want to display some data in the datagrid.

Whatever I do the grid is not updating. I am calling a "private void update()" to update the grid from SQL but after I cancel the form close event but it does not seem to work.

I have tried refreshing the form, refreshing the datagrid with no result.

After the form_Close even finishes, and the datagrid is empty, if i press a button that calls the same "private void update()" it works and the data is shown in the datagrid.

Thank you for your help.

EDIT1: to give you more details

I tried the code on FormClosing but I get no result. The code I'm using is:

    private void Form1_FormClosing(object sender, FormClosingEventArgs e)
    {
        getdata_fromSQL();//this private void gets some data from sql into the datagrid

        if (dataGridView1.RowCount > 0)//check if i have at least one row retreved
        {
            // Cancel the Closing event and tell the user
            MessageBox.Show("Please check the data before leaving.");
            e.Cancel = true;
            getdata_fromSQL();// retrieve the data again for the user to see (this part is not working
        }
    }

This is how the data is retrieved.

    private void getdata_fromSQL()
    {
      SqlConnection con = new SqlConnection("connection string"); //defining connection
      con.Open(); 
      string sql_command = "Select * from Test_Table where [Check] is null";
      SqlCommand command = new SqlCommand(sql_command, con); // defining the command
      DataSet set = new DataSet("SQL_table");
      SqlDataAdaptersda = new SqlDataAdapter(command); //defining the adapter and make it accept changes
      sda.AcceptChangesDuringFill = true; 
      sda.AcceptChangesDuringUpdate = true;
      set.Clear(); //just to make sure my adapter is empty
      cmdBuilder = new SqlCommandBuilder(sda); //creating the command builder so I can save the changes
    sda.Fill(set, "SQL_table");  // fill the dataset
    dataGridView1.DataSource = set; 
    dataGridView1.DataMember = "SQL_table"; //fill datagrid
    dataGridView1.CellValueChanged -= dataGridView1_CellValueChanged;
    dataGridView1.CellValueChanged += dataGridView1_CellValueChanged; //look for cell value changed (I am using this in other scope)
    }

After I cancel the close and try to update the datagrid again, it remains blank.

EDIT2: @Gami

your code does this:

     private void Form1_FormClosing(object sender, FormClosingEventArgs e)
    {
        if (i == 1)
        {
            MessageBox.Show("Hello");
            e.Cancel = true;
            getRefresh();
        }
    }

and your refresh is this:

     private void getRefresh()
    {
        SqlConnection con = new SqlConnection(@"user id=testuser;" +
                                   "password=testpass;Data Source=SERVER;" +
            //                           "Trusted_Connection=yes;" +
                                   "Initial Catalog=Partner_database; " +

                                   "connection timeout=30"); //defining connection
        con.Open();
        SqlCommandBuilder cmdBuilder;
        string sql_command = "Select * from Test_table where [Check] is null";
        SqlCommand command = new SqlCommand(sql_command, con); // defining the command
        DataSet set = new DataSet("SQL_table");
        SqlDataAdapter sda = new SqlDataAdapter(command); //defining the adapter and make it accept changes
        sda.AcceptChangesDuringFill = true;
        sda.AcceptChangesDuringUpdate = true;
        set.Clear(); //just to make sure my adapter is empty
        cmdBuilder = new SqlCommandBuilder(sda); //creating the command builder so I can save the changes
        sda.Fill(set,"SQL_table");  // fill the dataset
        dataGridView1.DataSource = set;
        dataGridView1.DataMember = "SQL_table"; //fill datagrid

    }

My code is the one above. We are both using the FormClosing event, we both cancel the close process, and then call the refresh.

This is the SQL table:

This is the datasource for my datagrid:

Answer 1

I have found the issue. Looks like it was a mistake on my side, I still don't know why is not working the way it is, but know the cause.

I am using code to get the logged user name from Windows, and when i filter the SQL table i filter on the user as well.

This is the code to retrieve the user:

    private string getUserDisplayName()
    {
        var username = new StringBuilder(1024);
        uint userNameSize = (uint)username.Capacity;

        // try to get display name and convert from "Last, First" to "First Last" if necessary
        if (0 != GetUserNameEx(3, username, ref userNameSize))
            return Regex.Replace(username.ToString(), @"(\S+), (\S+)", "$2 $1");

        // get SAM compatible name <server/machine>\\<username>
        if (0 != GetUserNameEx(2, username, ref userNameSize))
        {
            IntPtr bufPtr;
            try
            {
                string domain = Regex.Replace(username.ToString(), @"(.+)\\.+", @"$1");
                DirectoryContext context = new DirectoryContext(DirectoryContextType.Domain, domain);
                DomainController dc = DomainController.FindOne(context);

                if (0 == NetUserGetInfo(dc.IPAddress,
                                        Regex.Replace(username.ToString(), @".+\\(.+)", "$1"),
                                        10, out bufPtr))
                {
                    var userInfo = (USER_INFO_10)Marshal.PtrToStructure(bufPtr, typeof(USER_INFO_10));
                    return Regex.Replace(userInfo.usri10_full_name, @"(\S+), (\S+)", "$2 $1");
                }
            }
            finally
            {
                NetApiBufferFree(out bufPtr);
            }
        }

        return String.Empty;
    }

In my FormClosing block i am calling this private void and use this as filter to sql.

    user_name = getUserDisplayName();
    string sql_command = "Select * from Test_table where [Check] is null and [User Name] = '" + user_name + "'";

When removing the getUserDisplayName() call it works. When i call it it does not refresh the grid even if it runs with no error.

Does it cut my connections when i press the close button? I think this is another question and here is off topic.

Answer 2

Try to write code in Form1_FormClosing() event of form

example is

namespace canceldemo
{
    public partial class Form1 : Form
    {
        int i = 1;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (i == 1)
            {
                MessageBox.Show("Hello");
                e.Cancel = true;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            label1.Text = (i+10).ToString();
        }
    }
}