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 (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
– mason, Commented May 20, 2017 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
– mason, Commented May 20, 2017 at 14:43
@mason - oh i see..your right. I will try the code if all works now.. — BrunoEarth
– BrunoEarth, Commented May 20, 2017 at 14:44

Greg · Accepted Answer · 2017-05-23 14:53:59Z

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, 2017 at 14:53

answered May 20, 2017 at 14:40

Greg

11.5k2 gold badges51 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Greg Over a year ago

@BrunoEarth If you have access to the instance, in which they both belong. You can modify the connection to speak to both. Example here. stackoverflow.com/questions/25253548/…

BrunoEarth Over a year ago

also one more question, is the string query in GetExample method necessary? because I noticed its not being used in that method.

Greg Over a year ago

@BrunoEarth It would be utilized, once you added your actual SqlConnection and SqlCommand.

BrunoEarth Over a year ago

can you kindly add it in your solution? so that l'll know where to place the SqlConnection and SqlCommand..

BrunoEarth Over a year ago

it seems like your solution is not working. The variable connection is not being recognized.

|

SynozeN Technologies · Accepted Answer · 2017-05-20 15:11:33Z

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, 2017 at 15:11

SynozeN Technologies

1,3431 gold badge14 silver badges19 bronze badges

Comments

Rudy De Hon · Accepted Answer · 2017-05-20 17:57:26Z

0

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.

answered May 20, 2017 at 17:57

Rudy De Hon

1

Collectives™ on Stack Overflow

Exception Error in trying to update database using asp.net

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related