Home > DeveloperSection > Articles > Insert, Delete, Update in DataGridView with DataTable in C#

Insert, Delete, Update in DataGridView with DataTable in C#


C# C# 
Ratings:
20 Comment(s)
 117259  View(s)
Rate this:

Insert, Delete, Update in DataGridView with DataTable in C#


Hello guys, there is very simple way to insert, delete, update data in DataGridVifew with the help of using DataTable. Steps are given below…

Step 1:-

Make table in database

create table gridview

(

id intprimarykey,

name varchar(50)NOTNULL,

age intNOTNULL,

salary floatNOTNULL,

country varchar(50)NOTNULL,

city varchar(50)NOTNULL

)

Step 2:-    

Take datagridview and button on windows form and disable checkbox of Adding, Editing, Deleting, Column Recordering   of datagridview

Insert, Delete, Update in DataGridView with DataTable in C#

Step 3:- Add a new Class for globally use of datatable and SqlAddapter ‘s objects

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace datagridview

{

    class GlobalClass

    {

        public static SqlDataAdapter adap;

        public static DataTable dt;

    }

}

 

 

Step 4:- Write code on form’s cs file

using System;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

 

 

namespace datagridview

{

    public partial class DataGridView : Form

    {

        SqlConnection con;

        SqlCommandBuilder bui;

 

        string str;

        int i;

        public DataGridView()

        {

            InitializeComponent();

        }

 

        /// <summary>

        /// gridfill method used for fill record in datagridview

        /// </summary>

        void gridfill()

        {

            string cnString = System.Configuration.ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

            con = new SqlConnection(cnString);

            GlobalClass.adap = new SqlDataAdapter("select * from gridview", con);

            bui = new SqlCommandBuilder(GlobalClass.adap);

            GlobalClass.dt = new DataTable();

            GlobalClass.adap.Fill(GlobalClass.dt);

            dataGridView1.DataSource = GlobalClass.dt;

            dataGridView1.ReadOnly = true;

        }

 

 

        private void DataGridView_Load(object sender, EventArgs e)

        {

            try

            {

                gridfill();

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }

 

        /// <summary>

        /// selected rows index and values passed to Operation Form

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)

        {

            str = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();

            i = dataGridView1.Rows[e.RowIndex].Index;

            Opereation op = new Opereation(str,i);

            op.Show();

        }

       

        private void button1_Click(object sender, EventArgs e)

        {

            str = "";

            Opereation op = new Opereation(str, i);

            op.Show();

        }

   }

}

 

Step 5:- Make second form for display, insertion, deletion and updating data into datagridview 

Insert, Delete, Update in DataGridView with DataTable in C#

Step 6:- write code on form’s cs file

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

 

namespace datagridview

{

  

    public partial class Opereation : Form

    {

        string userid;

        int rowId;

        public Opereation(string id,int i)

        {

            InitializeComponent();

            userid = id;

            rowId = i;

           

        }

 

        /// <summary>

        /// auto gererate column in datatable

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void Opereation_Load(object sender, EventArgs e)

        {

            int rowcount = GlobalClass.dt.Rows.Count;

 

            if (GlobalClass.dt.Rows.Count == 0)

            {

                int set = 100;

                txtId.Text = set.ToString();

                btnUpdate.Visible = false;

                btnInsert.Visible = true;

                btnDelete.Enabled = false;

            }

            else if(userid == "")

            {

                int maxId = Convert.ToInt16(GlobalClass.dt.Compute("MAX(id)", string.Empty));

                maxId = maxId + 1;

                txtId.Text = maxId.ToString();

                btnUpdate.Visible = false;

                btnInsert.Visible = true;

                btnDelete.Enabled = false;

            }

 

            else

            {

               try

                {

                    txtId.Text = GlobalClass.dt.Rows[rowId]["id"].ToString();

                    txtName.Text = GlobalClass.dt.Rows[rowId]["name"].ToString();

                    txtAge.Text  = GlobalClass.dt.Rows[rowId]["age"].ToString();

                    txtSalary.Text = GlobalClass.dt.Rows[rowId]["salary"].ToString();

                    txtCountry.Text = GlobalClass.dt.Rows[rowId]["country"].ToString();

                    txtCity.Text = GlobalClass.dt.Rows[rowId]["city"].ToString();

                }

                catch (Exception ex)

                {

                    MessageBox.Show(ex.ToString());

                }

            }

        }

 

