[RESOLVED] Using variable for connection strings-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] Using variable for connection strings

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Resolved [RESOLVED] Using variable for connection strings

    Hello, I'm having trouble with variables for connection strings. In my app.config database path seems fine but on form, I'm getting error for that connection string. When I try to add:

    C# Code:
    1. _connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; " +
    2.         "AttachDbFilename = \"|DataDirectory|\\gazi_db.mdf\"; " +
    3.         "Integrated Security = True; Connect Timeout = 30";

    the database won't work correctly; it can't save data on exit. However this Works fine:

    C# Code:
    1. connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; " +
    2.         "AttachDbFilename = \"C:\\Users\\Can\\Desktop\\c_sharp_gazi_installer" +
    3.         "\\Gazi Installer\\gazi_installer\\gazi_db.mdf\"; " +
    4.         "Integrated Security = True; Connect Timeout = 30";

    How can I fix this?

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,277

    Re: Using variable for connection strings

    Well, since you didn't post the error, we're relegated to guessing as to what the error is. My guess is that the error is something along the lines of the database is not found. That's because you're installing the database into the app folder... which is generally discouraged. It should be deployed to the Datadirectory for the app (the installer takes care of that) ... Again, this is all guess work here, but I'd go back to the installer you're using and tell it to stuff the database into the DataDirectory rather than in the app path.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Re: Using variable for connection strings

    Thank you for your reply. I'm getting error

    Code:
    An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
    for line sqlDataAdapter.Fill(dataTable)

    I tried adding

    C# Code:
    1. AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments));

    but am still getting error.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,097

    Re: Using variable for connection strings

    Why would setting the data directory to your Documents folder be useful when you say that explicitly setting the data source in the connection string to your Desktop folder works? How EXACTLY are you deploying your application? Where EXACTLY is your MDF file in relation to your EXE?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Re: Using variable for connection strings

    I don't know how to answer, couldn't understand your first question, sorry for my English. I tried Release type and running it by f5. I was also struggling with prerequisties, sorry for the late reply. I'm sure I'll have to compile with framework 3.5 prerequisties.

    It's the best for my mdf to be in same folder of exe. Thank you for your reply.

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Re: Using variable for connection strings

    Thanks for your replies. I got help from another site and fixed the problem using stringbuilder. Here's my final code:

    AppDomain.CurrentDomain.SetData("DataDirectory", @AppDomain.CurrentDomain.BaseDirectory + "database");

    C# Code:
    1. SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    2. builder["Data Source"] = @"(LocalDB)\MSSQLLocalDB";
    3. builder["AttachDbFilename"] = @"|DataDirectory|\gazi_db.mdf";
    4. builder["Integrated Security"] = true;
    5. builder["Connect Timeout"] = 30;
    6.  
    7. sqlConnection = new SqlConnection(builder.ConnectionString);

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,097

    Re: Using variable for connection strings

    Your code is not wrong but it is suboptimal. It should be like this:
    csharp Code:
    1. SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    2.  
    3. builder.DataSource = @"(LocalDB)\MSSQLLocalDB";
    4. builder.AttachDBFilename = @"|DataDirectory|\database\gazi_db.mdf";
    5. builder.IntegratedSecurity = true;
    6. builder.ConnectTimeout = 30;
    7.  
    8. sqlConnection = new SqlConnection(builder.ConnectionString);
    There's no need to call SetData on the current AppDomain because you can include that subfolder in the file path. You could even collapse that code down to this:
    csharp Code:
    1. sqlConnection = new SqlConnection(new SqlConnectionStringBuilder
    2.                                     {
    3.                                         DataSource = @"(LocalDB)\MSSQLLocalDB",
    4.                                         AttachDBFilename = @"|DataDirectory|\database\gazi_db.mdf",
    5.                                         IntegratedSecurity = true,
    6.                                         ConnectTimeout = 30
    7.                                     }.ConnectionString);
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.