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 don't 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 any 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);  

Answer 3

string constr = @"Data Source=(LocalDB)\v11.0;Initial Catalog=Bank;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(constr);
DataSet ds = new DataSet();
con.Open();
SqlCommand cmd = new SqlCommand("UPDATE Account SET name = Aleesha, CID = 24 WHERE name = 'Areeba' and CID = 11 )";
cmd.ExecuteNonQuery();

Answer 4

There is always a proper syntax for every language. Similarly SQL(Structured Query Language) has also specific syntax for update query which we have to follow if we want to use update query. Otherwise it will not give the expected results.

Answer 5

Please, never use this concat form:

String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
        + "WHERE supplier_id = " + textBox1.Text;

use:

command.Parameters.AddWithValue("@attribute", value);

Always work object oriented

Edit: This is because when you parameterize your updates it helps prevent SQL injection.

Answer 6

If you don't want to use the SQL syntax (which you are forced to), then switch to a framework like Entity Framework or Linq-to-SQL where you don't write the SQL statements yourself.

Answer 7

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

 

Answer 8

private void button4_Click(object sender, EventArgs e)
    {
        String st = "DELETE FROM supplier WHERE supplier_id =" + textBox1.Text;

        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            MessageBox.Show("刪除成功");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }



    private void button6_Click(object sender, EventArgs e)
    {
        String st = "SELECT * FROM suppliers";

        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            SqlDataReader reader = sqlcom.ExecuteReader();
            DataTable datatable = new DataTable();
            datatable.Load(reader);
            dataGridView1.DataSource = datatable;
            //MessageBox.Show("LEFT OUTER成功");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Answer 9

If you are using EF Core, then you can use FromSqlRaw Here is an example.

Using Robin's update string, just add SELECT STATEMENT at the end.

String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
            + "WHERE supplier_id = " + textBox1.Text; SELECT Id From supplier WHERE supplier_Id ="+ textBox1,Text

var updatedSupplier = context.Supplier.FromSqlRaw(st).ToList();

(Please note this is using EF Core, Data table has Id column)

Answer 10

String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
            + "WHERE supplier_id = " + textBox1.Text;

        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            MessageBox.Show("update successful");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }