Connecting to SQL Server Express from C#

Question

I'm trying to connect to a SQL Server database from C#, but keep get thrown by an exception:

System.Data.SqlClient.SqlExceptionwhen trying toopen()` the connection.

I have created a simple web form, with two text boxes: usernameTextBox, and passwordTextBox, and a 'sign in' button.

For now, just for testing purposes, what I'm trying to do is to insert values into a users table, when the sign in button is clicked, so this is what I have so far:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _default : System.Web.UI.Page
{
    protected void signInButton_Click(object sender, EventArgs e)
    {
        string connection_str="Data Source=.\\SQLEXPRESS;"+
        "AttachDbFilename=|DataDirectory|\\project.mdf;"+
        "Integrated Security=True;"+
        "User Instance=True";

        SqlConnection connection = new SqlConnection(connection_str);
        connection.Open();

        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText="INSERT INTO users VALUES ('"+userNameTextBox.Text+"','"+passwordTextBox.Text+"');";
        command.ExecuteNonQuery();
        connection.Close();
    }
}

As stated above, this fails at connection.Open();.

The project.mdf file is located at the VS Project folder, inside the 'App_Data' folder.

Does anyone have an idea about what can cause the Open() to fail?

I can also provide the 'exception info', if it is needed.

Thanks.

BTW, When should the connection be established? at the 'sign-in-button handler', or in the 'Page_Load' handler?

EDIT

System.Data.SqlClient.SqlException was unhandled by user code
  HResult=-2146232060
  Message=Cannot open user default database. Login failed.
Login failed for user 'J***-PC\J***'.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=11
  LineNumber=65536
  Number=4064
  Procedure=""
  Server=\\.\pipe\8EDE82F8-E3AA-40\tsql\query
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at _default.signInButton_Click(Object sender, EventArgs e) in c:\Users\J***\Documents\Visual Studio 2012\Projects\db5774\default.aspx.cs:line 23
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: 

Answer 1

Try this:

  AttachDbFilename=~\..\ProjectName\App_Data\project.mdf

Answer 2

So the exception says:

Message=Cannot open user default database. Login failed. Login failed for user 'J***-PC\J***'.

That would mean the user has no access to the default database, usually master.

Alter the user so it can access the default database, or better, point the default to another database.

Use this sql for that:

alter login [username] with default_database = database_that_user_can_access

Or change the connection string by adding this:

Database=database_that_user_can_access