NickName:user3399326 Ask DateTime:2015-06-11T06:34:36

Connection to SQL Server 2008 R2 isn't working

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!");
        }
    }
}

Copyright Notice：Content Author:「user3399326」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/30768469/connection-to-sql-server-2008-r2-isnt-working

Answers

Rajesh Shetty 2015-06-11T04:00:36

//Try this\n SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[\"MyDbConn\"].ToString());\n\nprotected void btnTestDb_Click(object sender, EventArgs e)\n {\n try {\n con.Open();\n if (con.State == ConnectionState.Open)\n {\n Response.Write(\"Connection Open\");\n }\n else\n {\n Response.Write(\"Connection Closed\");\n }\n con.Close();\n } catch {\n Response.Write(\"No Connection!\");\n }\n}\n",

Steve 2015-06-10T22:44:45

You have some problems in your code that tries to open the connection. \n\nWhen you try to call connection.Open, the result is an exception if you have problems or simply a connection with its ConnectionState=Open.\nYour 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.\n\nYou could try this code....\n\nprotected void btnTestDb_Click(object sender, EventArgs e)\n{\n string result = TestConnection();\n Response.Write(result);\n}\nprivate string TestConnection()\n{\n try\n {\n using(SqlConnection connection = new SqlConnection(\"....\"))\n {\n connection.Open();\n return \"Connection opened correctly\";\n }\n }\n catch(Exception ex)\n {\n return \"Error opening the connection:\" + ex.Message;\n }\n}\n",