SQL Server Express 2008 sharing problem

I assume the connection string contains something like "attachfilename=etc", right?
instead, you should NOT use that, but keep the database attached, and just connect to the server.

Thanks for your help!

The Web.Config file statement is:

<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=c:\SpotNote\DB\SpotNote.mdf;User Instance=true" providerName="System.Data.SqlClient"/>

The Connection statement from my WinForms application that accesses the same database is:

DSN = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SpotNote\DB\SpotNote.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"

HBH

that's what I thought: the attachdbfilename part.

so, instead, attach the db permanently to the sql server instance, and connect
by replacing the attachdbfilename=...; part by this:
database=spotnote;

(assuming that you attach the db with that name).
to attach the db permamently, use the stored procedure sp_attachdb

In addition to what Angel said, make sure you have the correct server name in your connection string. The example uses ".\SQLEXPRESS", which is valid for a local instance only. If the database is on a different server to IIS then you need to specify the target server name instead of "."

Thanks for the help, but I'm still getting a database conflict on the IIS SERVER, but not on my DEV system.  If web application is NOT running, then the WinForms application works fine.

The web application and the WinForms application are both running on the same server (the IIS server).

The connection for my web project (in Web.Config) is:

      <connectionStrings>
      <clear/>
        <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;database=c:\SpotNote\DB\SpotNote.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
        <add name="SpotNoteDataBase" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;database=c:\SpotNote\DB\SpotNote.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
      </connectionStrings>

The connection for my WinForms project is:

DSN = "Data Source=.\SQLEXPRESS;database=C:\SpotNote\DB\SpotNote.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"

The error message I receive when my web application is running and I try to start my desktop application is:

System.Data.SqlClient.SqlException: Cannot open database "C:\SpotNote\DB\SpotNote.MDF" requested by the login. The login failed.
Login failed for user 'JD-DESKTOP\James'.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

To reiterate, on the IIS server, either one works alone, but not together.  On my DEV system they work together.

Thanks again for any help you can provide.

HBH

Thanks again for all of your help on this.

I've determined what the issue is.

It's related to the fact that we're using SQL Server Express.

SQL Server Express allows up to 10 connections, but only 1 application can attach to the database files at a time.

Thus once IIS opens the DB, no other application can access it.

The only solution would appear to switch to one of the other editions of SQL Server.

HBH

Correction. The number of connections isn't quite unlimited. You can have 32767 simultaneous connections.

Forgive me, but I guess I must be dense.

I've tried all combinations of connection strings for both my ASP.NET application and my WinForms application with no luck.

I don't understand how to use the "sp_attach_db proc" you're suggesting.

Is this something that goes in the connection string?

Is this something I do from my ASP.NET and WinForm application?  If so, then how?

For example, currently my WinForm application:

           DSN = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SpotNote\DB\SpotNote.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
       Dim dB As SqlConnection = New SqlConnection(DSN) ' Connection object
       dB.Open()

How would this (and the similar code in my ASP.NET appliction) change?

HBH