SQL Update Statement in C#

SQL update statement in C#

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.

C# - Update SQL Table

As Michał Turczyn wrote in his answer, you have some problems with your code.

I agree with everything he wrote, but I thought you might benefit from seeing how your code should look like - so here you go:

var connetionString = "Data Source=EVOPC18\\PMSMART;Initial Catalog=NORTHWND;User ID=test;Password=test";
var sql = "UPDATE Employees SET LastName = @LastName, FirstName = @FirstName, Title = @Title ... ";// repeat for all variables
try
{
    using(var connection = new SqlConnection(connetionString))
    {
        using(var command = new SqlCommand(sql, connection))
        {
            command.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = Lnamestring;
            command.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = Fnamestring;
            command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Titelstring;
            // repeat for all variables....
            connection.Open();
            command.ExecuteNonQuery();
        }       
    }
}
catch (Exception e)
{
    MessageBox.Show($"Failed to update. Error message: {e.Message}");
}

Update SQL table using C#

There are some issues with your SQL update statement.Look at following for reference to Update Statement in SQL LINK

There is also an easier way to add the parameters using the AddWithValue Method. LINK

Next, you are not executing the SQL command. For Update statements use the ExecuteNonQuery() method. LINK

Also as @Nikki9696 mentioned, episodeId is not declared. Make sure to declare episodeId with your other variables.

int episodeId = 117;
int seriesNumber = 9;
int episodeNumber = 13;
string episodeType = "abnormal episode";
string title = "Reconsideration";
string notes = "recuring behaviour";

//connectionString

string connectionString = "data source=LAPTOP-VLO4EFFQ\\MSSQLSERVER01; database=DoctorWho; integrated Security=True;";

//connection using
using (SqlConnection conn = new SqlConnection(connectionString))
{

conn.Open();
Console.WriteLine("Connection sucessfull");

string query = "UPDATE tblEpisode " +        
    "SET SeriesNumber=@SeriesNumber, EpisodeNumber=@EpisodeNumber, EpisodeType=@EpisodeType, Title=@Title, Notes=@Notes " +
    " WHERE EpisodeId=@EpisodeId;";

using (SqlCommand command = new SqlCommand(query, conn))
{
    //updating data in the sql table with the initial variables  
    command.Parameters.AddWithValue("@EpisodeId", episodeId);
    command.Parameters.AddWithValue("@SeriesNumber", seriesNumber);
    command.Parameters.AddWithValue("@EpisodeNumber", episodeNumber);
    command.Parameters.AddWithValue("@EpisodeType", episodeType);
    command.Parameters.AddWithValue("@Title", title);
    command.Parameters.AddWithValue("@Notes", notes);
    
    command.ExecuteNonQuery();

}

conn.Close();
Console.WriteLine("connection is closed!!");
}

SQL command for update in C#

Change to use Parameters, it will save you a lot of trouble.

SqlCommand cmd = new SqlCommand("UPDATE dbo.Status SET Status = @status WHERE ActivateMember = @activateMember",mydatabase.cn);
cmd.Parameters.AddWithValue("status", "<span class=\"label label-success\">Success</span>");
cmd.Parameters.AddWithValue("activateMember", i);

C# SqlCommand query with update

If you want to add, just add:

