Exception Error in trying to update database using asp.net

Question

How come I'm getting this error while trying to update my database?

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

Here is the code:

cmd1 = new SqlCommand("UPDATE [guitarBrands] SET type = @type, name = @name, image = @image WHERE id = @id", con1);

con1.Open();

cmd1.Parameters.Add(new SqlParameter("type", newType.Text));
cmd1.Parameters.Add(new SqlParameter("name", newName.Text));
cmd1.Parameters.Add(new SqlParameter("image", newImage.Text));
cmd1.Parameters.Add(new SqlParameter("id", id));

cmd1.ExecuteNonQuery();

con1.Close();
cmd1.Parameters.Clear();

cmd = new SqlCommand("UPDATE [guitarItems] SET brand = @brand WHERE id = @id", con1);
con.Open();

cmd.Parameters.Add(new SqlParameter("brand", newName.Text));
cmd.Parameters.Add(new SqlParameter("id", id));

cmd.ExecuteNonQuery();
con.Close();

cmd.Parameters.Clear();

You are not handling your [IDisposable](https://msdn.microsoft.com/en-us/library/system.idisposable(v=vs.110).aspx) (SqlConnection) items correctly. They should be wrapping in using statements so they get disposed of properly. This will also help with the scope issue in your variables. Plus, why are you using two different connections? Are you dealing with one database, or two? — mason, May 20 '17 at 14:37
You should make your variable names more explicit. You have confusion because you're using both `con` and `con1`. You're executing two queries with `con1`, and none with `con`. Look at the arguments you pass to your second `SqlCommand`. — mason, May 20 '17 at 14:43
@mason - oh i see..your right. I will try the code if all works now.. — BrunoEarth, May 20 '17 at 14:44

Greg · Accepted Answer · 2017-05-23T14:53:59.147

3

To avoid these sort of issues, it is recommended you utilize the tower of power.

using(var connection = new SqlConnection(dbConnection))
{
     connection.Open();
     using(var command = new SqlCommand(query, connection)
     {

     }

     using(var command = new SqlCommand(query, connection)
     {

     }
}

So the beauty of the tower of power, the using block will implement via within the given code block. So this will make it clear, that both these commands are utilizing the same connection from the using. Also, once the code is out of scope it will implement the IDispose, which will call the garbage collector to free up your resources.

Also, should you choose. The SqlCommand, accepts a parameter array. So if you utilize a method call, you could simply do:

public static GetExample(string query, params SqlParameter[] parameters)
{
     using(var connection = new SqlConnection("YourDbConnection"))
     using(var command = new SqlCommand("YourQuery", connection))
     {
          connection.Open();
          if(parameters != null)
               if(parameters.Any())
                    command.Parameters.Add(parameters);

          command.ExecuteNonQuery();
     }
}

I can't recall if the collection is a add, add range, or concat. But either way the option exist.

edited May 23 '17 at 14:53

answered May 20 '17 at 14:40

Greg

11,302
2
48
79

@BrunoEarth If you have access to the instance, in which they both belong. You can modify the connection to speak to both. Example here. http://stackoverflow.com/questions/25253548/one-sql-command-with-two-connection-string – Greg May 20 '17 at 14:59
also one more question, is the string query in GetExample method necessary? because I noticed its not being used in that method. – BrunoEarth May 22 '17 at 07:27
@BrunoEarth It would be utilized, once you added your actual SqlConnection and SqlCommand. – Greg May 22 '17 at 13:14
can you kindly add it in your solution? so that l'll know where to place the SqlConnection and SqlCommand.. – BrunoEarth May 22 '17 at 16:19
it seems like your solution is not working. The variable connection is not being recognized. – BrunoEarth May 23 '17 at 12:40
@BrunoEarth Your implementing it incorrectly then, because proper database connections are done like above. If you can post another question or edit your existing one so we can see your code. Also, you marked my answer then unmarked it because you can't get it to work correctly, you really shouldn't abuse that to hold over peoples. head – Greg May 23 '17 at 14:55

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

0

cmd = new SqlCommand("UPDATE [guitarItems] SET brand = @brand WHERE id = @id", con1);

replace this line to this

cmd = new SqlCommand("UPDATE [guitarItems] SET brand = @brand WHERE id = @id", con);

answered May 20 '17 at 15:11

SynozeN Technologies

1,337
1
14
19

score 0 · Answer 3 · answered May 20 '17 at 17:57

In your code you use Connexion "con1" in both Commands "cmd1" and "cmd". It is OK to use just one connexion for both commands but then you should leave the connexion open until both command are executed. In your case you choose to use a new connexion "con" for the second command but you reopen "con1". So you get an error because "con" is never opened.

Exception Error in trying to update database using asp.net

3 Answers3