        private void btnClose_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

        /// <summary>

        /// delete record from datatable

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnDelete_Click(object sender, EventArgs e)

        {

            try

            {

               GlobalClass.dt.Rows[rowId].Delete();

               GlobalClass.adap.Update(GlobalClass.dt);

               this.Close();      

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }

 

        /// <summary>

        /// update record in datatable

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            try

            {

                GlobalClass.dt.Rows[rowId]["name"] = txtName.Text.Trim();

                GlobalClass.dt.Rows[rowId]["age"] = txtAge.Text.Trim();

                GlobalClass.dt.Rows[rowId]["salary"] = txtSalary.Text.Trim();

                GlobalClass.dt.Rows[rowId]["country"] = txtCountry.Text.Trim();

                GlobalClass.dt.Rows[rowId]["city"] = txtCity.Text.Trim();

                GlobalClass.adap.Update(GlobalClass.dt);

                this.Close();

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }

 

        /// <summary>

        /// Insert record in datatable

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnInsert_Click(object sender, EventArgs e)

        {       

            try

            {

                    if (txtId.Text.Trim() == "" || txtName.Text.Trim() == "" || txtAge.Text.Trim() == "" || txtSalary.Text.Trim() == "" || txtCountry.Text.Trim() == "" || txtCity.Text.Trim() == "")

                    {

                        MessageBox.Show("Please fill all the entry....");

                    }

                    else

                    {

                        DataRow dr;

                        dr = GlobalClass.dt.NewRow();

                        dr["id"] = txtId.Text.Trim();

                        dr["name"] = txtName.Text.Trim();

                        dr["salary"] = txtSalary.Text.Trim();

                        dr["age"] = txtAge.Text.Trim();

                        dr["country"] = txtCountry.Text.Trim();

                        dr["city"] = txtCity.Text.Trim();

                        GlobalClass.dt.Rows.Add(dr);

                        GlobalClass.adap.Update(GlobalClass.dt);

                        this.Close();   

                    }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }

 

        /// <summary>

        /// Name Validatoin

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void txtName_KeyPress(object sender, KeyPressEventArgs e)

        {

            e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back || e.KeyChar == (char)Keys.Space);

        }

       

        /// <summary>

        /// txtAge Validation

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

 

        private void txtAge_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)

        {

            e.Handled = !(char.IsNumber(e.KeyChar) || e.KeyChar == (char)Keys.Back);

        }

        

        /// <summary>

        /// Salary Validatoin

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void txtSalary_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)

        {

            e.Handled = !(char.IsNumber(e.KeyChar) || e.KeyChar == '.' || e.KeyChar == (char)Keys.Back);

        }

 

        /// <summary>

        /// Country Validatoin

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void txtCountry_KeyPress(object sender, KeyPressEventArgs e)

        {

            e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back || e.KeyChar == (char)Keys.Space);

        }

 

        /// <summary>

        /// city Validatoin

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void txtCity_KeyPress(object sender, KeyPressEventArgs e)

        {

            e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back || e.KeyChar == (char)Keys.Space);

        }

    }

}

 

 

 

Step 6:- Execute the program and click button ‘Add Record’ for adding new record.

Insert, Delete, Update in DataGridView with DataTable in C#

Step7:- Fill all appropriate record in text fields and then click button ‘Save’

Insert, Delete, Update in DataGridView with DataTable in C#

Insert, Delete, Update in DataGridView with DataTable in C#

Step 8:- double click on datagridview row for updating record and make some changes whatever you want for example, here you want to change name from “Avadhesh Patel” to “Avadhesh Singh” then you have to replace name from “Avadhesh Patel” to “Avadhesh Singh” in Name text field and after that click on button ‘Update’. Now updated record will visible in datagridview.

Insert, Delete, Update in DataGridView with DataTable in C#

Insert, Delete, Update in DataGridView with DataTable in C#

Step9:- Double click on datagridview row and press button ‘Delete’ to deleting data.

Insert, Delete, Update in DataGridView with DataTable in C#

