Specify database server in making executable vb.net

[Lethugs]

How can I specify the Server of my database when I make my project executable

I have this module I call everytime i modify my database

 Public Sub main()


        constr = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:"database file".mdf;" & _
            "Integrated Security=True;Connect Timeout=30;User Instance=True"
        Con = New SqlConnection(constr)
        Try
            Con.Open()
            'MsgBox("Server Connection is Open ! ")
            'frmMain.Connection.Text = "Connecting To serv``er"
            Con.Close()
        Catch ex As Exception
            MsgBox("Sorry Can not open connection ! ", vbCritical, "Database")
        End Try

    End Sub

    MY project will be installed on several computers. The database in integrated in my project 
since i used Service-Based Database. Meaning when i copy the project to different computers,
the database always comes with it.

    I'm thinking that when I will make an executable project, the database will be included.
How will I change the datasource above during installation of my project just to ensure that
there will be a single server for my database?

Thanks

[john.knapp]

For your "Service Based Database", you are using MSSQL?

[Lethugs]

Yes Im using microsoft sql server 2008 r2. I just cant think of a way to ensure that the installed application will not used the integrated database within it. Do i have to exclude the database form the application and separately install it?

Thanks

[john.knapp]

Well, for one thing - if your connection string specifies SQLExpress as your datasource, the application can't use the local database file.

I would remove the db file and all references to it now, myself - and code for SQL or SQLExpress, whichever you plan on using in Release.

[Lethugs]

you mean I will remove the module then hard code the connection string in every form that calls that module? If thats the case, if i will compile my project now (make it executable and ready install from cd), how will i change that connection/datasource as what the user wants?

Im thinking of doing something like during users installation set up, this system will ask him to specify its server and location of database then dynamically change my source code above but i dont really how to do it.. Can you please guide me sir

thanks

[TnTinMN]

how will i change that connection/datasource as what the user wants?

I thought you did not want them to be able to change the DataSource.

If you need to allow them to select a server, maybe this will work for you?

    Dim csb As New SqlClient.SqlConnectionStringBuilder

    csb.IntegratedSecurity = True
    csb.UserInstance = True

    ' Full File Path Works with both these DataSource constructs
    'csb.AttachDBFilename = "F:\SQLServer2008\AttachedDBTest.mdf"
        ' DataSource = ".\" & InstanceName
        ' DataSource = ServerName & "\" & InstanceName


    ' |DataDirectory| only works with DataSource = ".\" & InstanceName
    csb.AttachDBFilename = "|DataDirectory|\AttachedDBTest.mdf"

    ' Try to retrieve the available servers
    Dim dtSQLServers As DataTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
    If dtSQLServers.Rows.Count > 0 Then
        Try
            Dim ServerName As String = dtSQLServers.Rows.Item(0).Field(Of String)("ServerName")
            Dim InstanceName As String = dtSQLServers.Rows.Item(0).Field(Of String)("InstanceName")

            csb.DataSource = ".\" & InstanceName
            'csb.DataSource = ServerName & "\" & InstanceName

        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

    End If

    Dim conn As New SqlClient.SqlConnection(csb.ConnectionString)

    conn.Open()
    conn.Close()

[Lethugs]

Thanks for the reply

But where will i put this code? inside my module or i will create a form that will prompt during installation?

[TnTinMN]

The code I posted showed you a way to retrieve the available instances of SQL Server within the local network [System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()].

Where you place the code is up to you to decide.

Personally, I would make the user select a server on the first time execution of the program and then store that server info as a user setting. For subsequent runs, do a check on load to verify that the server is still available. If not, prompt the user for a new selection. It would also be advisable to verify that the database file exists where you expect it to be before attempting to make a connection. Users do get curious at times and can delete your files.

[john.knapp]

Curious is not the word I use when that happens!

[TnTinMN]

Curious is not the word I use when that happens!

LOL. And just what may the word you use be?

[john.knapp]

It would always end with "luser" :)

[Lethugs]

Personally, I would make the user select a server on the first time execution of the program and then store that server info as a user setting

How will i save that as user setting? Sorry but I'm just confused on the set up of my module, since i call this everytime i accessed my database. How will i change the code of my module, datasource part(constr) such that it will use what the user selected? How will it stays like that after the user installed my application? Its my first time to compile a vb.net project, sorry for bothering you..

Please guide me how to do it

Thanks

[Lethugs]

 Dim constr As String = System.Configuration.ConfigurationManager.ConnectionStrings("Title").ToString
        'Dim consta As String = Configuration.con
        Con = New SqlConnection(constr)

I tried this code on my module an it worked. It call the appSetting in app.config. The only problem now is how will i ask the user to indicate the server and database during installation and make sure it will not change after closing and opening of this project?

If i will remove the database from the folder of my application, how will i seperately install it? Will i create another exe file just for my database?\

Thanks

[Lethugs]

Is attachedDBFile in datasource advisable to use if the database is installed separately and in different location? I tried to delete my database in my application and put it to another location, unfortunately there was an error in debug that cannot find my database

[john.knapp]

I'm somewhat confused as to how your database and connection are set up.
Please verify the following items of information:

1. You are using SQL Server 2008 R2
2. You are manually attaching a database file
3. You do or do not want the database file deployed with your project ?????

[john.knapp]

Work in progress, expands on this code.