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

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

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

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

Step 1:-

Make a table in the 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 a windows form and disable checkbox of Adding, Editing, Deleting, Column Reordering of DataGridview

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

Step 3:- 

Add a new Class for global 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 OperationForm
        /// </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 the 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 a 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:-  And then 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 records 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 to you.


You should also read this Article - Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

Last updated:7/21/2020 12:04:43 PM
AVADHESH PATEL

AVADHESH PATEL

Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

20 Comments

Horas Panjaitan
Horas Panjaitan

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();

                }

AVADHESH PATEL
AVADHESH PATEL

Hi Horas Panjaitan

provide  more information  related your question with table structure!

Horas Panjaitan
Horas Panjaitan

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

Chris Anderson
Chris Anderson

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();

Horas Panjaitan
Horas Panjaitan

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();
}

AVADHESH PATEL
AVADHESH PATEL

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.

Horas Panjaitan
Horas Panjaitan

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();
        }

John fugio
John fugio

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

AVADHESH PATEL
AVADHESH PATEL

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

AVADHESH PATEL
AVADHESH PATEL

Hi John!


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

Horas Panjaitan
Horas Panjaitan

Hi  AVADHESH PATEL Thank you so much, I was in the wrong code placement, and now the code is working.

god bless us

Horas Panjaitan
Horas Panjaitan

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.

Horas Panjaitan
Horas Panjaitan

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.

AVADHESH PATEL
AVADHESH PATEL

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

AVADHESH PATEL
AVADHESH PATEL

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.

Chishambo Nzui
Chishambo Nzui

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();


Jaya kumar
Jaya kumar

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

Leave Comment