C# Web Forms : Update Set doesn't update Row-CodePudding

I am using ASP .NET Web Forms . I have 8 Textboxes in which the user can input data for a specific row on the SQL and make changes on the database accordingly for a car , with make , model , year , engine , the rate , availability and location.

I am using the Update Set SQL query as seen on the code line "string query" but it doesn't do anything to the database. The row already exists.

I have established a connection on the SQL Server with Visual Studio , I am using Visual Studio 2022

string a = TextBox1.Text;
            string b = TextBox2.Text;
            string c = TextBox3.Text;
            string d = TextBox4.Text;
            string ee = TextBox5.Text;
            string f = TextBox6.Text;
            string g = TextBox7.Text;
            string h = TextBox8.Text;


            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TLESConnectionString"].ConnectionString);
            conn.Open();
            string query = "UPDATE MyCar SET Make = @Make, Model = @Model, Year = @Year ,Engine = @Engine , Rate = @Rate ,Availability = @Availability , Location = @Location";

            SqlCommand com = new SqlCommand(query, conn);
            com.Parameters.AddWithValue("@Make", a);
            com.Parameters.AddWithValue("@Model", b);
            com.Parameters.AddWithValue("@Year", c);
            com.Parameters.AddWithValue("@Engine", d);
            com.Parameters.AddWithValue("@Rate", ee);
            com.Parameters.AddWithValue("@Availability", f);
            com.Parameters.AddWithValue("@Location", g);
            conn.Close();

What is your suggestion as to why it is not updating the Row ?

CodePudding user response：

Dont you need to add a condition as to where you want to update the values?

Example from w3schools

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Also have you checked if the connection string and the query are both correct? You can use the Debug for that, check the string value / content for both the connection and the query

On top of that, I noticed that you opened a connection and created a Command, but you didnt run the command, you close the connection, are you running the command?

You could do that with:

try
    {
         Com.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
          return;
      }

EDIT: You only need to use the 'Where' clause if you want to update a specific value rather than the entire table, If you omit the 'WHERE' clause, ALL records will be updated

CodePudding user response：

Your code is missing a few aspects. You are defining the query which is correct, you set the parameters and values correctly however you never execute the query and after setting the variables , you close the connection.

I copied your code and I made these changes and it worked for me. Give it a try.

 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TLESConnectionString"].ConnectionString);
            conn.Open();
            string query = "UPDATE MyCar SET Make = @Make, Model = @Model, Year = @Year ,Engine = @Engine , Rate = @Rate ,Availability = @Availability , Location = @Location";
            SqlCommand com = new SqlCommand(query, conn);
            com.Parameters.AddWithValue("@Make", a);
            com.Parameters.AddWithValue("@Model", b);
            com.Parameters.AddWithValue("@Year", c);
            com.Parameters.AddWithValue("@Engine", d);
            com.Parameters.AddWithValue("@Rate", ee);
            com.Parameters.AddWithValue("@Availability", f);
            com.Parameters.AddWithValue("@Location", g);
            com.ExecuteNonQuery();

As you can see , after inputting all the variables, I am executing the query, thus making it work. You were missing com.ExecuteNonQuery(); only. Well done so far.