Error; Connection has not been closed. Current connection status opens Sqlserver 2016

Question

I am creating an application in visual Studio in C#, and when connecting to sqlserver in Server Explore, everything was correct in connection test.

This is my connection string:

return "Data Source=DESKTOP-58ASAQP\\MSSQLSERVER16;Initial Catalog=ControleDeEstoque;User ID=sa;Password=root";

However, when entering a data in the form returns me the following message:

"Connection has not been closed. Current connection status is opened."

Has anyone ever made that mistake? This requires some configuration in Sqlserver?

Answer 1

This is simply because you are trying to open a connection to the bank when it is already open.

See the example below.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace stackoverflow
{
    public partial class ConexaoAberta : System.Web.UI.Page
    {
        protected String ConnString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["DBConn"].ToString();
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConnString;

            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dt = new DataTable();
            StringBuilder query = new StringBuilder();
            query.Append("SELECT *  FROM [stackoverflow].[dbo].[Categoria]");

            conn.Open();
            adapter.SelectCommand = new SqlCommand(query.ToString(), conn);
            adapter.Fill(dt);
            GetSubCategoria(conn);
        }

        public void GetSubCategoria(SqlConnection conn)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dt = new DataTable();
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * FROM [stackoverflow].[dbo].[SubCategoria]");

            conn.Open(); // Aqui você tenta abri novamente a conexão, sendo que o método acima não tinha
            // fechado ela.
            adapter.SelectCommand = new SqlCommand(query.ToString(), conn);
            adapter.Fill(dt);
        }
    }
}

In the last method public void GetSubCategoria(SqlConnection conn), can my connection and having open it again conn.Open(); , where the current status is open generating the error.

Surely your error is being generated when you try to enter your data.

Make sure the connection status is open, the most advisable would be to involve all your connections in blocks.

using .

using (var conn = new SqlConnection(_dbconnstr)) 
{
    //code
}

Or Try Finally.

SqlConnection conn = new SqlConnection(_dbconnstr);
try
{
    //code
}
finally
{
    conn.Dispose();
}