Remove From My Forums

I will not give up (connection to ASPNETDB)

Question
0

Sign in to vote

User380413204 posted

Hi all,

I have some (forum)pages that "look" to a seperate database, every reference to it in the pages looks like this:

Dim myConnection as New oleDBConnection(ConfigurationSettings.AppSettings("forumDSN"))

In my web.config the following key exitst:

<appSettings>

<add key="forumDSN" value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Database=dbname;Trusted_Connection=Yes;Language=Dutch" />

</appSettings>

This all works well, but

Now this is what I am trying to do:

Move the tables to ASPNETDB.MDF (not to difficult)

But here comes the part that is driving me mad!

Change the key in web.config so that the aspx.pages actually "see" the tables in ASPNET.MDF

I am still trying to get this done, any ideas will be highly appreciated.

Lex

Tuesday, June 6, 2006 5:43 AM

All replies

0

Sign in to vote

User-1614457691 posted

...

In my web.config the following key exitst:

<appSettings>

<add key="forumDSN" value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Database=dbname;Trusted_Connection=Yes;Language=Dutch" />

</appSettings>

This all works well, but

Now this is what I am trying to do:

Move the tables to ASPNETDB.MDF (not to difficult)

But here comes the part that is driving me mad!

Change the key in web.config so that the aspx.pages actually "see" the tables in ASPNET.MDF

...

I get the impression that you are somewhat less than happy.

:-)

Ug. I feel your pain. Or, rather, I feel pain of my own that seems to be something like yours. I have seen more and more questions like this regarding SQL Express. SQL Express is a VERY nice product and I do like it a lot. However, it is not exactly plug-and-play. I fear their design approach was a bit off due to their insistence on using IntegratedSecurity, UserInstance, AttachDBFilename. I really wish they would have provided a simple username/password alternative, (like Access did), for connecting to an MDF. Oh well. You are not alone. I continue to wrestle with this sort of thing too (even after setting up 5 projects), and many others are facing the same issues.

Here are some thoughts that may help.

I think that your new key should look something like this...

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|\WlaASPNET200605142000.MDF;Initial Catalog=WlaASPNET200605142000" providerName="System.Data.SqlClient" />

...because that's what I am doing on 5 different sites and it works fine.

Of course, change the attribute value for the name attribute, change the MDF file name, and change the Initial Catalog to fit your application.

Delete the other key because you do not need it and you want the code to FailFast if that other key is accessed.

A few things to note, which I found helpful...

Before making changes, detach the database.

Before moving the database, detach the database.

If you move the database, move just the MDF files not the LDF files.

Grant ReadWrite permissions to the App_Data folder for the following accounts: ASPNET, NETWORK SERVICE, IUSR_LocalMachineName.

If things go awry, detach all databases, delete the LDF files, rename the database, and start over. (I know that is heavy handed; but, when all else fails, use good old Brute-Force, the miracle worker.)

HTH.

Thank you.

--Mark Kamoski

Tuesday, June 6, 2006 7:50 AM
0

Sign in to vote

User380413204 posted

Hi mark,

Thanks for your response and your empathy ([:)]

Tried to transpose your string.

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|\WlaASPNET200605142000.MDF;Initial Catalog=WlaASPNET200605142000" providerName="System.Data.SqlClient" />

When I use <add name

I get:

Parser Error Message: Unrecognized attribute 'name'. Note that attribute names are case-sensitive

When I change it to

<add key

Parser Error Message: Unrecognized attribute 'connectionString'. Note that attribute names are case-sensitive.

I must admit I haven't got a clue where to find info about Initial Catalog.

But in the process I came up with yet another new message with my old striing:

<add key="forumDSN" value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|ASPNET.MDF;Trusted_Connection=Yes;Language=Dutch" />

Now it says:

Server Error in '/Webs' Application.

An attempt to attach an auto-named database for file C:\Webs\App_Data\ASPNET.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Perhaps that can be some sort of a clue?

Anyway thanks,

Lex

Tuesday, June 6, 2006 8:43 AM
0

Sign in to vote

User1275632693 posted

Lex,

I know the feeling, here is something I found as well.
Attach a database file on connect to a local SQL Server Express instance:

"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
- or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
(use |DataDirectory| when your database file resides in the data directory)
Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).
Using "User Instance" on a local SQL Server Express instance:

"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"
The "User Instance" functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)

Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)

Tuesday, June 6, 2006 10:06 AM

User1275632693 posted

Lex,

Here is another good read:

Building Connection Strings

In ADO.NET, connection strings are name/value pairs, as with other generic APIs. Unlike OLE DB and ODBC, ADO.NET does not mandate what those name keywords should be, though it is suggested that they follow the OLE DB conventions. The OleDb and Odbc bridge data providers each follow their own convention. The Microsoft OracleClient follows OLE DB convention and SqlClient supports either keyword when the OLE DB and ODBC keywords differ. Here are some examples:

Table 5. Connection string names and values in different data providers

Meaning	Odbc	OleDb	SqlClient	OracleClient
Source to connect to	Server	Data Source	Server or Data Source	Server or Data Source
User	UID	User ID	UID or User ID	User ID
Password	PWD	Password	PWD or Password	Password
Is a Windows login used?	Trusted_Connection	Integrated Security	Trusted_Connection or Integrated Security	Integrated Security
Database to connect to	Database	Initial Catalog	Database or Initial Catalog	N/A
Connection Pooling		OLE DB Services	Pooling	Pooling

