none
How to edit data source name. RRS feed

  • Question

  • Hi, i am using 2 computer to do learning my coding.

    when i am at home the connection string for my database is 
    Data Source=.;AttachDbFilename="R:\future\STB KA SJ\STB KA SJ\Customer.mdf";Integrated Security=True

    when i am at office, i need to change the connection string for my database became this in order to keep it continue

    Data Source=.\\SQLExpress;AttachDbFilename="R:\future\STB KA SJ\STB KA SJ\Customer.mdf";Integrated Security=True

    i guess that happen when i am installing SQLserver using default name. but this is getting tricky here. everytime i need to rename my code before testing the wfa, so i need help regarding how to configure this 

    . become .\\SQLExpress

    l tried to search at ODBC but nothing there. please help. thank you

    Friday, October 16, 2020 5:49 AM

Answers

  • Express and Developer are the same SQL codebase so you don't really need both. Express starts up when you need it whereas Developer runs as a service. At this point I'd leave your SQL installations alone. I think you have SQL Express working so just use it.

    In SSMS notice that your DB is connected but it says recovery is pending. That isn't good. That means your DB is corrupted and SQL cannot repair it to make it available. This can happen for a variety of reasons. Note that normally you have both a .mdf and .ldf files for your database. One is the data and the other is the log file for it. It sounds like the log file is corrupt. To fix it follow the steps here. Once the database is repaired then disconnect it from SSMS.

    Another thing to note is that you said you're running from USB. This is probably fine for your .NET app but I'm not so sure about trying this reliably. If the USB drive disconnects while a DB operation is in progress you're going to corrupt the DB again. I'd recommend that you keep the DB on a local drive while you're working on it.

    Once you've recovered your DB then go back to your app's config file and use the connection string 'Data Source=.\\SQLExpress;AttachDbFilename="R:\future\STB KA SJ\STB KA SJ\Customer.mdf";Integrated Security=True'

    At the office use the same connection string. If you don't have SQLExpress installed at the office then install it. Now you can use the same connection string on both machines.

    Once you have all this working properly then the last thing I'd recommend is that you move your DB file into your app's directory instead. Then modify your connection string to retrieve the database file relative to the application using the `|DataDirectory|` path instead. But get the DB loading from SQL Express first.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Beebee1988 Sunday, October 18, 2020 1:59 AM
    Saturday, October 17, 2020 4:33 PM
    Moderator

