C# - Update SQL Table

Question

I want to update my sql table. I was searching here and found solutions on how to go onto that problem. But sadly it just wont update the database. I have no clue what the problem is. I checked to sql command a couple of times for writing mistakes but couldnt find any or fixed them but still sadly nothing. I suppose it's something within the try block but cant find it out.

This is my code:

string connetionString = null;
SqlConnection connection;
SqlCommand command;
string sql = null;
SqlDataReader dataReader;
connetionString = "Data Source=xxx\\xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx";
sql = "UPDATE Employees SET LastName = '" + Lnamestring + "', FirstName = '" + Fnamestring + "', Title = '" + Titelstring + "', TitleOfCourtesy = '" + ToCstring + "', BirthDate = '" + Birthdatestring + "', HireDate = '" + Hiredatestring + "', Address = '" + Adressstring + "', City = '" + Citystring + "', Region = '" + Regionstring + "', PostalCode = '" + Postalstring + "', Country = '" + Countrystring + "', HomePhone = '" + Phonestring + "', Extension = '" + Extensionsstring + "', Notes = '" + Notesstring + "', ReportsTo = '" + ReportTostring + "' WHERE EmployeeID = '" + IDstring + "'; ";
connection = new SqlConnection(connetionString);
try
{
    connection.Open();
    command = new SqlCommand(sql, connection);
    SqlDataAdapter sqlDataAdap = new SqlDataAdapter(command);

    command.Dispose();
    connection.Close();
    MessageBox.Show("workd ! ");

}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection ! ");
}

I hope someone can help me find my mistake.

EDIT: when i try it out it seems to work as the windows pops up with "workd" but the database is unchanged.

What exactly is happening? Is an error occurring? P.S Make use of [Using Statement](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement) P.P.S Make use of parameterisation — IronAces, Sep 06 '17 at 07:02
try with `command.ExecuteNonQuery();` before `command.Dispose();` — Nino, Sep 06 '17 at 07:05
Try using `Integrated Security = false` when providing login credentials for connection string [link](https://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi) and add `providerName="System.Data.SqlClient"` to connection string too. — JB's, Sep 06 '17 at 07:08

score 8 · Accepted Answer · answered Sep 06 '17 at 07:39

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}");
}

Thanks worked like a charm and yes it was very helpful seeing how it was meant. Because im coding in C# for two days now :) — MansNotHot, Sep 06 '17 at 07:41

score 3 · Answer 2 · answered Sep 06 '17 at 07:07

Few issues with your code:

1) Use using, when working with IDisposable objects, in your case connection and command.

2) As suggested in comments, use SqlCommandParameters instead of concatenating strings for security reasons (google "preventing from SQL injections")

3) You don't execute your query! How you want it to make an impact if you don't do it? There's, for example, method like ExecuteNonQuery in SqlCommand class.

score 0 · Answer 3 · edited Sep 06 '17 at 07:10

string connetionString = null;
SqlConnection connection;
SqlCommand command;
string sql = null;
SqlDataReader dataReader;
connetionString = "Data Source=EVOPC18\\PMSMART;Initial Catalog=NORTHWND;User ID=test;Password=test";
sql = "UPDATE Employees SET LastName = '" + Lnamestring + "', FirstName = '" + Fnamestring + "', Title = '" + Titelstring + "', TitleOfCourtesy = '" + ToCstring + "', BirthDate = '" + Birthdatestring + "', HireDate = '" + Hiredatestring + "', Address = '" + Adressstring + "', City = '" + Citystring + "', Region = '" + Regionstring + "', PostalCode = '" + Postalstring + "', Country = '" + Countrystring + "', HomePhone = '" + Phonestring + "', Extension = '" + Extensionsstring + "', Notes = '" + Notesstring + "', ReportsTo = '" + ReportTostring + "' WHERE EmployeeID = '" + IDstring + "'; ";
connection = new SqlConnection(connetionString);
try
{
    connection.Open();
    command = new SqlCommand(sql, connection);
    SqlDataAdapter sqlDataAdap = new SqlDataAdapter(command);

    command.ExecuteNonQuery();

    command.Dispose();
    connection.Close();
    MessageBox.Show("workd ! ");

}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection ! ");
}

Don't forget to execute the command
Try to get the stacktrace or error message from Exception as much as possible. For example: MessageBox.Show($"Can not open connection ! {e.GetBaseException().Message}, {e.StackTrace}");

score 0 · Answer 4 · edited May 14 '22 at 06:23

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;

cmd.CommandText = "update CarTb1 set ( @RegNo , @MOdel , @Price , @Available  where @Brand);";
cmd.CommandType = System.Data.CommandType.Text;
Da = new SqlDataAdapter("Select * From CarTb1", con);
Da.Fill(Dt);
cmd.Parameters.AddWithValue("@RegNo", txtRegnumber.Text);
cmd.Parameters.AddWithValue("@Brand", combBrand.Text);
cmd.Parameters.AddWithValue("@Model", txtModel.Text);
cmd.Parameters.AddWithValue("@Price", txtPrice.Text);
cmd.Parameters.AddWithValue("@Color", txtColor.Text);
cmd.Parameters.AddWithValue("@Available", combAvailable.Text);
        con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Record Edited Successfally");
con.Close();
ClearData();

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — KiynL, May 15 '22 at 00:28

score -1 · Answer 5 · edited Sep 06 '17 at 07:42

-1

Please use the ExecuteNonQuery() instead of SqlDataAdapter:

connection.Open();
command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
MessageBox.Show("workd ! ");

edited Sep 06 '17 at 07:42

Michał Turczyn

32,028
14
47
69

answered Sep 06 '17 at 07:13

saravanan

1
1

add a few words of explaination, as to why this is a solution to the question – RealCheeseLord Sep 06 '17 at 07:31

C# - Update SQL Table

5 Answers5