two (desperate) questions for ASP.NET and SQL Server 2005 Express

[Loane Sharp]

Hi there

I'm not sure that this is strictly relevant to the ASP.NET newsgroup, but
perhaps you've encountered a similar problem. Please help!!!

I'm using the "System.Data.SqlClient" namespace in an ASP.NET application to
connect to a SQL Server 2005 Express database. Moreover, I'm using XCopy and
"Database=temp;AttachDBFilename='c:\temp\temp_Data.mdf'" in the connection
string when I attach to the database for the first time. (The database name
'temp' is used for purposes of this post only.) I encounter two problems:

(a) If I ever connect to the database a second time, I (understandably) get
the following message from the ASP.NET compiler: Database
'c:\temp\temp_Data.mdf' already exists. Could not attach file
'c:\temp\temp_Data.mdf' as database 'temp'. It seems I need to be able to
detect, from VB.NET, whether the database has already been attached, and if
so, to use the customary connection string rather than AttachDBFilename. At
the moment, to work around this problem, I use "On Error GoTo ..."
statements so that, if the connection string containing "AttachDBFilename"
results in an error, I try the normal connection method (database previously
attached), and if that returns an error, I show the user an error page.
Question: Is there a neater way to do this?

(b) Every week I use XCopy to attach another database containing updated
records. Again, I encounter the same error as above, but in this case I
can't use the On Error GoTo logic, for two reasons. Firstly, if I don't
delete the earlier version of the MDF file, the On Error GoTo logic simply
results in a connection established to the old database. Secondly, if I
delete the earlier version of the MDF file and attempt to attach the new MDF
file, I still get an error because, although a new .MDF file exists, a
database object named 'temp' already exists in SQL Express. It seems that I
need a method to delete the old database (both the MDF file and the database
within SQL Express) from VB.NET. Question: Am I right, or is there a neater
way to do this?

Any suggestions gratefully appreciated.

Best regards
Loane

 

[Loane Sharp]

Never mind, sorted it out, this is a classic undocumented feature: The file
path and database name must be contained in ' '.

Dim strConn = "Data
Source=.\SQLEXPRESS;AttachDBFileName='c:\temp\temp_Data.MDF';Database='temp';Integrated
Security=SSPI;Timeout=60;Connection Timeout=60;"

Used in this way, neither questions (a) nor (b) in my post apply.

Best regards
Loane