Why doesn't my C# code update the SQL Server database although I get the correct number of affected rows

Question

I created the following code:

public static bool setHeadword(int id, string headword)
{       
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\pms.mdf;Integrated Security=True";
    conn.Open();

    SqlCommand command = new SqlCommand("UPDATE headwords SET Headword = @headword WHERE Id = @id", conn);

    command.Parameters.AddWithValue("@headword", headword);
    command.Parameters.AddWithValue("@id", id);

    int result = command.ExecuteNonQuery();
    conn.Close();

    return true;
}

But the code doesn't work because the value in the database doesn't change. If I run the code manually in the database the change takes place. But it won't work with C#.

Also the result variable are holding the right number of affected rows (1 in this case).

I'm not sure I have to flush the changes or something else.

Thanks for your help and best regards Franz

Your title says SQLite (one DB type) but the code uses the DB provider for SQL Server (a totally different type). Which is it. AddWithValue is ill advised in both cases though — Ňɏssa Pøngjǣrdenlarp, Sep 07 '18 at 16:49
1) it should work: how do you verify the results? 2) try to use `using (var conn = new SqlConnection)`; it disposes the connection properly. I think it also applies to the `SqlCommand` object. See: https://stackoverflow.com/questions/5895879/when-do-we-need-to-call-dispose-in-dot-net-c#5895934 — Stefan, Sep 07 '18 at 16:50
Thanks Plutonix. This link helped. Setting the .mdf to "Copy if newer" worked — Franz, Sep 07 '18 at 17:35
Unrelated tips: `SqlConnection` and `SqlCommand` are both `IDisposable` so each should be in a `using` block. (The `conn.Close();` is redundant once you've done that because the Dispose will call Close as you exit the using block.) And [can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Richardissimo, Sep 07 '18 at 20:59

score -2 · Answer 1 · answered Sep 07 '18 at 17:19

-2

static void Update(int id, string headword)
{
   try
   {    
   //You should create connectionString with correct details otherwise fail connection
    string connectionString =
        "server=.;" +
        "initial catalog=employee;" +
        "user id=sa;" +
        "password=123";
    using (SqlConnection conn =
        new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd =
            new SqlCommand("UPDATE headwords SET Headword=@headword" +
                " WHERE Id=@Id", conn))
        {
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@headword", headword);

            int rows = cmd.ExecuteNonQuery();


        }
    }
   }
catch (SqlException ex)
{
    //Handle sql Exception
}

}

answered Sep 07 '18 at 17:19

Dev006

87
1
13

1

Thanks for the suggestions. Setting the Database mdf-File to "Copy if newer" also solved the problem. – Franz Sep 07 '18 at 17:37
1

First of all, there was no mention of any exceptions in the question, so "otherwise fail connection" seems misplaced. Secondly, the connection string is actually correct, provided you want to connect to SQL Server localdb. Thirdly, your answer only contains code, no explanation of why your code differs from the the one in the question, and no hints as to why (you think) this is more correct. – Lasse V. Karlsen Sep 07 '18 at 19:01
Provide example code, It isn't meaning franz's source code is wrong. Provide example code have some ideas. Exception handling best practices.Exceptions can be thrown and caught so the application can recover or continue gracefully. Unhandled exceptions (which are errors) can also be logged so they are looked at by a developer to fix the underlying error. if you have answer then put it. Don't judge other answer. – Dev006 Sep 07 '18 at 19:25

Why doesn't my C# code update the SQL Server database although I get the correct number of affected rows

1 Answers1