Insert, Delete, Update in DataGridView with DataTable in C#

I hope this article might be helpful for you.


Help me

By Horas Panjaitan on   3 years ago
Hi AVADHESH PATEL

How do I replace the following code "1001" to "K001"

thanks

 SqlCommand cmd = new SqlCommand("Select max(kode_barang) from tblbarang", myConnection);

                    SqlDataReader dr = cmd.ExecuteReader();

                    if (dr.Read())
                    {

                        string d = dr[0].ToString();

                        if (d == "")
                        {

                            txtkode.Text = "1001"; // this may code........<<<

                        }

                        else
                        {

                            r = Convert.ToInt32(dr[0].ToString());

                            r = r + 1;

                            txtkode.Text = r.ToString();

                        }

                    }

                    myConnection.Close();


                }

                catch (Exception ex)
                {

                    MessageBox.Show(ex.ToString());

                }

                finally
                {

                    myConnection.Close();

                }

provide more details about your problem

By AVADHESH PATEL on   3 years ago
Hi Horas Panjaitan

provide  more information  related your question with table structure!

How do I replace the following code "1001" to "K001"

By Horas Panjaitan on   3 years ago
this example my table

 table  student

nim  varchar(10) noot null, Primary key            Information nim = student identification number
name varchar(50) noot null,
class  varchar (35) noot null,

 table   quiz
code_quiz varchar(10) noot null, Primary key
semester char(4) noot null,
nim varchar(10) noot null,   FK




thank you in advance

How do I replace the following code "1001" to "K001" ?

By Rohit Kesharwani on   3 years ago
Hi Horas,

