Changes are not saved to the SQL Server database

Question

The code compiles fine without error or warning, but the database does not change. I mean the changes are not saved to the database.

I wrote the following methods:

private void Test2()
    {
        connection = new SqlConnection();
        string Conn = @"Data Source=(LocalDB)\MSSQLLocalDB;"
                                    + @"AttachDbFilename=|DataDirectory|\User.mdf;"
                                    + "Integrated Security=True;"
                                    + "Connect Timeout=30";

       // string sqlString = Properties.Settings.Default.ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(Conn);
        try
        {
                string SQL = "UPDATE Primuser SET Following = @Following WHERE Insta = @Insta";
                SqlCommand sqlCommand = new SqlCommand(SQL, sqlConnection);
                sqlCommand.Parameters.AddWithValue("@Following", "123");
                sqlCommand.Parameters.AddWithValue("@Insta", "hgd");
                sqlCommand.CommandText = SQL;

                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();

                MessageBox.Show("Record Updated");

        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
        }

    }

and in this Code, the result is bigger than 0.

private void Test2()
    {
        connection = new SqlConnection();
        connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;"
                                    + @"AttachDbFilename=|DataDirectory|\User.mdf;"
                                    + "Integrated Security=True;"
                                    + "Connect Timeout=30";
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT * FROM Primuser";
        command.Connection = connection;

        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = command;
        DataSet dataset = new DataSet();
        adapter.Fill(dataset, "Primuser");

        foreach (DataRow row in dataset.Tables["Primuser"].Rows)
        {
            if (row["Insta"].ToString() == "1495")
            {
                row["Following"] = "1024";
            }
        }

        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        try
        {
           var result = adapter.Update(dataset, "Primuser");

            if (result > 0)
                MessageBox.Show("Update Successful.");
            else
                MessageBox.Show("Update Failed.");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

but the database does not get changed. There are no errors and other query is working. I can insert, delete or select, but update is not working.

Is this a winform or wpf application? do you have your database MDF file listed between your project files? — Steve, Jun 24 '18 at 13:00
@Steve yes, this is winform. yes, MDF file listed in Solution. — radin, Jun 24 '18 at 13:10
Then look at my answer here https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 — Steve, Jun 24 '18 at 13:18
You probably have two mdf files. You do not need to mdf file in the connection string. The SQL server already has the mdf file attached to the database so it is sufficient to just use the database name in the connection string. The database is not getting refresh when you write directly to the mdf file. — jdweng, Jun 24 '18 at 13:21
You should use the server and not the mdf. You can attach your mdf to the server. You can issues access the database from another PC if you do not use the server and you can have issue when the database is being access by multi-users if you do not use the SQL Server. — jdweng, Jun 25 '18 at 08:42

Changes are not saved to the SQL Server database

0 Answers0