Connection to SQL Server 2008 R2 isn't working

Question

I tried to test if my application is connected to the local database. I don't get any errors, so couldn't quite figure out why it's not working. I only get "no connection" output. I tried to debug it but get connection = null. I have SQL Server 2008 R2 (mixed authentication) and Visual Studio 2008 sp1. I tried connecting using both Windows and SQL Server authentication however neither worked.

This is my web.config file.

<connectionStrings>
    <add name="MyDbConn" 
         connectionString="Data Source=local;Initial Catalog=Sample;Integrated Security=True;" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

Default.aspx.cs

public partial class _Default: System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e) 
    {
    }
    protected void btnTestDb_Click(object sender, EventArgs e)
    {
        try {
            SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=Sample; Integrated Security=SSPI");
            connection.Open();
            if (connection != null && connection.State == ConnectionState.Closed) {
                Response.Write("Connection OK!");
                connection.Close();
            } else {
                Response.Write("No Connection!");
            }
        } catch {
            Response.Write("No Connection!");
        }
    }
}

Answer 1

You have some problems in your code that tries to open the connection.

When you try to call connection.Open, the result is an exception if you have problems or simply a connection with its ConnectionState=Open.
Your code instead gives the "Connection OK" message if the ConnectionState is closed, but of course, having just called Open, and if you don't have problems, then the ConnectionState is open.

You could try this code....

protected void btnTestDb_Click(object sender, EventArgs e)
{
    string result = TestConnection();
    Response.Write(result);
}
private string TestConnection()
{
    try
    {
        using(SqlConnection connection = new SqlConnection("...."))
        {
             connection.Open();
             return "Connection opened correctly";
        }
    }
    catch(Exception ex)
    {
        return "Error opening the connection:" + ex.Message;
    }
}

Answer 2

 //Try this
 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ToString());

protected void btnTestDb_Click(object sender, EventArgs e)
 {
    try {
       con.Open();
        if (con.State == ConnectionState.Open)
        {
            Response.Write("Connection Open");
        }
        else
        {
            Response.Write("Connection Closed");
        }
        con.Close();
    } catch {
        Response.Write("No Connection!");
    }
}

Answer 3

The written ado.net code will open the connection for sure, but I doubt the connection string will do. Choose appropriate connectionstring from this dedicated site.