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

user_19540722 0
Viewed 73

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.

I have established a connection on the SQL Server with 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 ?

webforms asp.net c#
2 Answers

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

I copied your code, 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 of the variables, I am executing the query, thus making it work. You were just missing:

com.ExecuteNonQuery();

Well done so far.

user_53401080

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

user_191521030
Related