Difficulty in linking SSMD created database into VWD

cuddling101 · August 8th, 2010, 09:20 AM

Dear Imar

I have tried to follow through all the points in Chapter 19 and Appendix B of your Beginning ASP.NET 4 in C# and VB but I am clearly missing some crucial point/s.

Background

1. I have created the database I want to use directly through SQL Server Management Studio (SSMD).
2. It is on an SQL instance named MSSQLSERVER.
3. My machine name is PIP-HP-PC.
4. The database name is How_and_Why_Wonder_Books.
5. I use Windows Authentication.
6. My web application is in the directory C:\How_and_Why_Wonder_Books_ASPNET

Text of my web.config file

Code:

<configuration>
  <connectionStrings>
    <add name="How_and_Why_Wonder_BooksConnectionString1" connectionString="Data Source=.\MSSQLSERVER\;Initial Catalog=How_and_Why_Wonder_Books; Integrated Security=True; Connect Timeout = 30;"
         providerName="System.Data.SqlClient"
    />
    <add name="How_and_Why_Wonder_BooksEntities" connectionString="metadata=res://*/App_Code.How_and_Why_Wonder_Books.csdl|res://*/App_Code.How_and_Why_Wonder_Books.ssdl:res://*/App_Code.How_and_Why_Wonder_Books.msl;provider=System.Data.SqlClient;provider connectionstring=&quot;Data Source=.\MSSQLSERVER;Initial Catalog=How_and_Why_Wonder_Books;Trusted_Connection=True;MultipleActiveResultsSets=True&quot;"
         providerName="System.Data.EntityClient"
    />
    <add name="LocalSqlServer" connectionString="DataSource=.\MSSQLSERVER;Initial Catalog=aspnetdb;Trusted_Connection=True"
         providerName="System.Data.SqlClient"
    />
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

  <system.web>
    <compilation debug="false" strict="false" explicit="true" targetFramework="4.0" />

    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login.aspx" timeout="2880" />
    </authentication>

    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
             enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
             maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
             applicationName="/" />
      </providers>
    </membership>

    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
      </providers>
    </profile>

    <roleManager enabled="false">
      <providers>
        <clear/>
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" />
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
      </providers>
    </roleManager>

  </system.web>

  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
</configuration>

I had thought that having done all that I would see my database when I go in to Database Explorer within VWD, but all I see is the aspnetdb file, which it is expecting to find at C:\HOW_AND_WHY_WONDER_BOOKS_ASPNET\SITE\APP_DATA\A SPNETDB.MDF.

I know that I am not doing things exactly as you set out basically, but I am trying to follow through the instructions you direct the reader to from page 357 of your 3.5 book, and page 407 of your 4.0 book, and I am in tears here with the frustration. Please ask for any files, extract of code etc. that you need, but please help.

I can see and manipulate the database in SSMD, Iâm just missing the correct link to VWD.

I look forward to your advice.

Yours sincerely

Philip Hunt
Medina, Kwinana
Perth, Western Australia
August 8th, 2010

Imar · August 9th, 2010, 03:16 AM

Hi Philip,

You're code is redefining LocalSqlServer which is not allowed because it's already defined in global config files. Try adding a clear element:

Code:

<connectionStrings>
  <clear />
  .. Connection strings go here
</connectionStrings>

Your connections may not automatically appear in the Database / Server Explorer. Have you tried adding them yourself using the Connect to Database button on its toolbar?

Imar

cuddling101 · August 9th, 2010, 08:41 AM

Dear Imar

Sorry about the <clear />; I had typed it in but somehow didn't save it properly.

I feel I am getting tantalisingly close but just not there yet. I have been trying the Connect to Database options within VWD Database Explorer, but have hit some problems.

Firstly there were a number of options, the two 'relevant' to my situation appeared to be Microsoft SQL Server or Microsoft SQL Server database file.

I first tried the latter, which led me to review a lot of Chapter 19 again and make some relevant additions to what I had done previously - I must admit that am losing track a bit of what I did before on the XP machine, and now have to redo here on the Windows 7 machine

, but I think I have that sorted out now, after double checking things this evening.

At the end though, when I had browsed to the How_and_Why_Wonder_Books.mdf file, it said that it was already in use by another program I suppose I was not wholly surprised as I knew it was attached to MSSQLSERVER and therefore would probably not want to be attached to SQLEXPRESS as well.

My problem is that when I try the first option of Microsoft SQL Server, I am not presented MSSQLEXPRESS as an option in Server Name; I am only offered SQLEXPRESS.

I suspect this may be something to do with aspnetdb. I have read a lot of the bits about that, but am just a little lost.

I do seem to recall reading something about copying the aspnetdb file, but now I can't find it again. On the other hand there is the bit at the end of Appendix B that describes how to build a new aspnetdb. I am just not too sure what I should be doing at this stage when all I am trying to do is enable my development website to look at the database file on MSSQLSERVER.

My mdf file turns out to be sitting at the end of a quite long tree -

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

I look forward to your further guidance.

With thanks in anticipation.

Philip

Imar · August 9th, 2010, 09:34 AM

Quote:

I first tried the latter, which led me to review a lot of Chapter 19 again and make some relevant additions to what I had done previously

If the database is already attached to SQL Server, don't access it here as a file; SQL Server has a lock on the file so that won't work. Also, don't store databases attached to SQL Server in the App_Data folder. I typically use App_Data for database files I attach on the fly only. Instead, create a folder such as C:\Databases, save your mdf there, and use that file to attach it to SQL Server using SSMS.

