0

No matter what I do I get this error during run-time:

ExecuteReader requires an open and available Connection.

I have verified that this error is thrown with every SQL service access in my app, I've made sure that every time I access the DB, the connection is opened and then closed respectively.

Hare's an example of my DB class Code:

    public void ConexionDB()
    {
        try
        {
            cnx = new SqlConnection();
            cnx.ConnectionString = "Data Source=DESKTOP-GAHK8CT;Initial Catalog=WInventario;Integrated Security=True";
            cnx.Open();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }

    public void DELETE_Lineas(int ID)
    {
        try
        {
            ConexionDB();
            SqlCommand cmd = new SqlCommand("spDELETE_Linea", cnx);
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@ID_Linea", ID));
            cmd.ExecuteNonQuery();
            cnx.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            cnx.Close();
            throw;
        }
    }

My Application Pool Settings

I have also made sure that the folder that contains has all the permissions with all the available users (Full access).

I'm not sure what else I can check to make it work.

squillman
  • 13,363
  • 3
  • 41
  • 60
Carcass696
  • 9
  • 2
  • Also It is important to note that for some reason it works perfectly on Visual Studio – Carcass696 Aug 10 '22 at 17:31
  • `While trying to publish...` Do you mean you get this error during deployment or after you've deployed and you get the error at run-time? – squillman Aug 10 '22 at 17:33
  • It deployed successfully, I'm getting this at runtime – Carcass696 Aug 10 '22 at 17:35
  • `DESKTOP-GAHK8CT` is presumably your development PC? I imagine you're actually getting another exception when you try to open the connection at `cnx.Open();`. I would think that the app pool identity on the server does not have access to the SQL Server instance on your PC. – squillman Aug 10 '22 at 17:40
  • I'm sorry I should have specified that, I'm trying to host it on my dev PC which is DESKTOP-GAHK8CT. Also How would I go and change it so that the App Pool gets the permission it needs to access sql? My apologies I'm very new at this – Carcass696 Aug 10 '22 at 17:57
  • Ok, that would be fine. But I still expect that the built in app pool identity doesn't have access to your SQL Server instance. – squillman Aug 10 '22 at 17:59
  • Here [ExecuteReader requires an open and available Connection. The connection's current state is Connecting](https://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren) you will find the solution to the same problem. – Anderson Constantino Aug 10 '22 at 18:09
  • oh god does that mean I have to refactor mi 50+ sql functions in my class? also after reading that article, why would it work on Visual studio IIS but not "real" IIS? – Carcass696 Aug 10 '22 at 18:21
  • You have an anti-pattern in your code. Your ConexionDB method swallows any errors instead of handling them. You need to know if there is an exception, not just print a line in the console window. Also, I would urge to look into the `USING` statement and wrap your connection objects in them. Also your connection string values really should be in a config file, not hard coded. – Sean Lange Aug 10 '22 at 18:40
  • even after wrapping it with the using statement still has the same result, Is there any setting in IIS or something that might be alluding to what I am experiencing? – Carcass696 Aug 10 '22 at 20:26
  • Seems like `cnx` is getting shared amongst multiple methods, don't do that. This is probably a threading issue where one request is invoking `cnx.Close()` just after another thread has invoked `cnx.Open()` and stomping on the connection. Don't use a shared `cnx` variable like that, just return the connection instance as the result of `ConexionDB`. Although, really, `SqlConnection` and `SqlCommand` are `IDisposable` objects so should be getting wrapped in `using` blocks wherever they're referenced. – AlwaysLearning Aug 10 '22 at 21:41
  • That is saying the Connection is not Open - so all you need to do is call Open on myConnection at the top of the method, and Close when you are finished with it. write your code like this : Con.Open(); //Open the Connection da.Fill(dt) Con.Close(); //Close the connection so that other processes can open the connection if they need use con.open(); before executing wherever execute reader method is used. Set the iis app pool identity to the sql server user or you can add the iis user in sql database allow list – Jalpa Panchal Aug 11 '22 at 08:33

1 Answers1

0

Change -> Data Source=DESKTOP-GAHK8CT to Data Source=localhost

Pradip Daunde
  • 381
  • 2
  • 5