First of all, you aren't crazy or a no brained soul. This is not you. This is quirkiness in how Microsoft (and I am their biggest fan), in trying to make things easier, has somehow made CHANGES to your SQL environment difficult. I am working on sorting this out and creating some kind of a flow chart to troubleshoot all of this, but here are some insights in the meantime (Gurus - please correct me where I am wrong on any of this):
* ASPNETDB.MDF seems to occur for every ASP.NET application and should appear in the |DataDirectory| - which is normally(?/automatically?) APP_DATA
* I think it gets automatically created the first time you run your app (because it seems to me to not be there, then suddenly appear) but it may be under program control - I don't know
* if you have an instance that has been named ASPNETDB or ASPNETDB.MDF in your Sql database, connecting a new one will fail as a duplicate, so use Sql Management Studio to rename any such instances to something unique, like "c:\Webs\PartnerPortal\Northwind Traders\App_Data\ASPNETDB.MDF" instead of just "ASPNETDB.MDF"
* In my environment I have several named instances of Sql Server, including: "STARTEAM" (a Borland created named instance), "OFFICESERVER" "SQLEXPRESS" "MSSQLSERVER" and "MICROSOFT##SSEE" (Mobile edition). However, none of these are useful to me. I have uninstalled SQLSERVER (SQL 2000) and SQLEXPRESS. The one I NEED does not appear - which is the unnamed instance - SQL Enterprise 2005. To access that all I do is enter the server name, ip or .\
* I was using Microsoft Expression Web to edit the default connection string (and this is identical in Server Explorer in VS 2005 also). It asks you the provider. I was using the defaul and could not enter .\ as my Sql Server. I only had the other useless named instances and could not successfully connect. SOOOO I changed the provider. I had two choices:
"Microsoft Sql Server" and "Microsoft Sql Server Database File" (along with "Sql Server Mobile Editiion", etc).
* I changed my selection FROM "Microsoft Sql Server" TO "Microsoft Sql Server Database File". This allowed me to edit the server to be .\ when I went back to my connection string
* Then I changed the provider BACK and now I had the choice I needed! I think that the choice are coming from the registry and it got out of sync
Anyway, now I can connect. I think that connectionString updates the registry, where the problems lie.
*************************************************
Here is the skinny on troubleshooting connections:
*************************************************
(1) have you run aspnet_regsql from a command prompt at the c:\windows\Microsoft.net Framework v2xxx directory?
(2) did you create a virtual directory for your app and enable Asp?
(I have a separate memory pool for each version of Asp - another matter)
(3) did you change the Asp settings to the right connection string? The important things are: .\SQLEXPRESS or .\ or your named instance. If you do not use SQLEXPRESS then User Instance must be False
(4) are all of your instances of ASPNETDB.mdf named uniquely? (Ie: include a path)
(5) does IIS_WPG have sufficient rights to your web directory (this may not be true if you put your webs in your "My Documents")
(6) In VS2005, did you go to Websited-Asp config?
(7) Do you have the right settings in your Web.config?
(8) You must not have extraneous copies of Web.config on your path - they will be automatically referenced in the project. Ie, creating a backup in a subdir, is trouble.
(9) did you set your Default.aspx as the Startup page? (and the right project as the startup project if you have multiple projects?)
(10) did you use Server Explorer to try a different provider, update the registry choices of servers and to include your unnamed instance, per above?
Does Machine.config figure into this?
What have I missed or misstated?
To be honest, having done all of this, I still seem to have a web that I am still troubleshooting the connection for!
****
If anyone can correct, add or improve on the above, please do. What i would love to see is a Visio flowchart in a .pdf that could be emblazoned here. I can be reached at my gmail account of norisksoftware at gmail . com
AND I am looking for contract work doing almost anything technical (web, C#, consulting, Cobol, EDI..) that pays well. I was out of the country for 2 1/2 years and my business kind of dried up. Go figure.
Bill Ross