locked
Can't connect to database RRS feed

  • Question

  • User732704340 posted

    Hi,

    I just created a textbox, which I want to use to write data into a database but it doesn't work. I've tried everything but propably I'm still doing something wrong. This is my code:

    protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection.(string "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebUsersDB.mdf;Integrated Security=True;User Instance=True");
            connection.Open();
    
            string username = TextBox1.Text;
            string sqlquery = ("INSERT INTO Patients(RegisteredOnline) VALUES ('" + TextBox1.Text + "')");
            SqlCommand command = new SqlCommand(sqlquery, connection);
            command.Parameters.AddWithValue("RegisteredOnline", username);
            command.ExecuteNonQuery();
        }

    Thursday, April 28, 2011 9:24 AM

Answers

  • User-1828494216 posted

    Then don't attach the DB since it's already attached...

            protected void Button1_Click(object sender, EventArgs e)
    {
    string MyConnectionString = @"Data Source=.\SQLEXPRESS;Database=WebUsersDB;Integrated Security=SSPI";
    using (SqlConnection connection = new SqlConnection(MyConnectionString))
    {
    SqlCommand command = new SqlCommand("INSERT INTO Table1(1) VALUES (@MyParameter)", connection);
    command.Parameters.Add("@MyParameter", System.Data.SqlDbType.NChar, 10).Value = TextBox1.Text;
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
    }
    }

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 28, 2011 1:26 PM
  • User-1828494216 posted

    Hi again,

    i tried to do what u're trying to do (i've never used a local db since now hehe) and i found out it's actually a user access problem!

    The user that you are using to run the SQL Server service does not have access to your personal user folders. So if it's running under NETWORK SERVICE, grand that user access.

    working code (Database1.mdf is located in App_Data folder):

            protected void Button1_Click(object sender, EventArgs e)
            {
                string MyConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;User Instance=true;Integrated Security=True";
                using (SqlConnection connection = new SqlConnection(MyConnectionString))
                {
                    SqlCommand command = new SqlCommand("INSERT INTO Table1([1]) VALUES (@MyParameter)", connection);
                    command.Parameters.Add("@MyParameter", System.Data.SqlDbType.NChar, 10).Value = TextBox1.Text;
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 28, 2011 1:52 PM

All replies

  • User-1618234021 posted

    Hi

    Use the following:

     

    protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebUsersDB.mdf;Integrated Security=True");
            connection.Open();
    
           
            string sqlquery = "INSERT INTO Patients(RegisteredOnline) VALUES(@userName)";
            SqlCommand command = new SqlCommand(sqlquery, connection);
            command.Parameters.AddWithValue("@userName", TextBox1.Text);
            command.ExecuteNonQuery();
            connection.Open();
        }

    Thursday, April 28, 2011 9:41 AM
  • User-1828494216 posted

    Hi,

    first check that your connection string is valid, look here: http://www.connectionstrings.com/

    and the insert should look something like this (NOTE: serious security risk not using parameters ;) )

    string MyConnectionString = @"ur connection string from http://www.connectionstrings.com/";
                using (SqlConnection connection = new SqlConnection(MyConnectionString)
                {
                    SqlCommand command = new SqlCommand("INSERT INTO Patients(RegisteredOnline) VALUES (@MyParameter)", connection);
                    command.Parameters.Add("@MyParameter", System.Data.SqlDbType.NVarChar, 100).Value = TextBox1.Text;
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }

    Thursday, April 28, 2011 9:41 AM
  • User-1313990200 posted

    Hi what is the error you are getting??

     

    protected void Button1_Click(object sender, EventArgs e) 
        { 
            SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebUsersDB.mdf;Integrated Security=True"); 
            connection.Open(); 
     
            
            string sqlquery = "INSERT INTO Patients(RegisteredOnline) VALUES(@userName)"; 
            SqlCommand command = new SqlCommand(sqlquery, connection); 
            command.Parameters.AddWithValue("@userName", TextBox1.Text); 
            command.ExecuteNonQuery(); 
            connection.Open(); 
        }
    
    OR
    
    protected void Button1_Click(object sender, EventArgs e) 
        { 
            SqlConnection connection = new SqlConnection(\\"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebUsersDB.mdf;Integrated Security=True"); 
            connection.Open(); 
     
            
            string sqlquery = "INSERT INTO Patients(RegisteredOnline) VALUES(@userName)"; 
            SqlCommand command = new SqlCommand(sqlquery, connection); 
            command.Parameters.AddWithValue("@userName", TextBox1.Text); 
            command.ExecuteNonQuery(); 
            connection.Open(); 
        }
    Thursday, April 28, 2011 9:46 AM
  • User732704340 posted

    mitija.GTI you couldn'y confuse me more. There is hundreds of choices. It's my first time with databases and web developing as well so sorry but it didn't help me. It shows me this error. Maybe you know what can cause it. It displays line 18 in red.

     

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\Users\PC\Documents\Visual Studio 2010\WebSites\WebSite7\App_Data\WebUsersDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Source Error: 

    Line 16:     {
    Line 17:         SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WebUsersDB.mdf;Integrated Security=True");
    Line 18:         connection.Open();
    Line 19: 
    Line 20: 


    Source File: c:\Users\PC\Documents\Visual Studio 2010\WebSites\WebSite7\RegisterUsers.aspx.cs    Line: 18 

     

    Thursday, April 28, 2011 10:26 AM
  • User-1828494216 posted

    Hi,

    you're having trouble with connection string. It's wrong. The path can't be found or db is already attached.

    You need to modify: AttachDbFilename=|DataDirectory|\WebUsersDB.mdf I can't write the connection string for you since i don't know the path of ur mdf file ;) if it's in parent directory i think you should use AttachDbFilename=WebUsersDB.mdf

    look at this page: Connecting to a SQL Server Express Database

    You can also specify a database file to attach to by using the AttachDBFilename connection-string attribute in place of the InitialCatalog or Database connection-string attributes. Connecting to the database by using a file name simplifies deploying your database with your application (provided the target server is running SQL Server Express). For example, the following connection string connects to a database that is stored in the Customers.mdf file.

    Data Source=.\SQLEXPRESS;AttachDbFileName=e:\data\Customers.mdf;Integrated Security=True;User Instance=True
    ASP.NET provides a convenient option for storing data in the App_Data directory of a Web application. Contents of the App_Data directory are not served in response to Web requests, which improves the security of the data for your application. As an added convenience, you can supply the |DataDirectory| connection string variable in place of the file path to the App_Data directory for your application. ASP.NET features — such as the SqlDataSource control or the providers for membership, roles, user profiles, Web Parts personalization, and so on — will automatically substitute the file path to the App_Data directory for the |DataDirectory| connection-string variable when opening a connection to the database. This ensures that the path to your database remains current if your Web application is moved to a different directory. The following code example shows a connection string that includes the |DataDirectory| connection-string variable.

     

    Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Customers.mdf;Integrated Security=True;User Instance=True
    

     

    NOTE: also read this... you db might be already attached to ur Server ;) (second part of the error u're getting)

    You can close the connection held by Visual Web Developer by right-clicking the database in Solution Explorer and selecting the Detach option, or by right-clicking the database in Server Explorer and selecting Close Connection. Visual Web Developer will automatically close any open database connections when you run or debug your Web application.

    Additionally, if you need to release any open connections to a SQL Server Express database, you can unload your Web application by using Internet Information Services Manager (IIS Manager). You can also unload a Web application by adding an HTML file named App_offline.htm to the root directory of your Web application. To allow your Web application to start responding to Web requests again, simply remove the App_offline.htm file. You will need to release open connections to a SQL Server Express database when you want to copy or move the database to a new location.

    Thursday, April 28, 2011 10:48 AM
  • User732704340 posted

    I keep it in App_Data folder in the parent directory.

    Thursday, April 28, 2011 11:14 AM
  • User-1828494216 posted

    I keep it in App_Data folder in the parent directory.

     

    Then your DataBase file is already attached to the server.

    NOTE: also read this... you db might be already attached to ur Server ;) (second part of the error u're getting)

    You can close the connection held by Visual Web Developer by right-clicking the database in Solution Explorer and selecting the Detach option, or by right-clicking the database in Server Explorer and selecting Close Connection. Visual Web Developer will automatically close any open database connections when you run or debug your Web application.

    Additionally, if you need to release any open connections to a SQL Server Express database, you can unload your Web application by using Internet Information Services Manager (IIS Manager). You can also unload a Web application by adding an HTML file named App_offline.htm to the root directory of your Web application. To allow your Web application to start responding to Web requests again, simply remove the App_offline.htm file. You will need to release open connections to a SQL Server Express database when you want to copy or move the database to a new location.

    Thursday, April 28, 2011 11:20 AM
  • User732704340 posted

    Right, and what it supposed to do? I've closed the connection in server explorer but the problem still occured so I created the html file in the main directory and the connection closed but then when I click on my website and choose to open it in browser it doesn't open because the  connection is closed and when I reconnect them ther is the same problem again. It just doesn't make any sense.

    Thursday, April 28, 2011 1:03 PM
  • User-1828494216 posted

    Then don't attach the DB since it's already attached...

            protected void Button1_Click(object sender, EventArgs e)
    {
    string MyConnectionString = @"Data Source=.\SQLEXPRESS;Database=WebUsersDB;Integrated Security=SSPI";
    using (SqlConnection connection = new SqlConnection(MyConnectionString))
    {
    SqlCommand command = new SqlCommand("INSERT INTO Table1(1) VALUES (@MyParameter)", connection);
    command.Parameters.Add("@MyParameter", System.Data.SqlDbType.NChar, 10).Value = TextBox1.Text;
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
    }
    }

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 28, 2011 1:26 PM
  • User-1828494216 posted

    Hi again,

    i tried to do what u're trying to do (i've never used a local db since now hehe) and i found out it's actually a user access problem!

    The user that you are using to run the SQL Server service does not have access to your personal user folders. So if it's running under NETWORK SERVICE, grand that user access.

    working code (Database1.mdf is located in App_Data folder):

            protected void Button1_Click(object sender, EventArgs e)
            {
                string MyConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;User Instance=true;Integrated Security=True";
                using (SqlConnection connection = new SqlConnection(MyConnectionString))
                {
                    SqlCommand command = new SqlCommand("INSERT INTO Table1([1]) VALUES (@MyParameter)", connection);
                    command.Parameters.Add("@MyParameter", System.Data.SqlDbType.NChar, 10).Value = TextBox1.Text;
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 28, 2011 1:52 PM
  • User732704340 posted

    After many, many ... ,many hours ... It's working!!!!  Laughing

    mitja.GTI, you've done something that I've started to think is impossible!

    Thanks a lot for your effort.  Wink

    Thursday, April 28, 2011 4:38 PM
  • User-1828494216 posted

    Not a problem at all! I'm glad we cracked the nut :D

    Thursday, April 28, 2011 4:40 PM