[RESOLVED] dynamic connection string

[toecutter]

I have been trying to set the connection string at run with variations of this:

Option Strict On
Option Explicit On

Public Class payments
    Private Sub payments_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cConString As String = (My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\dir\expressAccountsDB.mdf;Integrated Security=True;User Instance=True")
        PaymentsTableAdapter.Connection.ConnectionString = (cConString)
        'TODO: This line of code loads data into the 'ExpressAccountsDBDataSet.payments' table. You can move, or remove it, as needed.
        Me.PaymentsTableAdapter.Fill(Me.ExpressAccountsDBDataSet.payments)
    End Sub
End Class

in VB2008 are we able to do this?

I have read almost everything on doing this and i am unable to get it to work.

This is what my connection looks like in setting at the moment:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\expressAccountsDB.mdf;Integrated Security=True;User Instance=True

and this is from the app.config file:

    <connectionStrings>
        <add name="expressAccountsDB.My.MySettings.expressAccountsDBConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\expressAccountsDB.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

I have tried numorus variation of the above from the many examples i have came across but i was unable to get any working.

[jmcilhinney]

That code can't possibly work because your new connection string has no Data Source value. What are you actually trying to do? Change the location of the MDF file? In that case, and even apart from that, you should use a SqlConnectionStringBuilder.

Dim connection = PaymentsTableAdapter.Connection
Dim builder As New SqlConnectionStringBuilder(connection.ConnectionString)

builder.InitialCatalog = My.Computer.FileSystem.CombinePath(My.Computer.FileSystem.SpecialDirectories.MyDocuments,
                                                            "dir\expressAccountsDB.mdf")
connection.ConnectionString = builder.ConnectionString

That way, you keep everything else the way it is and just change exactly what you need to change. Notice the proper way to combine file and folder paths too.

[toecutter]

What are you actually trying to do? Change the location of the MDF file?

Yes.


If i change the extension of the .mdf in the dir folder for testing purposes the database still loads into the grid and the properties of the PaymentsTableAdapter suggest this but it is impossible.




Does this line of code mean that it will try and load the database form the "Initial Catalog" and if unsussessful it will then load from the "DataDirectory"?

ConnectionString	"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\expressAccountsDB.mdf;Initial Catalog=C:\Users\ess\Documents\dir\expressAccountsDB.mdf;Integrated Security=True;User Instance=True"	String

[QUOTE] Notice the proper way to combine file and folder paths too.[/QUOTE
noted, cheers for that

toe

[jmcilhinney]

Opps! This line:

builder.InitialCatalog = My.Computer.FileSystem.CombinePath(My.Computer.FileSystem.SpecialDirectories.MyDocuments,
                                                            "dir\expressAccountsDB.mdf")

should have been this:

builder.AttachDBFilename = My.Computer.FileSystem.CombinePath(My.Computer.FileSystem.SpecialDirectories.MyDocuments,
                                                              "dir\expressAccountsDB.mdf")

[toecutter]

thanks,
80000 posts, your entilted to one mistake


Now when i run the project i get this error when loading the table adapter with the dataset both in the designer and also when publishing the project and insatalling.

Me.PaymentsTableAdapter.Fill(Me.ExpressAccountsDBDataSet.payments)

Database 'C:\Users\ess\Documents\Visual Studio 2008\Projects\expressAccountsDB\expressAaccountsDB\bin\Debug\expressAccountsDB.mdf' already exists. Choose a different database name. An attempt to attach an auto-named database for file C:\Users\ess\Documents\dir\expressAccountsDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

[jmcilhinney]

The error message is telling you what's wrong.

[toecutter]

hmmmm,

Well the first part cannot be true as i have deleted it from the debug folder and tried, i dont want to use a different database anyway.

Database 'C:\Users\ess\Documents\Visual Studio 2008\Projects\expressAccountsDB\expressAaccountsDB\bin\Debug\expressAccountsDB.mdf' already exists. Choose a different database name.

I thought it would have to exist to be able to load it anyway i removed it from the folder and at least that part of the error message has been eliminated.

C:\Users\ess\Documents\dir\expressAccountsDB.mdf failed. A database with the same name exists.

I have no idea why this would be the case

or specified file cannot be opened

i checked out what UNC means and not sure if thats the is....

or it is located on UNC share

UNC names identify network resources using a specific notation. UNC names consist of three parts - a server name, a share name, and an optional file path.

[jmcilhinney]

If the data file is part of your project, as it should be, then it's going to get copied to the output folder by default. If you don't want it copied to the output folder then the correct approach is to select the data file in the Solution Explorer, open the Properties window and set the Copy To Output Directory property to Do Not Copy. When you rebuild, the existing copy will be deleted and no new copy will be made. You can then copy the data file manually from the project folder to another location.

If you want to use a different data file then you'll have to make this change for every table adapter. As such, you should write a method that updates the connection string in a common location and then pass the Connection property of every table adapter to that method before using it.

[techgnome]

I'm going to go out on a limb and say that the reason for the error is because there is already a database attached that has that name... likely your original database... it even told you so in the error message "A database with the same name exists" ... when you first ran it, it attached to the mdf from a specific location... now you're trying to attach it from a new location, but with the same name... SQL Server is going "Woah! Wait a minute! You've already got one of these... can't have that."

What (I think) you need to do is go into SQL Server and see if there is already a database by the same name, and if so detach it (I wouldn't delete it, just detach it). Then try again. Which brings up the question of what is your database doing in the users Document folder in the first place? It should be safely ensconced in the DataDir where a user is less likely to delete it.

-tg

[toecutter]

Pretty sure all of what you guys say above are implimented.

i do not want to use a different DB.
the DB is in "Documents\expressAccountDB\DB\"
Copy To Output Directory property set " to Do Not Copy"
Db in solution exployer has been "detached"
no db in server exployer
i want the DB in the documents folder so i can easily move it between my computers




maybe it will be eaier to just add a button to open the startup dir and copy from there when time come to uoload to laptop/desktop as this seems like a major episode to simply load it from a custom location...

thanks
toe

[jmcilhinney]

Noone said anything about removing the MDF file from your project. That's not "detaching". Anyway, the error message is not lying to you. Stop your project and restart the SQL Server service, to make absolutely sure that any dynamically attached databases are detached. Make sure that you're not making any other connections to the database in your code before the one that uses the modified connection string and run the project again.

Having said all that, is there a particular reason that you're changing the connection string in code rather than updating it in the config file? The default connection string for each table adapter comes from the config file. The ability to edit it is the whole reason that it's stored there in the first place.

[toecutter]

restart the SQL Server service?

I did find the SQL Server Config manager and stopped the SQL Server (SQLEXPRESS) which causes the project to fail, restarted and all good.

Does this mean that any machine that wants to run this project will need to have the SQL Server Config Manager/SQL Server (SQLEXPRESS) installed and running?

i added below to appconfig file and seems to be all good, even publish, installed and tested.

 connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\My.Computer.FileSystem.SpecialDirectories.MyDocuments, expressAccountDB\DB\expressAccountsDB.mdf;Integrated Security=True;User Instance=True"

Now my settings in "My Project" also includes the custom conString

[jmcilhinney]

Of course every user is going to need SQL Server Express installed. How are you going to use a SQL Server Express database without SQL Server Express installed? Users don't need the Configuration Manager because they don't need to configure the instance. To them it is transparent but it needs to be there.

What happened to you is not going to happen to your users because they're not running the project from the IDE and messing around with multiple connection strings. If something does go awry though, the average user is going to restart their computer. One thing that does is shut down all the services and then start them again and that can be what fixes some issues. In such cases they could have just restarted the appropriate service rather than restarting their machine but generally we don't know if a service is to blame and which one.

[jmcilhinney]

restart the SQL Server service?

I did find the SQL Server Config manager and stopped the SQL Server (SQLEXPRESS) which causes the project to fail, restarted and all good.

Does this mean that any machine that wants to run this project will need to have the SQL Server Config Manager/SQL Server (SQLEXPRESS) installed and running?

i added below to appconfig file and seems to be all good, even publish, installed and tested.

 connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\My.Computer.FileSystem.SpecialDirectories.MyDocuments, expressAccountDB\DB\expressAccountsDB.mdf;Integrated Security=True;User Instance=True"

Now my settings in "My Project" also includes the custom conString

I very much doubt that that connection string actually does what you want. You can't use VB code in a connection string.