locked
Unable to open phsyical file .. being used by another process .. or it is located on UNC share RRS feed

  • Question

  • User1389508859 posted

    I am getting the following error message when I run a sample program from my textbook. I traced it to the location marked below via DEBUGGING, however this is the only dbConnection.Open() statement existing within the project.

    ERROR: Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Bakery\App_Data\Bakery.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
    An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Bakery\App_Data\Bakery.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    CODE:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Collections;
    using System.Data.SqlClient;
    
    /// <summary>
    /// Generic shopping cart class
    /// </summary>
    public class ShoppingCart
    {
        private SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Bakery\\App_Data\\Bakery.mdf'; Integrated Security=True; User Instance=True");
    
        private ArrayList productID = new ArrayList();
        private ArrayList productQuantity = new ArrayList();
        private ArrayList productTable = new ArrayList();
    
        public bool addItem(string prodID, string table)
        {
            foreach (string item in productID)
            {
                if (item == prodID)
                    return false;
            }
    
            productID.Add(prodID);
            productQuantity.Add(1);
            productTable.Add(table);
            return true;
        }
    
        public string showCart()
        {
            string retValue = "<table width='100%' cellspacing='2' cellpadding='3' rules-'all' border='1' id='ProductGrid' style-'backgroun=color:#DEBA84;" +
                "border-color:#DEB84;border-width:1px;border-style:None;font-size:Samller;'>";
            retValue += "<tr style='color:White; background-color:#A55129;font-weight:bold;'><th align='center'>Product</th><th align='center'>" +
                "Quantity</th><th align='center'>Price Each</th></tr>";
    
            double total = 0;
    
            for (int i = 0; i < productID.Count; ++i)
            {
                string sqlString = "SELECT * FROM " + productTable[i] + " WHERE productID='" + productID[i] + "'";
                SqlCommand prodCommand = new SqlCommand(sqlString, dbConnection);
                SqlDataReader prodRecords = prodCommand.ExecuteReader();
    
                if (prodRecords.Read())
                {
                    retValue += "<tr style='color:#8C4510;background-color:#FFF7E7;'>" 
                        + "<td>" + prodRecords["name"] + "</td>" 
                        + "<td align='center'>" + productQuantity[i] + "</td>"
                        + "<td align='center'>" + String.Format("{0:C}", prodRecords["price"]) + "</td></tr>";
    
                    double price = Convert.ToDouble(prodRecords["price"]);
                    int quantity = Convert.ToInt16(productQuantity[i]);
    
                    total += price * quantity;
                }
    
                prodRecords.Close();
            }
    
            retValue += "<td align='center' colspan='2'> <strong>Your shopping cart contains " + productQuantity.Count + " product(s).</strong></td>";
            retValue += "<td align='center' <strong>Total: " + String.Format("{0:C}", total) + "</strong></td></tr>";
            retValue += "<asp:Button runat='server' Text='Button' /></table>";
    
            return retValue;
        }
    
        public ShoppingCart()
    	{
            /* Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Bakery\App_Data\Bakery.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
    An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Bakery\App_Data\Bakery.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. */
           dbConnection.Open(); // ERROR
    	}
        // Deconstructor method, p548
        ~ShoppingCart()
        {
            dbConnection.Close();
        }


    Ideas for why this could be happening? I'm sure I could move on to the assignment, however I'd feel better if this program worked properly.

    Thanks in advance for any advice.

    Monday, October 21, 2013 10:25 PM

All replies

  • User2103319870 posted

    Hi,

    Try changing your connection string from 

    private SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Bakery\\App_Data\\Bakery.mdf'; Integrated Security=True; User Instance=True");

    to

    private SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Bakery\\App_Data\\Bakery.mdf'; Integrated Security=SSPI; User Instance=True");


    The error you're getting is because VS generated a User Instance connection string. A User Instance is a separate instance of SQL Express that is started at runtime which tries to attach the database specified for use by the application. Your database is already attached to the parent instance of SQL Express, and databases can only be attached to a single instance at a time. By changing the connection string, you'll be using the parent instance, where the database is already attached, rather than the User Instance.

    Monday, October 21, 2013 11:55 PM
  • User1389508859 posted

    Hi,
    Try changing your connection string from 
    private SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Bakery\\App_Data\\Bakery.mdf'; Integrated Security=True; User Instance=True");

    to

    private SqlConnection dbConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Bakery\\App_Data\\Bakery.mdf'; Integrated Security=SSPI; User Instance=True");


    The error you're getting is because VS generated a User Instance connection string. A User Instance is a separate instance of SQL Express that is started at runtime which tries to attach the database specified for use by the application. Your database is already attached to the parent instance of SQL Express, and databases can only be attached to a single instance at a time. By changing the connection string, you'll be using the parent instance, where the database is already attached, rather than the User Instance.

     

    Thanks, A2H.

    I Googled this quite a bit last night so I've heard the User Instance thing, although I have not seen the change that you recommended. I will give that a try and let you know, might not get a chance to until tomorrow though.

    I appreciate the reply. I hope it works!

    Tuesday, October 22, 2013 8:32 AM
  • User1389508859 posted

    Semi-worked .. I got a time-out error this time (debug showed same location, at dbConnection.Open()).

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Referring to THIS, I attached my database to SQL Server Mgt Studio and ran the following statement: exec sp_updatestats. It found some updates, and after running the site again I was back to my previous error mentioned in the first post.

    Wednesday, October 23, 2013 8:36 AM