SQL update statement in C#

Question

I have table "Student"

   P_ID   LastName  FirstName  Address  City     1        Hansen    Ola                    2        Svendson   Tove    3        Petterson   Kari    4        Nilsen       Johan ...and so on  

How do i change edit code in C#

 string firstName = "Ola";  string  lastName ="Hansen";  string  address = "ABC";  string city = "Salzburg";   string connectionString = System.Configuration.ConfigurationManager                           .ConnectionStrings["LocalDB"].ConnectionString;   using (SqlConnection connection = new SqlConnection(connectionString))      using (SqlCommand command = connection.CreateCommand())  {     command.CommandText = "INSERT INTO Student (LastName, FirstName, Address, City)                            VALUES (@ln, @fn, @add, @cit)";     command.Parameters.AddWithValue("@ln", lastName);    command.Parameters.AddWithValue("@fn", firstName);    command.Parameters.AddWithValue("@add", address);    command.Parameters.AddWithValue("@cit", city);     connection.Open();     command.ExecuteNonQuery();     connection.Close();  }  

to edit entry where Lastname field has lastname value and FirstName field has firstname value.

I dont want to use like this

 UPDATE Persons  SET Address='Nissestien 67', City='Sandnes'   WHERE LastName='Tjessem'     AND FirstName='Jakob' 

and i edited my original statement to

 command.CommandText = "UPDATE Student(LastName, FirstName, Address, City)     VALUES (@ln, @fn, @add, @cit) WHERE LastName='" + lastName +                             "' AND FirstName='" +  firstName+"'"; 

but the statement is not getting executed, why is it throwing SQL exception ? Is there nay solution to it ?

Answer 1

This is not a correct method of updating record in SQL:

command.CommandText = "UPDATE Student(LastName, FirstName, Address, City) VALUES (@ln, @fn, @add, @cit) WHERE LastName='" + lastName + "' AND FirstName='" + firstName+"'"; 

You should write it like this:

command.CommandText = "UPDATE Student  SET Address = @add, City = @cit Where FirstName = @fn and LastName = @add"; 

Then you add the parameters same as you added them for the insert operation.

Answer 2

I dont want to use like this

That is the syntax for Update statement in SQL, you have to use that syntax otherwise you will get the exception.

command.Text = "UPDATE Student SET Address = @add, City = @cit Where FirstName = @fn AND LastName = @ln"; 

and then add your parameters accordingly.

command.Parameters.AddWithValue("@ln", lastName); command.Parameters.AddWithValue("@fn", firstName); command.Parameters.AddWithValue("@add", address); command.Parameters.AddWithValue("@cit", city);