none
Refresh datagridview c# RRS feed

  • Question

  • Hello guys

    I have one problem with my data grid view in c#. When I adding data to the database via the c# interface, sometimes datagridview won't refresh after adding new data. I don't why that happens sometimes, this is my code:

    Adding data:


                 
           using (OleDbConnection CNN = new OleDbConnection(strConn))
                        {
                            cnn.Open();
                            using (OleDbCommand komanda = new OleDbCommand(strConn, cnn))
                            {
                                komanda.CommandText = "insert into Table(year,month,day,money, name) values('" +
     dg.Text + "','" + comboBox1.Text + "','" + dd.Text + "','" + tbc.Text + "','" + tbo.Text + "')";
                                komanda.ExecuteNonQuery();
                                MessageBox.Show("Bravo");
                retrieve("SELECT * FROM Table where  month='" + comboBox1.Text.ToString() + "' and year='" + dg.Text.ToString() + "'  ");
                            }
                        }

           



    Method for display datagridview:

     private void show()
            {
                dataGridView1.ColumnCount = 6;
                this.dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[4].Name = "Name";
                dataGridView1.Columns[5].Name = "Money";
                dataGridView1.Columns[1].Name = "Day";
                dataGridView1.Columns[2].Name = "Month";
                dataGridView1.Columns[3].Name = "Year";
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                //SELECTION MODE
                dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                dataGridView1.ClearSelection();
                dataGridView1.MultiSelect = false;
    }

                 


    Method taking data from database and put them into datagridview

       using (OleDbConnection cnn = new OleDbConnection(strConn))
                    {
                        cnn.Open();
                        String sql = x;
                        using (OleDbCommand komanda = new OleDbCommand(sql, cnn))
                        {
                            OleDbDataAdapter adapter = new OleDbDataAdapter(komanda);
                            adapter.Fill(dt);
                            foreach (DataRow row in dt.Rows)
                            {
                                populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(),
     row[5].ToString());
                            }
                            dt.Rows.Clear();
                        }
                    }


    Method for putting data into datagridview

            private void populate(string o, string a, string b, string c, string d, string e)       

    {            dataGridView1.Rows.Add(o, a, b, c, d, e);

            }




    ES





    • Edited by dokirt.2 Sunday, January 21, 2018 4:20 PM
    Sunday, January 21, 2018 10:57 AM

Answers

  • Hello dokirt,

    There is a simple demo based on your description. If you want to refresh the datagridview when you insert data . you need to populate the data from database again.

        public partial class Form10 : Form
        {
            public Form10()
            {
                InitializeComponent();
                DataTable dt= SelectRecode("Select * from tblProducten");
                dataGridView1.DataSource = dt;
            }
         
    
           string strConn= @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxxxx;Persist Security Info=False;";
            private void button1_Click(object sender, EventArgs e)
            {
                string sql = "insert into tblProducten(Merk,Soort) values('" + textBox1.Text + "','" + textBox2.Text + "')";
                Boolean suc = AddRecode(sql);
                if (suc) {
                    DataTable dt = SelectRecode("Select * from tblProducten");
                    dataGridView1.DataSource = dt;
                }
            }
            private Boolean AddRecode(string sql) {
                try
                {
                    using (OleDbConnection cnn = new OleDbConnection(strConn))
                    {
                        cnn.Open();
                        using (OleDbCommand komanda = new OleDbCommand(strConn, cnn))
                        {
                            komanda.CommandText = sql;
                            komanda.ExecuteNonQuery();
                            return true;
                        }
                    }
                }
                catch { return false; }
            }
            private DataTable SelectRecode(string sql)
            {
                DataTable dt = new DataTable();
                using (OleDbConnection cnn = new OleDbConnection(strConn))
                {
                    cnn.Open();
                    using (OleDbCommand komanda = new OleDbCommand(sql, cnn))
                    {
                        OleDbDataAdapter adapter = new OleDbDataAdapter(komanda);
                        adapter.Fill(dt);
                    }
                }
                return dt;
            }
        }

    The result.

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by dokirt.2 Monday, January 22, 2018 3:37 PM
    Monday, January 22, 2018 10:03 AM
    Moderator

