set connection string at run time in vb.net

[khair.ullah]

Hi every one .
Recently i complete my project on libraray managment system which work perpectly on my system. Now the problem is that when i Execute my project on other system it show error at con.open i.e it mean that connection string not set in this system . Now i need code for how i set the connection i.e when i execute the project .exe it show me a form in which there is Database name , Server name , User name and Password . And after clicking the OK button it pass these value to connection string and save there.
I need simply the code for passing these value to connection string and how save these value there.
Thank in advance

[tinstaafl]

One relatively simple way is put, Database name , Server name , User name and Password, variables in a module, and have your new form have a series of textboxes that the user can enter the information. A command button can contain code to validate the information and then pass the values to the global variables. It should be a relatively simple matter to build the connection string from there.

[john.knapp]

Here's some source code at MSDN for the Data Connection Dialog that was
?packaged/affiliated/an add-in? for Visual Studio 2005. Alternatively, there's this project at CodeProject.com.

[khair.ullah]

Thank You tinstaafl and john knapp for your reply

Let see...
when my project start it show Splash screen after that it show Connection Form in which i enter Database Name , server name, user name, password etc . Now after that i confuse that how i pass these value to connection string. and second thing when i set the connection string after that it not show Connection Form at Run Time when i ReOpen the Project..

Thank's Once Again

[john.knapp]

There were at least 3 different methods suggested in this thread, which one are you having a problem with?

[khair.ullah]

how i send a value to connection string when i run the program. I.e server name, database name, user name and password.

[john.knapp]

which method are you using

[khair.ullah]

sqlclient

[john.knapp]

SQLClient is your DataProvider. In your original post, you made two requests (sic)

1:

Now i need code for how i set the connection i.e when i execute the project .exe it show me a form in which there is Database name , Server name , User name and Password . And after clicking the OK button it pass these value to connection string and save there.

tinstaafl suggested:

One relatively simple way is put, Database name , Server name , User name and Password, variables in a module, and have your new form have a series of textboxes that the user can enter the information. A command button can contain code to validate the information and then pass the values to the global variables. It should be a relatively simple matter to build the connection string from there

Do you understand what he is suggesting here? This sounds like it would satisfy your first requirement.

2:

I need simply the code for passing these value to connection string and how save these value there.

You just take the values from those textboxes, dropdown lists, or whatever and build the connection string.

For this question:

second thing when i set the connection string after that it not show Connection Form at Run Time when i ReOpen the Project

Try this code in your Form_Load() event

Dim strFile As String = "ChangeConnectionString.udl"
Dim strUserDocFldr As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim newFullPath As String = System.IO.Path.Combine(strUserDocFldr, strFile)
Dim fs As System.IO.FileStream

If (Not (System.IO.File.Exists(newFullPath))) Then
    fs = System.IO.File.Create(newFullPath)
End If

Dim p As New System.Diagnostics.Process
Dim s As New System.Diagnostics.ProcessStartInfo(newFullPath)

s.UseShellExecute = True
s.WindowStyle = ProcessWindowStyle.Normal
p.StartInfo = s
p.Start()

[khair.ullah]

Thank you johan!
Just simply write a code with the help of which i change the connection string in app.config and then save.
I have a problem with "connectionstring.udl" what it is?

[john.knapp]

Just simply write a code with the help of which i change the connection string in app.config and then save.

I don't know what you mean by that, your english needs some extra translation. As it is written, my interpretation is that you want me to "just simply write <you> some code to change your connection string and save it.... Hopefully that is not really what you're trying to say.

I have a problem with "connectionstring.udl" what it is?

Connectionstring.udl is an empty plain text file. The .UDL extension causes Windows to open it with the Data Link Properties editor. To add it to your project would require too much work on my part - you should probably scrap that code and use your own.

when my project start it show Splash screen after that it show Connection Form in which i enter Database Name , server name, user name, password etc . Now after that i confuse that how i pass these value to connection string. and second thing when i set the connection string after that it not show Connection Form at Run Time when i ReOpen the Project..

Post the form name and the code for that form

[androidz]

hope this could help you..