you can do like this:

       public string GetEmployeeId()
        {
            object sId = null;
            try
            {
                using (SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog=db; User Id =userid;Password=password"))
                {
                    using (SqlCommand cmd = cn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "select max(Id) from Employee";
                        cn.Open();
                        sId = cmd.ExecuteScalar();
                    }
                }
            }
            catch { }

            if (string.IsNullOrEmpty(sId.ToString()))
            {
                sId = "K001";
            }
            else
            {
                int id = Convert.ToInt32(sId.ToString().Substring(1));
                id++;
                if (id >= 1 && id <= 9)
                    sId = "K00" + id;
                else if (id >= 10 && id <= 99)
                    sId = "K0" + id;
                else if (id >= 100 && id <= 999)
                    sId = "K" + id;
            }

            return sId.ToString();
}

after creating the above method, you can call and set the value whereever you want:
for eg. txtEmployeeId.Text = GetEmployeeId();

Thanks

By Horas Panjaitan on   3 years ago
Thanks for the help Rohit kesharwani and Avadhesh PATEL
god bless us all

Help This Code To Asp.Net C#

By Horas Panjaitan on   3 years ago
Hi Rohit Kesharwani
  The code I cob in asp.net why does not work, I ask you please to explain it?

   public string GetEmployeeId()
        {
            object sId = null;
            try
            {
                using (SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog=db; User Id =userid;Password=password"))
                {
                    using (SqlCommand cmd = cn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "select max(Id) from Employee";
                        cn.Open();
                        sId = cmd.ExecuteScalar();
                    }
                }
            }
            catch { }

            if (string.IsNullOrEmpty(sId.ToString()))
            {
                sId = "K001";
            }
            else
            {
                int id = Convert.ToInt32(sId.ToString().Substring(1));
                id++;
                if (id >= 1 && id <= 9)
                    sId = "K00" + id;
                else if (id >= 10 && id <= 99)
                    sId = "K0" + id;
                else if (id >= 100 && id <= 999)
                    sId = "K" + id;
            }

            return sId.ToString();
}

Help This Code To Asp.Net C#

By AVADHESH PATEL on   3 years ago
Hi Horas Panjaitan! 

 I have tried the above code in my asp.net project and it works nice. 
Could you explain me your problem in more details along with the database.

thanks for the response

By Horas Panjaitan on   3 years ago
HI..AVADHESH VATEL

This My database
 
Name database = dbbarang

Culum Name        data type     lenght
Kode_barang          ntext            16
Nama_barang         ntext            59
Harga                    numeric         9

after I click buttun get the code, a message saying = if (String.IsNullOrEmpty (sId.ToString ()))

this code

public string getcodeid()
        {
            object sId = null;
          
            try
            {
                using (SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog=db; User Id =userid;Password=password"))
                {
                    using (SqlCommand cmd = cn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "select max(Id) from Employee";
                        cn.Open();
                        sId = cmd.ExecuteScalar();
                    }
                }
            }
            catch { }

            if (string.IsNullOrEmpty(sId.ToString()))
            {
                sId = "K001";
            }
            else
            {
                int id = Convert.ToInt32(sId.ToString().Substring(1)); this source error
                id++;
                if (id >= 1 && id <= 9)
                    sId = "K00" + id;
                else if (id >= 10 && id <= 99)
                    sId = "K0" + id;
                else if (id >= 100 && id <= 999)
                    sId = "K" + id;
            }

            return sId.ToString();
        }


performed on the two form

By John fugio on   3 years ago
if I have 2 fEmployee and fCustomer, how?
I open fEmployee before Then open to fCustomer, back fEmployee is faulty.
when using the event DataGridView1_CellDoubleClick

Help This Code To Asp.Net C#

By AVADHESH PATEL on   3 years ago
Hi Horas!

Which table structured you are described here, there is no id column, and you are try to get value based on id in below line of code

cmd.CommandText = "select max(Id) from Employee";

Create id column with varchar datatype into your table

re: performed on the two form

By AVADHESH PATEL on   3 years ago
Hi John!

Could you explain more information related to your problem, because i con't understand what you want. 

Thanks to Rohit Kesharwani and AVADHESH PATEL

By Horas Panjaitan on   3 years ago
Hi  AVADHESH PATEL Thank you so much, I was in the wrong code placement, and now the code is working.

god bless us

Help Me about date of birth

By Horas Panjaitan on   3 years ago
Hi AVADHESH PATEL

want to get the age of the following dates   06/04/1984  format date indonesia:(dd/MM/yyyy)


textAge.tex =   here I

how to code / syntak in this asp.net

please help me.

Help me get age date of birth

By Horas Panjaitan on   3 years ago
Hi Rohit Kesharwani

want to get the age of the following dates   06/04/1984  format date indonesia:(dd/MM/yyyy)


textAge.tex =   here I

how to code / syntak in this asp.net

please help me.

Re: Help me get age date of birth

By AVADHESH PATEL on   3 years ago
Hi Horas!

try this line of code

// Get current date time

DateTime d = DateTime.Now;

// Changed MM/dd/yyyy format to dd/MM/yyyy fromat

string s = d.ToString("dd/MM/yyyy");

// Convert date time in string (s) to DateTime (Todate) data type

DateTime Todate = DateTime.Parse(s, CultureInfo.CreateSpecificCulture("en-IA"));

// Convert entered date string (TextBox1.Text) to DateTime (givenDate) Data Type

DateTime givenDate = DateTime.Parse(TextBox1.Text);

// Count total days

double days = Todate.Subtract(givenDate).Days;

// Convert days to year and display into Label.Text

txtAge.Text = Math.Floor(days / 365.24219).ToString();

get more help from below link


I have an run time error with this line, after I clicked Add Record button

By Virak Sim on   3 years ago


Please help me out of this trouble.
Thank!

re: I have an run time error with this line, after I clicked Add Record button

By AVADHESH PATEL on   3 years ago
Hi Virak,

It may be possible you do not have initialized dt (data table) object. Re-check Connection String and Global Class. 

I hope it helpful for you. If not then give me details, in which case this error come, because I have checked it, it's working fine.

How will datagidview refresh after data update?

By Deepak Tyagi on   one year ago
How will datagidview refresh after data update?

Delete

By Chishambo Nzui on   one year ago
Replace the rowId in the square brackets of the delete button to 0 in case its not working. 

i.e GlobalClass
.dt.Rows[0].Delete();

instead of

GlobalClass.dt.Rows[rowId].Delete();



Regarding Datagridview Row and column wise totals

By Jaya kumar on   one year ago
Hi

How to Datagridview Row and column wise totals in c# windows application.

when i enter the lastrow not working calculation can you fixed them?

send your email id

Don't want to miss updates? Please click the below button!

Follow MindStick