Connect to SQL Server Database

Question

I recently started programming asp.net with C # (using VS2008), and I wrote my first web application that connects to a database. The first version worked fine, but now there are some problems when I modify it. The following are examples:

1) It works fine. The program connects to the database and uses the DeleteAllRecords() function to perform an action on it; It is important to note that I created the database for the connection in SQL Server Management Studio.

The code behind the button click event handler page:

  SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;Integrated Security=true"); try { dbConnection.Open(); dbConnection.ChangeDatabase("przemek8"); SqlCommand myCommand = new SqlCommand("DELETE FROM table8", dbConnection); myCommand.ExecuteNonQuery(); } catch (SqlException exception) { Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>"); } dbConnection.Close(); } 

2) the second time I did not use the database created in SQL SM Studio, but I added a new database item from Visual Studio itself (Website → Add New Item). I added some fields to this database, and I also set up a GridView to show the database that works. The problem, however, is that when I want to connect Gridview to a database created earlier in SQL SM Studio, it does not work - when setting up the connection that it won, you can select the database file by specifying:

You do not have permission to open this file. Contact the owner or administrator for permission.

It seems to me that the reason for this may be trivial, but I can’t figure it out.

Just note that all of these database files were created by SQL SM Studio in its default destination on drive C.

3) Unable to connect to the GridView with the database created by SQL Server, I continued to work with the database added by Visual Studio itself. He worked with GridView, so I used the function to interact with it (delete all records) - the same as at point 1), but with the database.

  SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS; AttachDbFilename='D:\\WebSite1\\App_Data\\mydtb.mdf'; Integrated Security=true; User Instance=true"); try { dbConnection.Open(); dbConnection.ChangeDatabase("mydtb"); SqlCommand myCommand = new SqlCommand("DELETE FROM Table1", dbConnection); myCommand.ExecuteNonQuery(); } catch (SqlException exception) { Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>"); } dbConnection.Close(); 

It does not connect to this, and the error message is:

Error code 911: The database 'mydtb' does not exist. Make sure the name is entered correctly.

I am new to this field, but should the data source in this case (connect to the database created in Visual Studio) Data Source=.\\SQLEXPRESS; how does this happen when a database is created in SQL Server Management Studio?

Thanks so much for any help and suggestions! asp.net excited beginner :-)

Answer 1

The problem with connecting to the database on the server is related to

 SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;Integrated Security=true"); 

The best way to do this is to go into your Web.config file and find the block and add the connection to your database there.

eg,

 <add name="ConnectionString" connectionString="Data Source=YOUR SERVER;Initial Catalog=YOUR DATABASE;User ID=YOUR USER ID;Password=YOUR PASSWORD" /> 

then you can just call the connection string throughout the project when you need to use it.

Also regarding VS2012. There are very few companies using this IDE at the moment, so you probably should study VS 2010 for the most part, but I would agree that VS2008 is quite outdated.

Answer 2

I think this will help

 SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;Integrated Security=true; initial catalog=database name; uid=servername ; password=yourpassword");