add new data class
and before you can import those two configurations make sure to add the reference

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings
Namespace PC
    Public Class Data

        Public Shared Function GetConnectionString(ByVal conName As String) As String
            'variable to hold our connection string for returning it
            Dim strReturn As New String("")


            'check to see if the user provided a connection string name

            Try
                If Not String.IsNullOrEmpty(conName) Then
                    'a connection string name was provided
                    'get the connection string by the name provided
                    strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString

                Else
                    'no connection string name was provided
                    'get the default connection string
                    strReturn = ConfigurationManager.ConnectionStrings("YourConnectionString").ConnectionString

                End If
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Connection Handling")
            End Try
            'return the connection string to the calling method
            Return strReturn

        End Function 'End getconnectionstring
        Public Shared Sub HandleConnection(ByVal conn As SqlConnection)
            With conn
                'do a switch on the state of the connection
                Select Case .State
                    Case ConnectionState.Open
                        'the connection is open
                        'close then re-open
                        .Close()
                        .Open()
                        Exit Select
                    Case ConnectionState.Closed
                        'connection is open
                        'open the connection
                        .Open()
                        Exit Select
                    Case Else
                        .Close()
                        .Open()
                        Exit Select
                End Select
            End With
        End Sub
    End Class ' End Data Class
End Namespace 'end PC

And now add another class that will get the users input

Public Class Connection
    Private Shared server As String

    Public Shared Function getserver() As String
        Return server
    End Function
    Public Sub setServer(ByVal Aserver As String)
        server = Aserver
    End Sub

End Class

now on your app.config file

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
        <add name="My DB"
        connectionString= "Persist Security Info=true;
                   Data Source = YourServer;
                   Initial Catalog = YourDatabase;
                   User id = Youruserlogin;
                   Password = Yourpassword;
                   Application Name=YourApplicationName;"
         providerName="System.Data.SqlClient"/>


  </connectionStrings>

</configuration>

you can add more connections strings depends on the type of your project by duplicating <add name.

Now on your Registration form

on the form load event

 Me.cboserver.Items.Clear()

        Dim collect As ConnectionStringSettingsCollection

        Try
            collect = ConfigurationManager.ConnectionStrings
        Catch ex As Exception
            collect = Nothing
        End Try

        If collect IsNot Nothing Then

            For Each cs As ConnectionStringSettings In collect
                Me.cboserver.Items.Add(cs.Name)  '-- this will get all the names found in your appconfig
            Next cs
        End If
        collect = Nothing

now on your register button add this one

 If Me.cboserver.Text = "" Then
            PC.Data.GetConnectionString("YourConnectionString")
            Exit Sub
        End If
        Aserver.setServer(Me.cboserver.SelectedItem.ToString)
        PC.Data.GetConnectionString(Connection.getserver)
        Try
            Dim connect As New SqlConnection(PC.Data.GetConnectionString(Connection.getserver))
            PC.Data.HandleConnection(connect)
            MsgBox("Successfully Registered", MsgBoxStyle.OkOnly, "Success")
            PC.Data.HandleConnection(connect)
            ReportInterface.Show()
            Me.Hide()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Connection Problem")

make sure to call the class connection by adding this one on the top
Public Aserver As New Connection

hope you this one works on your side. happy coding

[Reverend Jim]

Go the the machine on which the code works. On the line of code just prior to opening the connection, add the following line (I'm going to assume your connection object is called conn. If not then replace it with the actual object name)

Debug.WriteLine(conn.ConnectionString)

Post the output of that statement back here. Once we see the string we will be able to show you what parts need to be modified for the other system. You only want to have to enter the parameters once per system so I suggest you use Settings variables to store them between sessions. You will likely need three (four, if the database name is different on the other machine). If you don't know how to create Settings variables there are many examples on this forum. I suggest

ServerName   String
UserName     String
Password     String

You access the values as (for example)

My.Settings.ServerName

Just treat them like any other variable. Check the values at form load and if the values are blank you can prompt the user to enter the appropriate strings. Just make sure you save them back into the My.Settings.ServerName, etc. variables so they get preserved between sessions.

[khair.ullah]

thank you johan , jim and androidz for your sweet reply i got it. Thank all of u very much.

[Muhammad_113]

how i change database path after conpleting my project in vb.net. I want to open data connection by cliking on a button and change my database path then ok save it