All replies

  • Hello,

    At first glance of your code (which when posting code should be in a code block with proper indenting so it's easy to read)

    Next up, why create the DataGridView in code when its so easy to do this in the IDE?

    Why don't you use parameters for your SQL statements, there is zero reasons for using string concatenation. It's prone to errors without parameters.

    When "taking data from database and put them into datagridview", why are you looping the DataTable rows when you can set its DataSource to the DataTable?

    In regards to adding a new record, after you have confirmed the database added a row you would then add the row to the DataGridView, no refreshing required.

    Takeaways

    • Never use string concatenation for SQL statements other than breaking to a new line for easy of reading.
    • Never write code where you have to refresh data in a DataGridView unless there are other users that may be adding or changing data.

    If you want an example I can provide a super simple one so you get the idea yet it's best if you try first as I'm sure my coding style is different from yours.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 21, 2018 12:43 PM
    Moderator
  • I would be very grateful if you would give an example for all of the above.
    • Edited by dokirt.2 Sunday, January 21, 2018 2:58 PM
    Sunday, January 21, 2018 2:58 PM
  • The following code sample done in VS2017 is based off my code sample on MSDN

    Adding new records into Microsoft Access tables and display in a DataGridView

    Which means I didn't sit down today and write all the code.

    Special note: Each time you run the solution the database in the app folder is overwritten so you can try it again and again. To change this behavior 

    Change from "Copy always" to "Copy if newer" in the database properties in solution explorer.

    Code break down

    After adding a new record via Add one button

    Pressing "Add 3" will insert three records at once for show multiples.

    Form code

    using DataAccess;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            /// <summary>
            /// Access to database operations
            /// </summary>
            /// <remarks></remarks>
            private Operations ops = new Operations();
    
            /// <summary>
            /// Used for the data source for the DataGridView
            /// </summary>
            /// <remarks></remarks>
            private BindingSource bsCustmers = new BindingSource();
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                bsCustmers.DataSource = ops.LoadCustomers();
                bsCustmers.Sort = "CompanyName";
                customerGridView.DataSource = bsCustmers;
                customerGridView.Columns["Identifier"].Width = 52;
                cboTitles.DataSource = ops.LoadContactTypes();
            }
            private void cmdAddSingleRow_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrEmpty(txtCompany.Text) && !string.IsNullOrEmpty(txtContact.Text))
                {
                    var cust = new Customer()
                    {
                        CompanyName = txtCompany.Text,
                        ContactName = txtContact.Text,
                        ContactTitle = cboTitles.Text
                    };
    
                    int NewIdentifier = 0;
    
                    DataTable dt = (DataTable)bsCustmers.DataSource;
    
                    if (ops.AddNewRow(cust, ref NewIdentifier))
                    {
                        dt.Rows.Add(new object[] 
                        {
                            NewIdentifier,
                            cust.CompanyName,
                            cust.ContactName,
                            cboTitles.Text
                        });
    
                        bsCustmers.Position = bsCustmers.Find("Identifier", NewIdentifier);
                    }
    
                }
            }
            private void cmdAddRows_Click(object sender, EventArgs e)
            {
                bool SeconaryTry = false;
    
                List<Customer> Customers = new List<Customer>() {
    			new Customer {CompanyName = "BDF Inc.", ContactName = "Anne", ContactTitle = "Owner"},
    			new Customer {CompanyName = "Bill's shoes", ContactName = "Bill", ContactTitle = "Owner"},
    			new Customer {CompanyName = "Salem Fishing Corp", ContactName = "Debbie", ContactTitle = "Sales"}};
    
                int NewIdentifier = 0;
                DataTable dt = (DataTable)bsCustmers.DataSource;
                foreach (var Customer in Customers)
                {
                    //
                    // See if the row already exists
                    //
                    if (bsCustmers.Find("CompanyName", Customer.CompanyName) == -1)
                    {
                        if (ops.AddNewRow(Customer, ref NewIdentifier))
                        {
                            dt.Rows.Add(new object[] 
                            {
                                NewIdentifier,
                                Customer.CompanyName,
                                Customer.ContactName,
                                Customer.ContactTitle
                            });
                        }
                    }
                    else
                    {
                        SeconaryTry = true;
                        break;
                    }
                }
    
                if (SeconaryTry)
                {
                    MessageBox.Show("This was designed to work once :-)");
                }
            }
    
            private void cmdView_Click(object sender, EventArgs e)
            {
                ops.ViewDatabase();
            }
        }
    }

    Data class

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Diagnostics;
    using System.IO;
    
    namespace DataAccess
    {
        public class Operations
        {
    
            /// <summary>
            /// Creates our connection string to the database which is easy to follow
            /// and there is no string concatenation done here
            /// </summary>
            /// <remarks></remarks>
            private OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0",
                DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
            };
    
            /// <summary>
            /// Used to get all customers at program startup
            /// </summary>
            /// <remarks></remarks>
            private string SelectStatement = "SELECT Identifier, CompanyName, ContactName, ContactTitle " + 
                                             "FROM Customer ORDER BY CompanyName;";
    
            /// <summary>
            /// Responsible for inserting rows into the customer table
            /// </summary>
            /// <remarks></remarks>
            private string InsertStatement = "INSERT INTO Customer (CompanyName, ContactName, ContactTitle) " + 
                                             "Values (@CompanyName, @ContactName, @ContactTitle)";
    
            /// <summary>
            /// Used to open the database in Windows Explorer
            /// </summary>
            /// <remarks></remarks>
            public void ViewDatabase()
            {
                Process.Start(Builder.DataSource);
            }
            public List<string> LoadContactTypes()
            {
                var titles = new List<string>();
    
                using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                {
                    using (OleDbCommand cmd = new OleDbCommand { Connection = cn, CommandText = "SELECT Title FROM Titles" })
                    {
                        cn.Open();
    
                        OleDbDataReader reader = cmd.ExecuteReader();
    
                        while (reader.Read())
                        {
                            titles.Add(reader.GetString(0));
                        }
                    }
                }
    
                return titles;
            }
            public DataTable LoadCustomers()
            {
                using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                {
                    using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
                    {
                        cmd.CommandText = SelectStatement;
    
                        DataTable dt = new DataTable { TableName = "Customer" };
    
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
    
                        return dt;
    
                    }
                }
            }
            /// <summary>
            /// Works via a class instance, for an alternate see the method below this one
            /// </summary>
            /// <param name="pCustomer"></param>
            /// <param name="pIdentfier"></param>
            /// <returns></returns>
            public bool AddNewRow(Customer pCustomer, ref int pIdentfier)
            {
                bool Success = true;
    
                try
                {
                    using (OleDbConnection cn = new OleDbConnection { ConnectionString = Builder.ConnectionString })
                    {
                        using (OleDbCommand cmd = new OleDbCommand { Connection = cn })
                        {
    
                            cmd.CommandText = InsertStatement;
    
                            cmd.Parameters.AddWithValue("@CompanyName", pCustomer.CompanyName);
                            cmd.Parameters.AddWithValue("@ContactName", pCustomer.ContactName);
                            cmd.Parameters.AddWithValue("@ContactTitle", pCustomer.ContactTitle);
    
                            cn.Open();
    
                            cmd.ExecuteNonQuery();
    
                            cmd.CommandText = "Select @@Identity";
                            pIdentfier = Convert.ToInt32(cmd.ExecuteScalar());
    
                        }
                    }
    
                }
                catch (Exception ex)
                {
                    Success = false;
                }
    
                return Success;
    
            }
            /// <summary>
            /// Alternate to the above which takes a class instance rather then individual parameters
            /// </summary>
            /// <param name="pCompanyName"></param>
            /// <param name="pContactName"></param>
            /// <param name="pContactTitle"></param>
            /// <param name="pIdentfier"></param>
            /// <returns></returns>
            public bool AddNewRow(string pCompanyName, string pContactName, string pContactTitle, ref int pIdentfier)
            {
                bool Success = true;
    
                try
                {
                    using (OleDbConnection cn = new OleDbConnection(Builder.ConnectionString))
                    {
                        using (OleDbCommand cmd = new OleDbCommand("", cn))
                        {
    
                            cmd.CommandText = InsertStatement;
    
                            cmd.Parameters.AddWithValue("@CompanyName", pCompanyName);
                            cmd.Parameters.AddWithValue("@ContactName", pContactName);
                            cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle);
    
                            cn.Open();
    
                            cmd.ExecuteNonQuery();
    
                            cmd.CommandText = "Select @@Identity";
                            pIdentfier = Convert.ToInt32(cmd.ExecuteScalar());
    
                        }
                    }
    
                }
                catch (Exception ex)
                {
                    Success = false;
                }
    
                return Success;
    
            }
        }
    }
    

    Customer class

    namespace DataAccess
    {
        public class Customer
        {
            public string CompanyName { get; set; }
            public string ContactName { get; set; }
            public string ContactTitle { get; set; }
        }
    }
    

    Ending notes, for edit, removals you would follow a similar pattern.

    For a edit cast bsCustomers.Current to a DataRow to get to the current row record. For removal do the same cast and get the primary key, send it off to a delete method in the data class, if the delete works then use bsCustomers.RemoveCurrent.

    Get current row id

    var currentId = ((DataRowView)bsCustmers.Current).Row.Field<int>("Identifier");

    Get company name

    var companyName = ((DataRowView)bsCustmers.Current).Row.Field<string>("CompanyName");
    So there you have a better than decent start to working with database data and a windows form. We could get even better but let's keep it simple for learning.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 21, 2018 6:07 PM
    Moderator
  • You were right, we have a different way of encoding, but thank you very much for your time and effort, I will explore this way of coding too...

    ES

    Monday, January 22, 2018 7:14 AM
  • Hello dokirt,

    There is a simple demo based on your description. If you want to refresh the datagridview when you insert data . you need to populate the data from database again.

        public partial class Form10 : Form
        {
            public Form10()
            {
                InitializeComponent();
                DataTable dt= SelectRecode("Select * from tblProducten");
                dataGridView1.DataSource = dt;
            }
         
    
           string strConn= @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxxxx;Persist Security Info=False;";
            private void button1_Click(object sender, EventArgs e)
            {
                string sql = "insert into tblProducten(Merk,Soort) values('" + textBox1.Text + "','" + textBox2.Text + "')";
                Boolean suc = AddRecode(sql);
                if (suc) {
                    DataTable dt = SelectRecode("Select * from tblProducten");
                    dataGridView1.DataSource = dt;
                }
            }
            private Boolean AddRecode(string sql) {
                try
                {
                    using (OleDbConnection cnn = new OleDbConnection(strConn))
                    {
                        cnn.Open();
                        using (OleDbCommand komanda = new OleDbCommand(strConn, cnn))
                        {
                            komanda.CommandText = sql;
                            komanda.ExecuteNonQuery();
                            return true;
                        }
                    }
                }
                catch { return false; }
            }
            private DataTable SelectRecode(string sql)
            {
                DataTable dt = new DataTable();
                using (OleDbConnection cnn = new OleDbConnection(strConn))
                {
                    cnn.Open();
                    using (OleDbCommand komanda = new OleDbCommand(sql, cnn))
                    {
                        OleDbDataAdapter adapter = new OleDbDataAdapter(komanda);
                        adapter.Fill(dt);
                    }
                }
                return dt;
            }
        }

    The result.

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by dokirt.2 Monday, January 22, 2018 3:37 PM
    Monday, January 22, 2018 10:03 AM
    Moderator