Not able to connect to SQL, ERROR = ServerVersion 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException'

Question

I'm getting an error that I can't find an answer for anywhere online. It says

ServerVersion 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException'

I've blocked out some words as not to compromise our data. It's not even making the connection.

This connection string works in previous application version (Visual Studio 2012, .NET Framework 4.5.2 using Microsoft.Practices.EnterpriseLibrary.Data.Sql) so I know it's not the credentials. We are re-writing the old application so the new one is using Visual Studio 2015, .NET Framework 4.5.2, and System.Data.SqlClient.

Here is the web.config connection string:

<connectionStrings>
<add name="DataConnection" 
     connectionString="Data Source=mydatabaseserver\tst1;Initial Catalog=LeaseData;Persist Security Info=True;User ID=myID;Password=mypassword" 
     providerName="System.Data.SqlClient" />
</connectionStrings>

This is my c# code calling to the database using my connection string that is available in the web.config.

public static DataTable ContractDetails(string contractID, string pointOfContact)
    {
        string connString = ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            try
            {
                if (connString.Length > 0)
                {
                    using (SqlCommand cmd = new SqlCommand("spNameOfStoredProcedure"))
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Connection = conn;
                            da.SelectCommand = cmd;

                            cmd.Parameters.AddWithValue("@ContractID", contractID);
                            cmd.Parameters.AddWithValue("@PointOfContact", pointOfContact);
                            cmd.ExecuteNonQuery();

                            using (DataTable dt = new DataTable())
                            {
                                da.Fill(dt);
                            }
                        }
                    }
                }
            }
            catch (SystemException)
            {

            }
        }

Let me know if there is anything else that would be helpful in solving this issue!

Thanks!

Answer 1

In your screenshot you are viewing the properties of the connection prior to the connection being opened by the DataAdapter. Thus the connection is closed at that point - hence the InvalidOperationException on the call to ServerVersion. See MSDN.