Unable to update SQL Database, Using C# Win Form

Question

My Application (C# Win Form) was working perfectly a while ago - i.e (Update, insert, delete...). But after I close the program and open the Database there are no changes being made.

I'm not getting any errors during running. I'm using VS2013 professional, SQL Database, C#.

using (SqlConnection connection = new SqlConnection(conString)) 
{ 
    SqlCommand cmd = new SqlCommand("UPDATE [FullInk] SET [InStock] = '" + 
           newSum + "' Where [Catalog] = '" + catalog + "'"); 
    cmd.CommandType = CommandType.Text; 
    cmd.Connection = connection; 
    connection.Open(); 
    cmd.ExecuteNonQuery();    
    connection.Close(); 
}

Mention/Provide Some code...so we can understand and track problem — Khurram Sharif, May 31 '15 at 05:18
You are not committing your transactions and closing the application causes rollback to lose the changes? — James Z, May 31 '15 at 05:39
Maybe this is the problem. but at first it worked and i didn't changed the code. — user3661263, May 31 '15 at 07:33
using (SqlConnection connection = new SqlConnection(conString)) { SqlCommand cmd = new SqlCommand("UPDATE [FullInk] SET [InStock] = '" + newSum + "' Where [Catalog] = '" + catalog + "'"); cmd.CommandType = CommandType.Text; cmd.Connection = connection; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } — user3661263, May 31 '15 at 08:00
can't find any issue in your code..can u check the value coming in newSum and catalog? may be the condition is failing thats why update is not happening — Sachu, May 31 '15 at 08:26
I have checked it. The value is fine. I have other forms in the project that i make changes and non of them is working anymore. The thing is that this code, as is, worked fine few days ago. I also making checking in the program and not getting error. it seems that the DB is changing but after i close the App all the changing not saved. — user3661263, May 31 '15 at 08:44
I can't check the DB while the program runs. but i have section in the code that check the tables i update and its look fine.. — user3661263, May 31 '15 at 08:54
you can check this question hope it helps [http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460] — Sachu, May 31 '15 at 08:58
Hi, I think I know what is my problem.. I have 2 files of .mdf. one at path: \DataBase1.mdf and the other at path: \bin\Debug\DataBase1.mdf. maybe my connectionString is not defined correctly? — user3661263, Jun 01 '15 at 09:30

StuartLC · Answer 1 · 2015-05-31T10:14:38.810

In the absence of any additional transaction such as a TransactionScope or SqlTransaction (which we can't see in your code), there is no reason why the update will be rolled back. I believe you might not actually be updating the data you think.

Although not necessarily the solution, it is much better practice to use parameterized queries rather than using strings - this has security (Sql Injection), performance (query plan caching) and also helps to eliminate bugs relating to quotes, escaping, and type conversion (which might be the case here - e.g. you are inserting newSum which could imply a numeric value into InStock using quotes, which implies a char type). e.g.

using (var connection = new SqlConnection(conString)) 
using (var cmd = new SqlCommand("UPDATE [FullInk] SET [InStock] = @InStock Where [Catalog] = @Catalog")) 
{ 
    cmd.CommandType = CommandType.Text; 
    cmd.Connection = connection; 
    cmd.Parameters.AddWithValue("@InStock", newSum);
    cmd.Parameters.AddWithValue("@Catalog", catalog);
    connection.Open(); 
    cmd.ExecuteNonQuery();    
}

Other minor modifications include disposing of the SqlCommand, and also note that disposing a connection will also close it, so you won't need to explicitly close it (although doing so won't hurt).

score 0 · Answer 2 · answered May 31 '15 at 11:20

Try This: It Might work for you...

    string Query = "UPDATE FullInk SET InStock = '" + newSum + "' Where Catalog = '" + catalog + "'";
    SqlConnection connection = new SqlConnection(conString);
    connection.Open();
    SqlCommand cmd = new SqlCommand(Query, sql_con);
    cmd.ExecuteNonQuery();    
    connection.Close();

Also add breakpoints and check weather your code is executing or not Cause your code seems fine,& should be working

Unable to update SQL Database, Using C# Win Form

2 Answers2