Friday, 20 April 2012

How to Insert, Edit, Update and Delete Data with DataGridView in Windows Form C#.net ||Inserting , Updating and Deleting with DataGridView in Windows forms C#.net


In this article I am showing to Inserting , Editing , Updating and Deleting options with DataGridview.
For that I am Designing form with two textboxes with Name and Location ,DataGridview to display data and four buttons to Save , Edit , Update and Delete.
To do this just follow below steps:
·         In form load I am binding the data from database.
·         In save button click event saving data to database which are inserted into the name and location textboxes.
·         In Delete button click event  Deleting the selected row data in DataGridview from database.
·         In Edit button Click event filling the selected data from Gridview into Name and location textboxes.
·         In Update Button click event updating data which are edited the name and location textboxes.
Write the following code in Form.cs :
Form.cs Code :

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;
using System.Configuration;
using System.Data.SqlClient;

namespace savedata
{
    public partial class Form1 : Form
    {

        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["Sqlcon"].ConnectionString);
        public Form1()
        {
            InitializeComponent();
            Bind();

        }

        private void Clear()
        {
            txtName.Text = string.Empty;
            txtLocation.Text = string.Empty;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("Insert Into Test_Data(Name,Location) Values (@Name,@Location)", con);
            cmd.Parameters.AddWithValue("Name", txtName.Text);
            cmd.Parameters.AddWithValue("Location", txtLocation.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Inserted sucessfully");
            Bind();
            Clear();
        }

        private void Bind()
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Test_Data", con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            con.Close();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            SqlCommand delcmd = new SqlCommand();
            if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1)
            {
                delcmd.CommandText = "DELETE FROM Test_Data WHERE ID=" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
                con.Open();
                delcmd.Connection = con;
                delcmd.ExecuteNonQuery();
                con.Close();
                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                MessageBox.Show("Row Deleted");
            }
            Bind();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("Update Test_Data set Name=@Name,Location=@Location Where(Name=@Name)", con);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Location", txtLocation.Text);
            cmd.ExecuteNonQuery();
            MessageBox.Show("updated......");
            con.Close();
            Bind();
            Clear();
        }

        private void btnEdit_Click_1(object sender, EventArgs e)
        {
            int i;
            i = dataGridView1.SelectedCells[0].RowIndex;
            txtName.Text = dataGridView1.Rows[i].Cells[1].Value.ToString();
            txtLocation.Text = dataGridView1.Rows[i].Cells[2].Value.ToString();
        }
    }
}


Then run the application you will get output like below:



21 comments:

  1. wow thanks it really helped me

    ReplyDelete
  2. wow, awesome! I've been staring at my computer thinking of a way to do that until I've stumbled to your blog. Thanks! you saved me some time! more power to your blog.

    ReplyDelete
  3. Well looks like your code works till the time the application is running. The dataset is definitely updated with the changes that I make, however no changes are made to the actual database table if I try to manually view the table data contents. If I close the application and relaunch, the original table data shows on the datagrid view, as if no changes were made to it!

    Any help on this matter will be appreciated!

    ReplyDelete
  4. Thanks for post.
    Muhammad Usama Masood
    m-usama-m.blogpsot.com

    ReplyDelete
  5. This is really helpful for me. I have small external project to develop a small software which can insert data via C#.net form and end of every month or a week, users could be able to view the data in there database via report. I'm thinking of using a crystal report for that. Now i can insert data via .net form by using your code. Tnx again for that. can you please tell me how to create a crystal report to retrieve data from the database and view them to the users. This report should run via another form. Report should generate according tot the given parameters via the form....Thanks in advance

    ReplyDelete
  6. your Post was very hopeful but when i try to delete i got error can you fix this?

    ReplyDelete
    Replies
    1. int i;
      i = dataGridView1.SelectedCells[0].RowIndex;
      SqlCommand delcmd = new SqlCommand();
      if (dataGridView1.Rows.Count > 1 && i != dataGridView1.Rows.Count - 1)
      {
      delcmd.CommandText = "DELETE FROM Test_Data WHERE ID=" + dataGridView1.SelectedRows[i].Cells[0].Value.ToString() + "";
      con.Open();
      delcmd.Connection = con;
      delcmd.ExecuteNonQuery();
      con.Close();
      dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[i].Index);
      MessageBox.Show("Row Deleted");
      }
      Bind();

      Delete
  7. Hi!

    DataGridView control are used very frequently in C#. It has various type of functionality but comman funcatin are CRUD operation. So thanks for sharing your

    kanowledge. There are few other links that have described CRUD (Insert, Delete, Update) operation with good explaination and proper sample. I hope that's helpful for

    beginners.


    http://www.mindstick.com/Articles/9422cfc8-c2ed-4ec1-9fab-589eb850a863/?Insert%20Delete%20Update%20in%20DataGridView%20with%20DataTable%20in%20C

    http://www.dreamincode.net/forums/topic/238727-insert-update-and-delete-records-in-table-with-datagridview-using-c%23/

    ReplyDelete
  8. After adding bind its give me this error kindly tell me how to add Bind() -

    "Error 1 The name 'Bind' does not exist in the current context"

    ReplyDelete
  9. Hello in the line
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Sqlcon"].ConnectionString);

    I have this error "Object reference not set to an instance of an object."
    Can you help me please???

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Also, when I've change the way of connection string in different syntax, and I've run the program when I tried edit button and then save button I had this error
    "Incorrect syntax near 'nvarchar'." near the line: cmd.ExecuteNonQuery();
    In my project I have 4 columns: Id(int), Name(varcar 50), Password(varchar 50) and FullName(varchar 100)
    So what now???

    ReplyDelete
  12. sorry my friends, i am new to programming. i keep getting this error after copying exactly as ypur codes look.

    on this code:
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectStrings["Sqlcon"].CoonectionString);

    And the error is :
    NullReferenceException was unhandled.
    An inhandled exception of type 'System.NullReferenceException' ocvured in savedata.exe


    Any help please. thanks guys.

    Cecane.

    ReplyDelete
  13. your Post was very hopeful but when i try to delete i got error can you fix this?

    ReplyDelete