I have a problem with updating records in a database table. Background: The user can fetch data that belongs with an image. The data is displayed in a form. In this form the user can add/change data and then save this to the database. This part works fine. My next step is to let the user click a checkbox in the form to choose one image. The chosen image gets the value "1" in column Publish. This works.
Here's my problem: I also want to let the user choose another image, this image does get a "1" in column Publish. But as the change is saved I want to update the column Publish so that the rest of the images gets a "0" in column Publish (there can be only one). This does not work for me. Thankful for help!

protected void ButtonImgTextToDB_Click(object sender, EventArgs e)
   {
       try
       {
           string BildID = TextBoxStartPictID.Text;
           string Rubrik = TextBoxHeadLineStart.Text;
           string Text = TextBoxTextStart.Text;
           int Publicera = Convert.ToInt32(CheckBoxPublishStart.Checked);

           if (CheckBoxPublishStart.Checked)
           {
               Publicera = 1;
           }
           else
           {
               Publicera = 0;
           }

           SqlConnection conn = new SqlConnection(config);
           conn.Open();
           string sql = "UPDATE StartImage SET Headline ='" + Rubrik
+ "', Text ='" + Text + "',  Publish =" + Publicera + " WHERE ID ='" +
BildID + "'";
           SqlCommand comm = new SqlCommand(sql, conn);
           comm.ExecuteNonQuery();

//The code works to here!


           string sql2 = "SELECT ID FROM StartImage";
           SqlCommand comm2 = new SqlCommand(sql2, conn);
           SqlDataReader dr = comm2.ExecuteReader();

           while (dr.Read())
           {
               int ControlID = Convert.ToInt32(dr[0]);
               if (Convert.ToString(ControlID) != BildID)
               {
                   string sql3 = "UPDATE StartImage SET Publish = '0'";
                   SqlCommand comm3 = new SqlCommand(sql3, conn);
                   comm3.ExecuteNonQuery();
               }

               //message "changes saved"
           }
           dr.Close();
           conn.Close();
       }
       catch (Exception)
       {
           //message "something wrong"
       }

       //clear textboxes and checkbox
   }
Re: problem updating column 80 80

Edit:
39. string sql3 = "UPDATE StartImage SET Publish = '0' WHERE ID = '"+ ControlID +"'";

Re: problem updating column 80 80

I made a few more changes, here's the whole button-click again:

protected void ButtonImgTextToDB_Click(object sender, EventArgs e)
   {
       try
       {
           string BildID = TextBoxStartPictID.Text;
           string Rubrik = TextBoxHeadLineStart.Text;
           string Text = TextBoxTextStart.Text;
           byte Publicera;
           Publicera = Convert.ToByte(CheckBoxPublishStart.Checked);

           SqlConnection conn = new SqlConnection(config);
           conn.Open();
           string sql = "UPDATE StartImage SET Headline ='" + Rubrik
+ "', Text ='" + Text + "',  Publish =" + Publicera + " WHERE ID ='" +
BildID + "'";
           SqlCommand comm = new SqlCommand(sql, conn);
           comm.ExecuteNonQuery();

           string sql2 = "SELECT ID FROM StartImage";
           SqlCommand comm2 = new SqlCommand(sql2, conn);
           SqlDataReader dr = comm2.ExecuteReader();

           while (dr.Read())
           {
               if (Convert.ToString(dr[0]) != BildID)
               {
                   string sql3 = "UPDATE StartImage SET Publish = '1'";
                   SqlCommand comm3 = new SqlCommand(sql3, conn);
                   comm3.ExecuteNonQuery();
               }

           }
           dr.Close();
           conn.Close();
       }
       catch (Exception)
       {
           //message "something wrong"
       }
       //message "changes saved"

       //clear textboxes + checkbox
   }
Re: problem updating column 80 80

It looks like the Where condition is missing from line 27 UPDATE StartImage SET Publish = '1'

You can execute the SQL Statements at MS SQL Server Management Studio directly to see if the result is showing as expected.

For the UPDATE statement, you can see this link:
UPDATE Statement
http://www.sql-statements.com/sql-update-statement.html

Re: problem updating column 80 80

It does, but that was not the real problem. I found another solution that doesn't involve "not equal to" at all so I guess this discussion isn't going anywhere... :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.