none
Project ignored .mdf file when I changed application startup from form1.vb to SubMain RRS feed

  • Question

  • My new project is going to have a standalone database HierarchyBE.mdf inside the project itself.

    I created the project with the database and when I changed the applications startup point from Form1.vb to SubMain in Start.vb. The HierarchyBE.mdf was flagged as ignored.

    The App.Config file shows:

    However, I can open the database like this:

    How do I fix this, so the HierarchyBE.mdf file is no longer ignored and I can connect to my backend database?

    Personally, I would like to do this in code.

    Thanks,


    MRM256

    Monday, March 4, 2019 7:49 PM

Answers

  • Hi,

    see the code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = xxxx; Integrated Security = True ;AttachDbFileName= C:\xxx\xxx.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim dt As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select * From TableName", conn)
                dt = New DataTable()
                sda.Fill(dt)
            End Using
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MRM256 Tuesday, March 5, 2019 3:14 PM
    Tuesday, March 5, 2019 5:16 AM

All replies

  • app.config is only used during debug. The run time config file is programname.exe.config sitting in  the location of the programname.exe file, and it's the file that .NET is looking at during runtime, which is derived from the app.config during the successful build of the project and is editable.

    You can give the file location in the connectionstring

    https://www.connectionstrings.com/sqlconnection/localdb-automatic-instance-with-specific-data-file/

    Monday, March 4, 2019 10:56 PM
  • I just started making  the application. The only things in the project are frmMain.vb, Start.vb, and HierarchyBE,mdf.

    First thing I need to do is connect to the MDF file. How would I do that using VB.NET code?

    Thanks,


    MRM256

    Monday, March 4, 2019 11:05 PM

  • You would use the ConfigurationManager to go get the connection string at the time you are accessing the database. It doen't matter if its Web or Windows desktop program it's the same. 

    http://myqol.com/CodeSamples/VBAspNet/VBAspNetExample.aspx?id=171

    Tuesday, March 5, 2019 4:42 AM
  • Hi,

    see the code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = xxxx; Integrated Security = True ;AttachDbFileName= C:\xxx\xxx.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim dt As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select * From TableName", conn)
                dt = New DataTable()
                sda.Fill(dt)
            End Using
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MRM256 Tuesday, March 5, 2019 3:14 PM
    Tuesday, March 5, 2019 5:16 AM
  • Hi Alex,

    I took your code snippet and modified it for my .MDF file. I get the following exception:

    This error looks like there are more that one MDF file of the same name in the project and therefore doesn't know which one to use.

    Could this be caused by the App.Config file?

    Found the problem. The Application.StartUpPath needed to be added to the connection string. 

    I have modified Alex's answer to reflect how I got it to function.

    Imports System.Data.SqlClient
    
    Public Class frmMain
        Private strAppPath As String = Application.StartupPath
        Private constr As String = "Data Source=(LocalDB)\MSSQLLocalDB; " &
                                   "AttachDbFilename= " & strAppPath &
                                   "\HierarchyBE.mdf; Integrated Security = True"
        Private conn As SqlConnection
        Private sda As SqlDataAdapter
        Private dt As DataTable
        Private strSQL As String
    
        Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn = New SqlConnection(constr)
                conn.Open()
                strSQL = "SELECT * FROM tblMainJobTitles;"
                sda = New SqlDataAdapter(strSQL, conn)
                dt = New DataTable()
                sda.Fill(dt)
            End Using
            Call dt_Read(dt)
        End Sub
    
    #Region "Debug Tool - DataTable Viewer Subroutine"
        Public Sub dt_Read(ByVal dt As DataTable)
            'Purpose:       Lets the programmer see the data
            '               contained in the DataTable
            'Parameters:    dt As DataTable
            'Returns:       Nothing - Just a Debug routine
    
            Dim myRow As DataRow
            Dim myCol As DataColumn
            'For each field in the table...
            For Each myRow In dt.Rows
                'For each property of the field...
                For Each myCol In dt.Columns
                    'Display the field name and value.
                    Debug.Print(myCol.ColumnName & vbTab &
                                myRow(myCol).ToString())
                Next
                Debug.Print(vbCrLf)
            Next
        End Sub
    #End Region
    End Class


    MRM256


    • Edited by MRM256 Tuesday, March 5, 2019 3:19 PM Modified Alex's Solution.
    • Marked as answer by MRM256 Tuesday, March 5, 2019 3:20 PM
    • Unmarked as answer by MRM256 Tuesday, March 5, 2019 3:20 PM
    Tuesday, March 5, 2019 2:53 PM
  • This error looks like there are more that one MDF file of the same name in the project and therefore doesn't know which one to use.

    No,  the location is where Localdb expects to find the MDF file in a User folder off of AppData. That's where Localdb looks for the MDF file when you don't use the Attachfile option in the connectionstring giving the location of the MDF file to use.

    Could this be caused by the App.Config file?

    No, you didn't use the Attachfile option in the connectionstring  to point to the location of the MDF file,  and Localdb looked in the default location for the MDF file.

    https://blogs.msdn.microsoft.com/sqlexpress/2011/10/28/localdb-where-is-my-database/

    <copied>

    LocalDB is different. It's built to be used by developers, not DBAs. And we have heard developers saying loud and clear that they just want to focus on their databases and database code. Therefore our primary design goal was to eliminate all the server configuration and management that is getting in their way. As a result LocalDB stores all the system databases deep inside the "hidden" AppData folder in the user profile. For example, after playing with the Automatic Instance in the previous post, I find this folder in my user profile:

    <end>

    Found the problem. The Application.StartUpPath needed to be added to the connection string. 

    You should have put the usage of the connectionstring at the location where you are doing the Open(). The StartupPath should have nothing to do with using the connectionstring.

    Secondly, you hardcode the connectionstring instead of using the ConfigurationManager to go get the connectionstring making it a dynamic solution to obtain the connectionstring from the config file.


    • Edited by DA924x Tuesday, March 5, 2019 6:20 PM
    Tuesday, March 5, 2019 6:19 PM
  • DA924x,

    Thanks for the education.

    When I added the MDF file. It was placed in C:\Users\Mark\Documents\Visual Studio 2017\Projects\Hierarchy\Hierarchy\bin\Debug. Which is the Application.StartupPath when ran from inside the IDE.

    Secondly, you hardcode the connectionstring instead of using the ConfigurationManager to go get the connectionstring making it a dynamic solution to obtain the connectionstring from the config file.

    I never liked Config files. To me it was just easier to put the database file inside the folder where the application was installed.

    Thanks again,


    MRM256

    Tuesday, March 5, 2019 7:32 PM
  • When I added the MDF file. It was placed in C:\Users\Mark\Documents\Visual Studio 2017\Projects\Hierarchy\Hierarchy\bin\Debug. Which is the Application.StartupPath when ran from inside the IDE.

    I never let VS set the default location of where a solution is created on HD. I make a directory called C:\Projects and create solutions in the Projects folder. But you can call the folder anything you want. I do it becuase that's how it's done in a professional environment. And I have more than one machine where I would download the source code from a code repository like TFS to a C:\Projects folder on another machine. 

    I never liked Config files. To me it was just easier to put the database file inside the folder where the application was installed.

    That's bad news there, becuase as for instance, you deployed the solution and the MDF file to the C:\Program Files directory, which is a protected directory on Vista to Win 10, the MDF file cannot be written to with data in the tables due to the O/S blocking write access to the MDF file. 

    You can make a folder away from C\Program Files and install everything there,  or you install the executables for the solution in C:\Program Files and  place the MDF file in C:\programdata a hidden directory where such files are located. 

    Tuesday, March 5, 2019 8:29 PM