All replies

  • That's the cost of targeting multiple database servers. At the office you seem to have a local SQL Server instance installed that uses the default instance name. Hence the . works. At home you appear to be using SQL Express instead. Since you're using 2 different database engines with 2 different instance names the connection strings have to change.

    Note that you are using SQL Express which was "replaced" by LocalDB a few versions back so I'm curious what version of Visual Studio you're using. You are also attaching a DB file to the database so really you don't need a SQL Server instance anyway.

    Workaround 1 - (assuming Visual Studio 2017+) Use LocalDB instead. Change the connection string to point to a LocalDB instance. You can get the connection string by using SQL Server Object Explorer in Visual Studio. It is normally something like '(LocalDb)\MSSQLLocalDB` or one with a version number in it. Refer to this documention. Assuming both your home and office machines have the same VS version installed then the connection string will be the same between environments.

    Workaround 2 - Install the same version of SQL on your home computer as what you run in the office. I assume it is probably SQL Developer but I'm just guessing. Then the connection strings are the same albeit you're using more resources then needed.

    Workaround 3 - Use an environment variable or another config entry to specify which of the 2 connection strings to use.

    Workaround 4 - Update your connection string reading logic to look for a custom connection string file based upon user name. If found use that otherwise use the app's default connection string. On your home computer create this custom connections tring.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, October 16, 2020 2:05 PM
    Moderator
  • Hi!

    From my practice, using LocalDB with attached files is very unreliable and can lead to frustrating errors. I would suggest you install SQL Server Express edition and import the database into it with SMSS. This will make your debugging process more reliable and predictable. To learn how to use connection strings to authenticate with SQL Server you can look here

    Friday, October 16, 2020 4:36 PM
  • Hi Artem, 

    thank you for your answer, but i prefer to work with visual studio. and my problem is not debugging problem. but i am creating a wfa to insert edit and delete data.

    thank you

    Friday, October 16, 2020 5:07 PM
  • Hi Michael, 

    Really appreciate your long explanation. firstly i need to explain that i really have no clue of how SQL works, i am starting to coding for learning and fun purpose where i have no experience with coding background except with a exceptional understanding using ms excel formula. which i thought might be similar, yet it is so different, like excel is a simple language, c# is a labyrinth. sorry for all my bs let me explain

    I am using vs 2019

    the first problem occur here so i tried so many installation since i cannot figure out what it the different between SQL Express and SQL Developer, even until now. and i download both tried to open my download for it and install it. and somehow after installation i still got stuck until i change the data source which i take it as new server to process the database. now i dont know how to undo everything and do it in the correct way. 

    Trying your method one. there is no web.config in my solution.

    Number 3 and number 4 solution is not understandable by my brain. lol..so sad.

    hence, i am trying no. 2 which will take some time to download and install the server. will update again after installation.

    another one, you said that 

    You are also attaching a DB file to the database so really you don't need a SQL Server instance anyway.

    what does that mean? is it i can simplify connection string? please teach me how?

    thank you 

    Friday, October 16, 2020 5:28 PM
  • SQL Express is a single-user instance of SQL designed for local development. It is deprecated in lieu of LocalDB now for development. Don't use it unless you absolutely must ship SQL Express with your app.

    SQL Developer is a full version of SQL Server designed for complex apps. There is no difference between the 2 versions. Generally SQL is hosted on a server somewhere and you just access it from your app.

    Question 1 - In your connection string you are pointing to a file on disk for the database. Why? Is this just a sample database you want to load to play with or do you actually have a requirement to keep the database in a file that you can load on demand?

    Question 2 - The app you're building is just for learning or is it an app you plan to distribute?


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, October 16, 2020 5:45 PM
    Moderator
  • Hi Michael, i am back heehehe

    So after trying to install again, seems like i have alot of server in my computer. rofl, how the heck its number keep adding.

    Then i tried to using .\ and it show something like this

     hope my screenshot can get you some picture

    \

    Friday, October 16, 2020 5:47 PM
  • Firstly you may not need SQL Developer. See my earlier questions.

    Secondly SQL Server uses shared components. So if you install, say SQL Express 2019, and then install another SQL Server 2019 instance (Developer, etc) then it'll already have the core components installed and will simply add to the installed content that is already there. You can have any # of SQL Server instances (editions) installed at any one time. Each SQL Server instance is a separate Windows service that runs so I wouldn't recommend installing too many but they do run in isolation from each other.

    To access a SQL Server you have to specify an instance when you connect. The default instance name is something like MSSQL and this is what the installer wants to use. For SQL Express it is SQLEXPRESS. If you try to install another instance of SQL and the instance name is already in use then you have to specify a different one (e.g. SQLDEV).

    In your connection string when you want to connect to SQL you have to specify the instance name. If you don't (e.g. `.`) then it defaults to MSSQL which is the default instance. To specify the instance name you put the instance name after the machine name (the dot), e.g. `.\SQLEXPRESS` or `.\SQLDEV`. Then it'll connect to the machine (local) and instance specified.

    In your screenshot you're trying to connect directly to the DB but it appears your login account doesn't have permissions to that database. SQL requires that you have a login to the database. Since that file came from your Office, I assume, you'll need to use an account that has permissions. Since you are probably not connected to your Office's network you'll probably need to use SQL Authentication which requires you enter a user name and password. That account information needs to be set up in the database by whoever created that database though. So most likely you'll need to do that from your office.

    Perhaps more easily, use SQL Server Management Studio to load up the database on your local machine. SSMS does not ship with SQL anymore so you have to install it separately if you haven't already. Then inside SSMS connect to your local SQL instance (Dev or Express, whichever one you want to use). Then attach the database file to SQL. Since you have admin privileges in SQL you should be able to go to the database and set up a login for your local user account that you can use from home. Alternatively set up a SQL login account.

    Once you've done all that you should be able to just use the SQL Server instance instead of the database file, the easiest approach. To do that modify your connection string.

    "Server=.\SQLEXPRESS;Database=YourDatabaseName;Integrated Security=SSPI"

    If you still want to use the database file anyway you'll need to detach it from the SQL Server instance first.

    If all this seems like a lot of work, it is. The absolute easiest solution is to simply use SSMS, attach the database to the SQL Server instance you want, ensure your local account has permissions to that database in SQL and then modify the connection string to just use the SQL instance and forget the database file stuff altogether.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, October 16, 2020 6:01 PM
    Moderator
  • Hi,

    I have a simple trick to deal with this case, simply I add an app settings parameter which purpose is to select the currently used connection string whether it is production or development, suppose the configuration below:

      <connectionStrings>
        <add name="ProductionConnectionString" connectionString="data source=192.168.1.111;initial catalog=ProdDb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
    	<add name="defaultConnectionString" connectionString="Server=.\;Database=DevDb;Integrated Security=True;;MultipleActiveResultSets=True;Application Name=EntityFramework" name="ProductionConnectionString" providerName="System.Data.SqlClient" />
    
      </connectionStrings>
      <appSettings>
        <add key="ConnectionStringToUse" value="ProductionConnectionString" />
    
      </appSettings>

    in the C# code: 

    private readonly string _connectionStringToUse = ConfigurationManager.AppSettings["ConnectionStringToUse"]?.ToString() ?? "defaultConnectionString";
    private readonly string _connectionString = ConfigurationManager.ConnectionStrings[_connectionStringToUse];

    then you can adapt the concept to your need, for example with ADO.Net you should use the interfaces IConnectionString, ICommand etc ... to be able to switch between different providers.

    Hope it helps!

    best regards.

    Friday, October 16, 2020 10:51 PM
  • Hi Beebee,

    The easiest way to deal with connection strings is to store them in a config ... and that would be the app.config file in your project which, once compiled, becomes MyApplication.exe.config. In addition to ConnectionStrings, you can also store various settings for your application, if you have any.

    In your case, it would look like this:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <appSettings>
        <!-- settings are configured with key/value pairs, in the following format: -->
        <add key="MySetting" value = "The Value Of The Setting" />
      </appSettings>
      <!-- But, this is the section you're interested in -->
      <connectionStrings>
        <!-- Home -->
        <add name="MyDatabase" connectionString="Data Source=.;AttachDbFilename="R:\future\STB KA SJ\STB KA SJ\Customer.mdf";Integrated Security=True"/>
        <!-- Office -->
        <!--<add name="MyDatabase" connectionString="Data Source=.\\SQLExpress;AttachDbFilename="R:\future\STB KA SJ\STB KA SJ\Customer.mdf";Integrated Security=True"/> -->
      </connectionStrings>
    </configuration>
    

    Or, based on Michael's replies, use whatever type of connection string you decided to go with.

    Notice that I have commented out the Office connection string. So, this particular iteration of the config would be how you use it at home. Comment / uncomment the appropriate setting in your config file, depending on your location, before you start the application.

    Also note that the name is the same for both settings. What this means is that your application doesn't change at all, you won't need to change your code and recompile it. 

    This is how you retrieve the connection string in your code:

    // You may have to add a using System.Configuration and add System.configuration to your project's References.
    string ConnectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;

    Hope that helps.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 16, 2020 10:53 PM
    Moderator
  • Hi Michael,

    thank you again for your kind reply, so using express or developer is the same is it? 

    answer

    1. that mdf file i kept together with my solution in a flash drive which i will use as place to load my table and things for my code, since i am using 2 computer to learn this, i need this database to be portable to keep update them. and my office is actually a small company which i have full access to it

    2. my current motive is to learning, after that i will trying to create simple stock application for my office to use.

    reply for your another answer. I did have SSMS installed in my computer. and you see, i connect it at SSMS but when i am trying to connect this instance at VS data source, it said login failed thats why i am using different instance.

    and i tried just now using SSMS to attach database into /SQLExpress instance

    stuckkkkeddd!!!

    Saturday, October 17, 2020 5:27 AM
  • Hi Bonnie, sorry i overlooked your message. please take a look at my latest reply to Michael. maybe you can get a picture that not only the server instance that confusing me, but the SQL server version also. which i dont know why there is lower version since i download and install all the same way.
    Saturday, October 17, 2020 12:08 PM
  • Hi Michael,, I just thought that, if i install again sql server and use same instance, will the problem go away?
    Saturday, October 17, 2020 1:22 PM
  • You cannot reinstall SQL with the same instance name. It won't let you. You'd first have to uninstall SQL for that instance. But that isn't going to solve the problem you're having.

    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, October 17, 2020 4:23 PM
    Moderator
  • Express and Developer are the same SQL codebase so you don't really need both. Express starts up when you need it whereas Developer runs as a service. At this point I'd leave your SQL installations alone. I think you have SQL Express working so just use it.

    In SSMS notice that your DB is connected but it says recovery is pending. That isn't good. That means your DB is corrupted and SQL cannot repair it to make it available. This can happen for a variety of reasons. Note that normally you have both a .mdf and .ldf files for your database. One is the data and the other is the log file for it. It sounds like the log file is corrupt. To fix it follow the steps here. Once the database is repaired then disconnect it from SSMS.

    Another thing to note is that you said you're running from USB. This is probably fine for your .NET app but I'm not so sure about trying this reliably. If the USB drive disconnects while a DB operation is in progress you're going to corrupt the DB again. I'd recommend that you keep the DB on a local drive while you're working on it.

    Once you've recovered your DB then go back to your app's config file and use the connection string 'Data Source=.\\SQLExpress;AttachDbFilename="R:\future\STB KA SJ\STB KA SJ\Customer.mdf";Integrated Security=True'

    At the office use the same connection string. If you don't have SQLExpress installed at the office then install it. Now you can use the same connection string on both machines.

    Once you have all this working properly then the last thing I'd recommend is that you move your DB file into your app's directory instead. Then modify your connection string to retrieve the database file relative to the application using the `|DataDirectory|` path instead. But get the DB loading from SQL Express first.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Beebee1988 Sunday, October 18, 2020 1:59 AM
    Saturday, October 17, 2020 4:33 PM
    Moderator
  • ok noted... thanks alof for your guide. i guess i have some picture......hehehe
    Sunday, October 18, 2020 2:00 AM