How to Search and modify data of columns on database?

Question

I have two databases. One have only one column which will store fizic locations, and i have connected it to a dropdown menu. The second one have all the products with multiple columns, one of them is the location.

I want to add a update location name for my first table, and when this is updated, all the products in the second table which are located on that location, have the location name changed as well.

Unfortunately what i have tried, only create a new location leaving the old one right in place. On the second table, nothing happens.

NameLabel have the name of the curent location name, received from the previous form. The textbox have the new name i want to update.

private void Update_location_Click(object sender, EventArgs e)
{
    con.Open();
    string name = NameLabel.Text;

    SqlCommand dataAdapter = new SqlCommand("UPDATE dbLocation SET loc_name = @loc_name WHERE loc_name = '" + name + "'", con);

    dataAdapter.Parameters.AddWithValue("@loc_name", textbox_location.Text);
    dataAdapter.ExecuteNonQuery();

    SqlCommand dataAdapter2 = new SqlCommand("UPDATE DB_Main SET Location = @location WHERE Location = '" + name + "'", con);
    dataAdapter2.Parameters.AddWithValue("@location", textbox_location.Text);
    dataAdapter2.ExecuteNonQuery();

    con.Close();
    MessageBox.Show("Location updated!");
    this.Close();
}

Answer 1

Two things:

1. The update condition should be based on ID.

   UPDATE dbLocation 
   SET loc_name = @loc_name 
   WHERE loc_id = loc_id
   

2. The second table should only contain the Id and not the location name that way if the location name changes, it will automatically reflect in the second table (if you have used join)