CODEWITHSUNDEEP
asp.netwinforms
Ugine
Ugine

Reputation: 143

Unable to update database with the Window Form Application

I am trying to update my database with the window form I have created, however an error occurred when I execute the code:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll" at comm.ExecuteNonQuery();

Here is the code that I used to connect to my database. Was the code I used to update the database wrong?

string conn=ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
SqlConnection connection = new SqlConnection(conn);
SqlCommand comm = new SqlCommand("UPDATE ExerciseInstruction SET Accumulated_Daily_Sets_Completed = '0' WHERE ExerciseInstructionsID ='" + exerciseInstructionID +"'", connection);
comm.ExecuteNonQuery();

Here is the complete error message :

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Upvotes: 0

Views: 28

Answers (1)

Steve
Steve

Reputation: 216302

The error message explain all. The command cannot be executed if the code doesn't know how to reach the database. Just a call to connection.Open should fix the problem, however I think you need to use a proper way to execute the query.

This is called Parameterized query. In this way you don't concatenate strings together to form you query text but use parameters to pass values to the database engine and a special formatted string containing the parameters placeholders.

There are two main advantages to do so. It is not possible to target your code with Sql Injection hacks and you don't have to handle quoting around your strings (an infinite source of bugs)

string conn=ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

string cmdText = @"UPDATE ExerciseInstruction 
   SET Accumulated_Daily_Sets_Completed = 0 
   WHERE ExerciseInstructionsID =@exid";
using(SqlConnection connection = new SqlConnection(conn))
using(SqlCommand comm = new SqlCommand(cmdText, connection))
{
    connection.Open(); // Need this before executing the query
    comm.Parameters.Add("@exid", SqlDbType.Int).Value =  exerciseInstructionID;
    comm.ExecuteNonQuery();
}

Upvotes: 3

Related Questions