Datagrid view is not updating when update button is clicked

Question

Actually, when I click on the rows or cells of the datagrid view, they are populating into the text boxes to edit, after I editing and clecked on the update, the datagridview is not changing instantly, if I close and run the form again, it is changing. My requirement is it should change immediately after I click on the update button. The code I am using for update click is:

 private void btnUpdate_Click(object sender, EventArgs e)
 {
        SqlConnection con = Helper.getconnection();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        string PrjID = txtPrjID.Text;
        string PrjName = txtPrjNmae.Text;
        string Description = txtPrjdescription.Text;
        string Date = txtPrjDate.Text;
        string Size = txtPrjSize.Text;
        string Manager = txtPrjManager.Text;
        cmd.CommandText = "Update Projects set ProjectName= '" + PrjName + "', Description='" + Description + "', DateStarted='" + Date + "',TeamSize='" + Size + "',Manager='" + Manager + "' where ProjectID= " + PrjID + " ";
        MessageBox.Show("Project Details are updated");
        dataGridView2.Update();
        dataGridView2.Refresh();
        con.Open(); 
        cmd.ExecuteNonQuery();
        con.Close();
  }

Please tell me what is the mistake I am doing.

Answer 1

SqlConnection con = Helper.getconnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandType = CommandType.Text;
            string PrjName = txtPrjNmae.Text;
            string Description = txtPrjdescription.Text;
           string Date = txtPrjDate.Text;
            string Size = txtPrjSize.Text;
            string Manager = txtPrjManager.Text;
            cmd.CommandText = "Update Projects set Description='" + Description + "', DateStarted='" + Date + "',TeamSize='" + Size + "',Manager='" + Manager + "' where ProjectName= '" + PrjName + "' ";
            MessageBox.Show("Project Details are updated");
            dataGridView2.Update();
            dataGridView2.Refresh();
            cmd.ExecuteNonQuery();
            con.Close();

Answer 2

two important things to remember:

first of all when using DB or Streams you should use a using statement or try catch finally and close the connection in the finally block.

second, if you want to update the dataGridView after DB update then you should do it after the update. in your code you are doing it before the update.

third and most important your DB command is dangerous and open to SQL Injections. using parametrized command is almost always better:

private void btnUpdate_Click(object sender, EventArgs e)
{
    UpdateDB();
    dataGridView2.Update();
    dataGridView2.Refresh();
}

private void UpdateDB()
{
    using (DbConnection con = Helper.getconnection())
    {
        con.Open();

        using(DbCommand cmd = con.CreateCommand("Update Projects set ProjectName= @PrjName, Description=@Description, DateStarted=@Date, TeamSize=@Size,Manager=@Manager where ProjectID=@PrjID "))
        {

           cmd.CommandType = CommandType.Text;
           cmd.Parameters.Add(new SqlParameter("PrjName", txtPrjNmae.Text));
           cmd.Parameters.Add(new SqlParameter("Description", txtPrjdescription.Text));
           cmd.Parameters.Add(new SqlParameter("Date", txtPrjDate.Text));
           cmd.Parameters.Add(new SqlParameter("Size", txtPrjSize.Text));
           cmd.Parameters.Add(new SqlParameter("Manager", txtPrjManager.Text));
           cmd.Parameters.Add(new SqlParameter("PrjID", txtPrjID.Text));
           cmd.Connection = con;

           cmd.ExecuteNonQuery();
        }
    }
}

now i don't know how you binded the data to the dataGridView, but it might be needed to fetch the data from the DataBase again. this part however, just contains of calling the same command you did in the start of the program

Answer 3

well I would say you are calling things in the wrong order for a start... and you should really have your connection within a using statement.

protected void btnUpdate_Click(object sender, EventArgs e)
{
    // Update DB first
    UpdateProjectDetails(txtPrjID.Text, txtPrjNmae.Text, txtPrjdescription.Text, txtPrjDate.Text, txtPrjSize.Text, txtPrjManager.Text);

    // Fetch new results from DB
    IEnumerable<ProjectDetail> projectDetails = GetProjectDetails();

    // Update UI
    dataGridView2.DataSource = projectDetails;
    dataGridView2.Update();
    dataGridView2.Refresh();

    // Alert the user
    MessageBox.Show("Project Details are updated");
}

public void UpdateProjectDetails(string prjID, string prjName string description, string date, string size, string manager)
{
    using (DbConnection con = Helper.getconnection())
    {
        con.Open();

        DbCommand cmd = con.CreateCommand();

        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Update Projects set ProjectName= '" + PrjName + "', Description='" + Description + "', DateStarted='" + Date + "',TeamSize='" + Size + "',Manager='" + Manager + "' where ProjectID= " + PrjID + " ";
        cmd.Connection = con;

        cmd.ExecuteNonQuery();
    }
}