Quote:

My problem is that when I try the first option of Microsoft SQL Server, I am not presented MSSQLEXPRESS as an option in Server Name; I am only offered SQLEXPRESS.

Just type it in. First, make sure that "Microsoft SQL Server (SqlClient)" is selected under Data Source. Then in Server name, simply type .\MSSQLEXPRESS. Once you do that, your attached databases should appear in the Select or enter a database name drop down. If you get an error, then either .\MSSQLEXPRESS is not running, .\MSSQLEXPRESS does not exist, or there's a permission problem.

Quote:

I am only offered SQLEXPRESS.

If you have Express, why go through all those troubles getting it to run under MSSQLEXPRESS?

Cheers,

Imar

cuddling101 · August 10th, 2010, 01:48 AM

Quote:

Originally Posted by Imar

Then in Server name, simply type .\MSSQLEXPRESS. Once you do that, your attached databases should appear in the Select or enter a database name drop down. If you get an error, then either .\MSSQLEXPRESS is not running, .\MSSQLEXPRESS does not exist, or there's a permission problem.

I have typed in .\MSSQLEXPRESS, and it cannot see the database files. How do I tell if MSSQLEXPRESS is running? I believe that is what the database instance is called - it appeared to be what came up under SSMS. Where would I look in SSMS to be 1000% certain?

Quote:

Originally Posted by Imar

If you have Express, why go through all those troubles getting it to run under MSSQLEXPRESS?

I originally designed all my data into an Access database and had stored quite a lot of stuff there before I bought your 3.5 book, which directed me to use SQL Server. After 9 months of trial and tribulation and the purchase of a new computer, running under Windows 7, along the way, I was finally able to copy my Access data into an SQL Server database using the SSMS interface. MSSQLEXPRESS is where SSMS chose to put the database.

Bottom line - I still can't get at my database. I have followed through all your permission setting instructions.

Imar · August 10th, 2010, 02:12 AM

Quote:

I have typed in .\MSSQLEXPRESS, and it cannot see the database files. How do I tell if MSSQLEXPRESS is running? I believe that is what the database instance is called - it appeared to be what came up under SSMS. Where would I look in SSMS to be 1000% certain?

You need to look at the Login screen of SSMS. What do you enter (or is already there) in the server name box of the Connect to Server dialog that appears when you start SSMS? Also, when you're logged in you should see the full server name in the Object Explorer (at the top of the tree; it's followed by the version number).

And have you tried accessing .\SqlExpress instead?

Quote:

MSSQLEXPRESS is where SSMS chose to put the database.

I don't think Access installs SQL Express, so there must have been some installation already. Maybe it *is* called .\SqlExpress after all, and Access just created an alias for it?
Check out the SQL Server Configuration option on the start menu to learn more about various installations, instances and aliases of SQL Server.

Cheers,

Imar

cuddling101 · August 10th, 2010, 03:33 AM

Quote:

Originally Posted by Imar

You need to look at the Login screen of SSMS. What do you enter (or is already there) in the server name box of the Connect to Server dialog that appears when you start SSMS?

The Connect to Server dialogue box reads:

Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication
User name: Pip-HP-PC\Pip {greyed out line}

Quote:

Originally Posted by Imar

Also, when you're logged in you should see the full server name in the Object Explorer (at the top of the tree; it's followed by the version number).

The top line in the Object Explorer reads:

(local) (SQL Server10.50.1600 - Pip-HP-PC\Pip)

When right clicking on that and looking at its Properties, the value beside the name property reads:

PIP-HP-PC

and the value beside root directory reads

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

I cannot recall just now where I picked up the 'concept' MSSQLSERVER, but I do seem to recall it came up somewhere when I was building the database within SSMS, after having copied and pasted the data from Access.

Quote:

Originally Posted by Imar

I don't think Access installs SQL Express, so there must have been some installation already. Maybe it *is* called .\SqlExpress after all, and Access just created an alias for it?

Please know that I did not use Access directly to transfer or load the SQL Server database, as that also proved just way too difficult to implement. As the number of records was low, even though there were quite a few tables, I simply used cut and paste, in the end, to transfer the data from Access to SQL Server, having already defined the table structure using SSMS. Access didn't do anything to, or have any interaction directly with, SQL Server.

Re SQL Express: I have been burnt already down that track and am not willing to go there again. This whole episode, now over 9 months in running, started with me being unable to get VWD, on my old XP machine, to see the sample database provided for your 3.5 book against the Try It Out on page 356 of that book. You gave me quite some ideas at that time but in the end I just gave up, because nothing would work, SQL Server database wise, that is. After about a 4 or 5 months hiatus I bought a new computer, running under Windows 7, but have still now got back to the same stalemate point.

Imar · August 10th, 2010, 03:46 AM

Quote:

Server name: (local)

In that case, there is no SQL server instance.

So, whereever you tried .\MSSQLEXPRESS simply type a dot (.) or use (local).

Imar

cuddling101 · August 10th, 2010, 10:45 AM

Dear Imar

Thank you so very much. I did not make up the MSSQLSERVER bit, as you can see that is part of the directory name, but obviously I got misled by some message that referred to that name, in a way that made me think it was the server instance.

I have just used the dot (.) and now can see my database tables.

Now all I have to do is follow your book to do the queries etc.

Thank you for your patience.

Best regards

Philip

Imar · August 10th, 2010, 10:51 AM

Quote:

I did not make up the MSSQLSERVER bit

I know.... It can be confusing sometimes.... ;-)

Imar