Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America asked on

Modify ADO connection string...

In a simple drag and drop application, I have:

connectionString="Data Source=MIKE-VAIO;Initial Catalog=WSCGSoftware;Integrated Security=True"

connecting to a database (WSCGSoftware) in SQL Server 2012 Enterprise. I used  "Add New Data Source" wizard to do this.

Question: How can I modify this connection string to the same database at "C:\TestFolder\WSCGSoftware.mdf" on the same computer?

This application will be tested in another computer later with "C:\TestFolder\WSCGSoftware.mdf" on it, but there will be no SQL Server installed on it.

If this is not possible, what if the second computer had SLQExpress installed on it.

Idealy, path "C:\TestFolder\" should be the root directory of the project itself. Some comment on it will be appreciated.

Thank you.
 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Test_A.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=MIKE-VAIO;Initial Catalog=WSCGSoftware;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
    </startup>
</configuration>

Open in new window

Microsoft SQL Server 2008Visual Basic.NET

Avatar of undefined
Last Comment
Mike Eghtebas

8/22/2022 - Mon
Dave Baldwin

Since the connection string is to connect to the SQL Server, not the file, I don't see how you could possibly do that.  You SQL statements and driver are not going to work directly on the file.  They need the SQL Server to operate properly.
SOLUTION
TheAvenger

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Mike Eghtebas

DaveBaldwin,

re:> to connect to the SQL Server, not the file,

The attached image shows Option 1 (connecting to SQL Server) and Option 2 (connecting to database file). Having said this, I can see how it still all the drivers etc. that comes with SQL Server will be used with option 2.

So, then as stated in the original question: "...what if the second computer had SLQExpress installed on it..." then how would you modify the connection string in order to be working with "C:\TestFolder\WSCGSoftware.mdf" where WSCGSoftware may or may not present in the SQL installed.

 TheAvenger,

re:> SQL Server will know where the database is because it is attached to it.

There might be could of instances of SQL Server with the database with the same name but with different data + plus one in C:\TestFolder again with different data. In response to this part please consider Option 1 and Option 2 on the attached image.


Also see:
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28227481.html

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\Mike\Documents\__smc\_CS 32 ADO\WSCGSoftwareDatabase\WSCGSoftware.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

This type of connection is what I am expecting from the experts.

Thank you,

Mike
ChangeDataSource.png
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Mike Eghtebas

Sorry, this is valid one:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="TestB.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WSCGSoftware.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
    </startup>
</configuration>

Open in new window


I have generated this on a yet third computer with SQl Express on it and it works.

So, basically, I want have some conversation about it I can get more education about it.

The problem is this:
I have SQL Server Enterprise. I develop this simple application easily. Butthe other computer (the third computer has SQL Express).

1. I do develop with database in SQL Server 2012.
2. Give to someone else who has only SQL Express.
3. In creating the data connection we have agreed to use Option 2 (in the above image). This way, the database travels with the project and it doesn't depend on the database in their SQL Server.

So, I want to develop in SQL Server 2012. When done, modify the connection string and give it to the other person to look at the application with my data included with my app.

Alternatively, you can supply answer to the link given before to my other question this way, I don't have to look for work around like this.

Thanks,

Mike
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Mike Eghtebas

Dave,

re:> There isn't any case that uses a file without an SQL Server.

I never said there is. I assumed there maybe hence the question because I was not sure about it and then stated if this is not possible then...

Bottom line is:
Option1 :
   <connectionStrings>
        <add name="Test_A.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=MIKE-VAIO;Initial Catalog=WSCGSoftware;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

To, Option 2:

    <connectionStrings>
        <add name="TestB.My.MySettings.WSCGSoftwareConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\WSCGSoftware.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

Open in new window

Dave Baldwin

You said "but there will be no SQL Server installed on it."
ASKER
Mike Eghtebas

Sorry Dave,

I am not blessed with English as my first language, I probably have not described properly.

When I wrote: "If this is not possible, what if the second computer had SLQExpress installed on it." I meant to say if lack SQL Server installation was not an option, lets proceed with assumption that SQL Express is present.

Update: Looking at the connection string in my last comment, I think I probably have answered my question partially, if you agree. I can change the connection string but as to manually copying and pasting the database from SQL Server .../DATA/ folder to the project root folder I am not sure but I can test it and post it here.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dave Baldwin

To be clear, you can not actually use Only the file but you can ask SQL Server or SQLExpress to attach that file and use it.  As for the actual file location, you just have to get the details right.  'mdf' files do not have to be in the standard 'data' directory, I have several that are not that work fine.  I'm not sure but I think you need to 'un-attach' the database to properly copy it.  Maybe you will want to keep a 'reference' copy that is not currently attached so you could copy it and attach it on the target system.
TheAvenger

@Dave: The right term is "Detach". There is a command when you right click on the database. Also you can see which files are in use by the database by right clicking and going to the different options that have the name "file" in them.

The database files can be in any folder that is accessible for the SQL Server user. This means the user has to have rights to read and write to the files.

If you want to make a copy of the database or move it to another server, the options are to detach it (a bit bad because it might mess up some security roles when attaching again) or the better option stop the SQL Server service, copy the files, start the SQL Server service. Works 100%
ASKER
Mike Eghtebas

Thank you very much for the info. You are right on every point made. After comparing the content of project folder, I see that it is not easily possible to manually copy and paste the files. But at least I know what wouldn't work.

Mike
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Mike Eghtebas

TheAvenger,

Thanks for the good comments.

Mike