The DbConnectionStringBuilder is a base class for provider-specific connection string builders. Because it cannot be guaranteed that an ADO.NET data provider supports a specific connection string parameter, the DbConnectionStringBuilder just keeps a dictionary of name/value pairs. All of the ordinary collection methods are available, including a GetKeys and GetValues method to get the entire list from the dictionary. DbConnectionStringBuilder does support specific properties for connection string parameters, such as ShouldSerialize (the user may not want the password value serialized into a file, for example). You can get an instance of the generic DbConnectionStringBuilder through the DbProviderFactory class. Specific ConnectionStringBuilder classes have convenience properties, should as a DataSource property that refers to the database server to connect to. In general, the properties are data provider-specific, as are the keywords.

The Initial Catalog is simply the Database name you wish to use.

Tuesday, June 6, 2006 10:17 AM

User1275632693 posted

Lex.

Take a look at this thread as well:

http://forums.asp.net/1242411/ShowThread.aspx

I do believe they had the same type of issue with a different starter kit and resolved it.

One thing though, when you look at your actual SQL 2005 Express Database instance, does it already contain a database called ASPNET, if so this may well be the problem.

Tuesday, June 6, 2006 10:40 AM

User380413204 posted

Thanks guys,

One of the things I encounter with these examples is that almost always they hold:

<add name="ConnectionString"

When I take over the syntax (for trial and error [:)] ) it gets me into errors because it's about a key. <add key="forumDSN" It seems I can not use<add name in App Settings

One time I even moved the entire string to where the Connection string for ASPNETDB resides, (so I could use <add name="ConnectionString" , but then I get Connection string is not Initialized as soon I as I click the Forum link.

Thanks,

Lex

Tuesday, June 6, 2006 11:07 AM

User1275632693 posted

Lex,

Try using this tool:

Product Highlight: Microsoft is Providing a Free Tool Called SSEUtil

Are you looking for a lightweight and easy-to-use tool to help you manage SQL Server Express 2005, but you don’t want to wait for the SQL Server Management Studio for Express? Well, look no further.

Microsoft is providing a free SQL Server Express Utility—SSEUtil. This downloadable tool lets you list all installed instances of SQL Server Express; connect to an instance; and create, attach, and detach databases as well as execute SQL statements. Check out SSEUtil at:
http://www.microsoft.com/downloads/details.aspx?familyid=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en

I found a great page on this specific issue:

http://www.sqlmag.com/Article/ArticleID/49623/sql_server_49623.html

I will try to recreate the same scenario you have as I do think that the ASPNETDB file should be maintained seperatly for each application. So I suppose in the end I have a vested interest in getting this resolved as well.

Tuesday, June 6, 2006 12:37 PM

User380413204 posted

I will, thanks Angelo

But in the meantime I am mesmerised by something peculiar. And what I feel in the meantime confirms that the problem lies not in the connectionstrings themselves.

I feel the problem lies somewhere in instances of the databases. This is what happens:

I can connect to the Forumtables. But I have to do all sorts of things in the right order. By the way when after a succesful connection I click on any other link on the Masterpage I get Error:

Cannot open user default database. Login failed.
Login failed for user 'P4-3000\Lex'.

These are the things that I have to look out for:

In Management Studio the Database ASPNETDB must be detached
<add key="forumDSN" value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Database=ASPNETDB.MDF;Trusted_Connection=yes;Language=Dutch" />

<add key="forumDSN" value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Trusted_Connection=yes;Language=Dutch" />
One of the two strings work
I must not start the app. but I must use refresh in the BrowserWindow (F5)

I know it's silly all this, but at least it proves a connection can be made.

Now if I only knew where to start solving the rest [:)]

Regards,

Lex

Tuesday, June 6, 2006 2:01 PM

User1275632693 posted

Lex,

All this is happening on your local box at home right?

Tuesday, June 6, 2006 2:51 PM

User380413204 posted

Yes,

Imagine the tension when finally all goes well locally, and then the first time for real on my server....

Brrrr....

Tuesday, June 6, 2006 3:33 PM

User1275632693 posted

In English:

Don't worry, drink a beer and it will all be better

In Dutch:

Me maak niet, een bier ongerust drinken en het allen zal beter zijn

now if life was as easy as clicking an online translation button, i bet the one above reads really funny in Dutch or is that ( West Flemish, East Flemish, Brabantian,Limburgish, Low German,Zealandic or Hollandic ) of course there is room for a few more but I left them out.

Tuesday, June 6, 2006 4:36 PM

User654902800 posted
My 2 cents is that you'd be better off in the long run getting rid of the oleDB stuff and replacing it with SQLConnection so it looks and works like the rest of the site and uses the same connection strings and so on.

Tuesday, June 6, 2006 5:05 PM

User380413204 posted

Ha,

I'm sure everybody would understand what you mean by those words, or at least they will give you a beer.

True words though ( 2 beers perhaps?)

Lex

N.B.

It should be something like this:

Maak je niet ongerust, drink een biertje en alles zal beter gaan..

(now if those connnectionstrings would be that easy)

Tuesday, June 6, 2006 5:45 PM

User1275632693 posted

We shall get that working for you, no worries in the end no young child shall die if we don't get the connection strings working.

Albeit I bet you are pulling more and more hair out

I know the feeling.

Tuesday, June 6, 2006 6:03 PM

Asked by:

I will not give up (connection to ASPNETDB)

Question

All replies

Server Error in '/Webs' Application.

An attempt to attach an auto-named database for file C:\Webs\App_Data\ASPNET.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Building Connection Strings

In English:

In Dutch: