Insert into Database with GridView Edit Update and Delete in ASP.NET using C#

Hi everyone, today I will show you how to insert into a database and update, edit, delete into a GridView using ASP.NET and C# as a request of  Shruti Upari.

So create a new project in your Visual Studio and also open the SQL management Studio to create the database. First lets check the database. Create a new database and creata a new table for your project. Here i am working with just one table ie tblUser.

CREATE TABLE [dbo].[tblUser](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [Email] [nvarchar](50) NULL,
    [pass] [nvarchar](50) NULL
) ON [PRIMARY]

Now come back to the ASP project and create three pages, one for insert, second for show data and last one for edit & delete.

In the Insert page add the TextBoxes according to your table. Here you have to take TextBox for name, email and password. The design of insert page...

            


                

                

Insert into DB

                

            

                

                

                

            

                

                

                
                

            

                

                

                

            

                

                
Name 
                
:
                

                    
                
                

            

                

                
Email
                
:
                

                    
                    
                
                

            

                

                
Password
                
:
                

                    
                
                

            

                

                
Confirm Password
                
:
                

                    
                
                

            

                

                

                

            

                

                

                
                

            

                

                

                

            

Here I have show with TextBox with validations.
Now lets check the coding of the insert page.

SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["DemoDBConnectionString"].ToString());

protected void btnSave_Click(object sender, EventArgs e)
{
	SqlDataAdapter da;

	try
	{
		/* Check Email is already present or not */
		DataTable dt_email = new DataTable();
		da = new SqlDataAdapter("select * from tblUser where email='" + txtEmail.Text.Trim() + "'", con);
		da.Fill(dt_email);
		if (dt_email.Rows.Count > 0)
		{
			lblMessage.Text = "Email already exists.";
			lblMessage.ForeColor = Color.Red;
			return;   // important because it helps not to use else part
		}
		/* End chekcing */

		/* Entry into database */
		con.Open();
		SqlCommand cmd = new SqlCommand("INSERT INTO [DemoDB].[dbo].[tblUser] ([name],[Email] ,[pass]) VALUES ('" + txtName.Text.Trim() + "','" + txtEmail.Text.Trim() + "' ,'" + txtPassword.Text.Trim() + "')", con);
		cmd.Connection = con;
		if (cmd.ExecuteNonQuery() == 1)
		{
			lblMessage.Text = "Successfully done !";
			lblMessage.ForeColor = Color.Green;
		}
		else
		{
			lblMessage.Text = "Error !";
			lblMessage.ForeColor = Color.Red;
		}

		/* end entry */
	}
	catch (Exception ae)
	{
		lblMessage.Text = ae.Message;
		lblMessage.ForeColor = Color.Red;
	}
	finally
	{
		con.Close();
	}
}

Now the inserting is over. Now its time to show the data into show page. In show page we are using only a GridView to show the data. Lets check once.

Show page is also over. Its time for edit and delete. For this we will code in edit page. Check the front coding of the GridView.

Now lets check the coding to edit or delete the data. Previously in show page we are showing the GridView as AutoColumnGenerator=true. But here we made it false. And using a fillgrid() method to bind the GridView.

SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["DemoDBConnectionString"].ToString());

private void fillgrid()
{
	SqlDataAdapter da = new SqlDataAdapter("select * from tblUser",con);
	DataTable dt = new DataTable();
	da.Fill(dt);
	GridView1.DataSource = dt;
	GridView1.DataBind();
}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
	try
	{
		string ID = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblId")).Text;

		/* query to update db*/
		con.Open();
		SqlCommand cmd = new SqlCommand("delete tblUser  where id='" + ID + "'", con);
		cmd.Connection = con;
		if (cmd.ExecuteNonQuery() == 1)
		{
			lblMsg.Text = "Done !";
			lblMsg.ForeColor = Color.Green;
		}
		else
		{
			lblMsg.Text = "Error !";
			lblMsg.ForeColor = Color.Red;
		}
		/* end query */
	}
	catch (Exception ae)
	{
		lblMsg.Text = ae.Message;
		lblMsg.ForeColor = Color.Red;
	}
	finally
	{
		con.Close();
	}
	GridView1.EditIndex = -1;
	fillgrid();
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
	try
	{
		string ID = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblId")).Text;

		string name = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtName")).Text;
		string pass = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtPass")).Text;

		/* query to update db*/
		con.Open();
		SqlCommand cmd = new SqlCommand("update tblUser set name='" + name + "', pass ='" + pass + "' where id='" + ID + "'", con);
		cmd.Connection = con;
		if (cmd.ExecuteNonQuery() == 1)
		{
			lblMsg.Text = "Done !";
			lblMsg.ForeColor = Color.Green;
		}
		else
		{
			lblMsg.Text = "Error !";
			lblMsg.ForeColor = Color.Red;
		}
		/* end query */
	}
	catch (Exception ae)
	{
		lblMsg.Text = ae.Message;
		lblMsg.ForeColor = Color.Red;
	}
	finally
	{
		con.Close();
	}
	GridView1.EditIndex = -1;
	fillgrid();
}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
	GridView1.EditIndex = e.NewEditIndex;
	fillgrid();
}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
	GridView1.EditIndex = -1;
	fillgrid();
}

Now run the project and check your db to be filled or not. Download the full source code to execute.

Download Now