cmd = new SqlCommand(@"UPDATE Users 
                          SET Debit = Debit + @debit, 
                              Score = Score + @score 
                        WHERE Phone = @phone", con);

Please, notice verbatim string @"..." syntax. Please, do not forget about disposing (explicit Close is an antipattern):

string sql = 
  @"UPDATE Users 
       SET Debit = Debit + @debit, 
           Score = Score + @score 
     WHERE Phone = @phone";

//TODO: put the right connection string instead of "MyConnectionStringHere"
//DONE: IDisposable (SqlConnection) should be wrapped into using 
using (var con = new SqlConnection("MyConnectionStringHere")) {
  con.Open();

  //DONE: IDisposable (SqlCommand) should be wrapped into using
  using (var cmd = new SqlCommand(sql, con)) {
    //TODO: AddWithValue is often a bad choice; change to Add 
    cmd.Parameters.AddWithValue("@phone", textBox1.Text);
    cmd.Parameters.AddWithValue("@debit", textBox2.Text);
    cmd.Parameters.AddWithValue("@score", textBox3.Text);

    cmd.ExecuteNonQuery();
    //TODO: a better policy is to read localized strings from resources
    MessageBox.Show("Амжилттай");
  }
}

SQL Update statement on Winforms

Few Instructions: You are trying the wrong syntax here for SQL UPDATE, IF you have to update more columns then each one should be separated with commas, not with AND, One more thing you have to take care of is that your code opens a wide door for hackers through injection, To close this door you have to use parameterized queries. Another thing( but not sure), The names txtNama, txtStock etc looks like the names of TextBoxes if so you have to use its .Text properties as well. if not use proper naming conventions.

In simple your code should be like the following:

MySqlCommand sqlCommand = new MySqlCommand("UPDATE barang SET Nama_barang =@Nama_barang,Jumlah_barang=@Jumlah_barang,Harga_awal=@Harga_awal,Harga_jual=@Harga_jual WHERE ID =@id", con);
sqlCommand.Parameters.AddWithValue("@Nama_barang", txtNama.Text);
sqlCommand.Parameters.AddWithValue("@Jumlah_barang", txtStock.Text);
sqlCommand.Parameters.AddWithValue("@Harga_awal", txtBeli.Text);
sqlCommand.Parameters.AddWithValue("@Harga_jual", txtJual.Text);
sqlCommand.Parameters.AddWithValue("@id", txtIndex.Text);
try
{
   con.Open();
   sqlCommand.ExecuteNonQuery();
   MessageBox.Show("sukses");
   con.Close();
}
catch (Exception ex)
{
   MessageBox.Show(ex.Message);
}

You can try .Parameters.Add() if the values are of different types,

Update SQL statement with two conditions in C#

You should use and at where clause:

   pendinglist.CommandText = " UPDATE Pending_List set room_status =" +
  "'Still Pending'" + " Where Class_ID = "+ "[your_id]" + " and room_status 
!= 'Still Pending'"

How can I write a SQL update query with a where clause using Entity Framework .NET Core

If you want use sql directly you can use ExecuteSqlCommand

If you were handling a object and then doing a update I would change a object and call SaveChanges, but that's not the case.. here is an update directly to the table, If that table has millions of rows you want perform sql to get performance on that.

example

using(var context = new SampleContext())
{
    var commandText = "UPDATE Table SET SomeDateTime = @NewDateTime WHERE Id = @MyId AND SomeDateTime > @NewDateTime";
    var newDateTime = new SqlParameter("@NewDateTime", myDateValue);
    var myId = new SqlParameter("@MyId", myIdValue);

    context.Database.ExecuteSqlCommand(commandText,  new[]{newDateTime,myId});
}

Update SQL command C#

use OleDBCommand

string updateCommand = "UPDATE RoomsTable SET [Date Checked]='9/27/2012'";
updateCommand = new OleDbCommand(updateCommand, updateConnection);

updateCommand.ExecuteNonQuery();
updateConnection.Close();

maybe you could refractor the code using Using statement and parameterized the query. and column name Date Checked should be escaped with brackets.

string updateCommand = "UPDATE RoomsTable SET [Date Checked]=@checkedDate WHERE ID = @id"; // '9/27/2012'
using (OleDbConnection conn = new OleDbConnection("connectionStringHERE"))
{
    using (OleDbCommand comm = new OleDbCommand())
    {
        comm.Connection = conn;
        comm.CommandText = updateCommand;
        comm.CommandType = CommandType.Text
        comm.Parameters.AddWithValue("@checkedDate", this.dateTimePicker1.Value)
        comm.Parameters.AddWithValue("@id", row.roomID);
        try
        {
            comm.Open();
            conn.ExecuteNonQuery();
        }
        catch(OleDbException ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }
}

---

Related Topics