asp.net c# connect to local Db vs2008

Question

I'm making an ASP.net with c# webapp using VS 2008, and I added a new sql database item to my project. I added tables to the databse. In the database explorer the test connection works. I guess I have two questions. One:In the application, how does one connect to the database using a connection string? or what connection string should I use? Second: How do I add a username and password to the database?

Right now I'm using this connection string in the web.config file, but when I run the app it times out and says it can't make a connection. The error is on the conn.open line.

add name="ReportsConnectionString" connectionString="Data Source=(local); Initial     Catalog=REPORTS;Integrated Security=True" providerName="System.Data.SqlClient"

I have this code in one of my page's codebehind.

string sqlquery = "SELECT * FROM reportitems";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReportsConnectionString"].ConnectionString))
    {
        conn.Open();
        using (SqlCommand comm = new SqlCommand(sqlquery, conn))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds, "reportitems");
                DataRowCollection dra = ds.Tables["reportitems"].Rows;

                foreach (DataRow dr in dra)
                {
                    string DRZ = dr[0].ToString();
                     //more stuff here
                 }
            }
         }
     }

Change Data Source=(local) to Data Sourcee=Localhost\SQLEXPRESS and see what happens — Shyju
– Shyju, Commented May 30, 2012 at 21:45
Ok, that stopped the timeout. Now it says Cannot open database "REPORTS" requested by the login. The login failed. — Jake Gaston
– Jake Gaston, Commented May 30, 2012 at 21:48
Cannot open database "REPORTS" requested by the login. The login failed. Login failed for user 'hp100\E101'. — Jake Gaston
– Jake Gaston, Commented May 30, 2012 at 21:50
You need to assign permission for that user on that database. Googling will give you so many results for sure. — Shyju
– Shyju, Commented May 30, 2012 at 21:52

Steve · Accepted Answer · 2012-05-30 22:02:03Z

3

Usually SqlServer Express is reachable on your local PC using this syntax for the Data Source parameter yourpcname\SQLEXPRESS. To be sure start Management Studio and look at the Server Name request.

For the security part of your question, I suppose that you don't want the Integrated Security option (Windows User), but you want a SQLServer user. In this case you could use the User ID and Password parameters for the connection string:

Data Source=MYPC\SQLEXPRESS;Initial Catalog=REPORTS;User Id=MYNAME;Password=MYPASS;

However, this works only after you have added this user to the SQLServer. You could use the interface of Management Studio app or you could execute a script like this

USE [master]
GO
CREATE LOGIN [MYNAME] WITH PASSWORD=N'MYPASS', DEFAULT_DATABASE=[master]
GO
USE [REPORTS]
GO
CREATE USER [MYNAME] FOR LOGIN [MYNAME]
GO

edited May 30, 2012 at 22:02

answered May 30, 2012 at 21:54

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jake Gaston Over a year ago

If I'm putting this on a server in the future, I assume the MYPC part will be the name of the server?

Steve Over a year ago

Yes, get in the habit now so you will not forget in the future :-)

madsny · Accepted Answer · 2012-05-30 21:51:58Z

1

The Integrated Security=True part of the connectionstring means that the server will use the credentials of the app pool running the site, and you don't need to specify username or password. The app pool identiy will, however, need to have access to your database.

answered May 30, 2012 at 21:51

madsny

1116 bronze badges

Comments

robrich · Accepted Answer · 2012-05-30 21:54:36Z

1

Visit http://www.connectionstrings.com/ for a good primer on various ways to set the connection string for various applications. That'll show you why (local) didn't work but .\SQLEXPRESS did and how to add username and password to it. Here's an example lifted from http://www.connectionstrings.com/sql-server-2008

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

answered May 30, 2012 at 21:54

robrich

13.3k7 gold badges39 silver badges63 bronze badges

Comments

Edgar Hernandez · Accepted Answer · 2012-05-30 22:18:21Z

As others have said, you need a SqlExpress engine running as .mdf is not a flat file. It is a SQL server express database file and you need to connect to it.

But what have not said is that a Database in your App_Data folder needs to be attached to the SqlServer instance. This step is only done once in the first connection.

In http://www.connectionstrings.com/sql-server-2008 you will find an example in the "Attach a database file, located in the data directory, on connect to a local SQL Server Express instance" section that looks like this:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Also you can read this: http://msdn.microsoft.com/en-us/library/ms247257.aspx

I believe that you will need to run some scripts and stuff like that to create a user and assign permissions to this user in this database, and then change the connection string (once the database attached), so I don't see a point in having the database in the App_Data folder. I believe it should be better if since the beginning you create your database using the SqlServer tools and connect to it from your application.

Collectives™ on Stack Overflow

asp.net c# connect to local Db vs2008

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related