conectionstring in vb.net

[khair.ullah]

how to code vb.net
connection string support
to all computers? normally we are puting
connction string like this.
con = New SqlConnection
("Server=REHAN-PC
\SQLEXPRESS;Initial
Catalog=library;Integrated Security=true")
con.Open()

but the thing is when we are
going to put this application
to another computer we
have a trouble. Now what to do? . How solve this problem. thanks for reading.hope your help.

[AleMonteiro]

I think the best solution is to put the connection string in the app.config file under the <connectionString> section.

Then, use the name of the connection string as the name of the machine.

Then, in the code, get the connection string by the name of the machine.

[Reverend Jim]

Can you be more specific as to what kind of trouble you are having? If it is a connection problem related to security you may have to set up a database account with a userid and password and change your connection string to use those.

[AleMonteiro]

Reverend Jim, what I understood is that his connection string is hard-coded, so he has to change and re-compile each time he needs to change the connection string.

[Ancient Dragon]

You might put the connection string in an app.config file so that it can be customized for each computer on which the program is running without recompiling the program.

[Reverend Jim]

I would also imagine his problem is with security. His current connection string uses trusted security (based on the current logged in user). If the database is on another computer then the current security model will likely not allow the user to connect.

So, basically two problems.

[TnTinMN]

I believe the OP is concerned about getting the proper SQL Express server name on an unknown installation.

The following code works fine with SQL Express running on the machine. I have never tested it to see if it finds a Network server. When I still had SQLExpress 2005 and 2008 installed, it found both instances.

   Dim csb As New SqlClient.SqlConnectionStringBuilder
   csb.IntegratedSecurity = True
   csb.UserInstance = True

   ' This next line can take a few seconds to complete
   Dim dtSQLServers As DataTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()

   If dtSQLServers.Rows.Count > 0 Then ' Take 1st entry.  Could loop through all entries if needed
      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 = ServerName & "\" & InstanceName
      Catch ex As Exception
         MsgBox(ex.Message)
         Exit Try
      End Try
   End If 'dtSQLServers.Rows.Count > 0

   If csb.DataSource IsNot Nothing Then ' List the available database
      Dim conn As New SqlClient.SqlConnection(csb.ConnectionString)
      Dim cmd As New SqlClient.SqlCommand("Select name From sys.databases", conn)
      Dim da As New SqlClient.SqlDataAdapter(cmd)
      Dim tables As New DataTable
      da.Fill(tables)

      For Each dr As DataRow In tables.Rows
         Console.WriteLine(dr(0).ToString)
      Next dr
   End If

[rbcontra]

Problem is on your instent name(REHAN-PC\SQLEXPRESS).When your connecting to SQL Server Management Studio, do not use your PC name.use only .\SQLEXPRESS.

Now you can use your solution explorer to ge tyour connectionstring.

[TnTinMN]

Problem is on your instent name(REHAN-PC\SQLEXPRESS).When your connecting to SQL Server Management Studio, do not use your PC name.use only .\SQLEXPRESS.

You are assuming that the Instance Name is SQLExpress. This may not be the case.

[rbcontra]

if you use Instance Name as SQLExpress. your Connectionstring will be

Data Source=.\sqlexpress;Initial Catalog=a9spot;Integrated Security=True

this Connectionstring will work on any computer. I had khair.ullah's probles too.

[AleMonteiro]

But what if the database is not on the same machine?

That's why it's always better to have the connection string in the app.config.

In my opnion, the best way to use connection string is:
On app.config:

<connectionStrings>
    <add name="machine-name" connectionString="your connection string" />
</connectionStrings>

And in the code:

string hostName = Environment.MachineName.ToLower();

string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ hostName ].ConnectionString;

This way multiple developers can use the same config file(in case you use SVN) and also the deploy connection string, without having to change it every time.

This works if the DB is on the same machine, on the network or even on the internet. With no need to re-compile.

This can be used on windows forms and also asp.net web applications.

[khair.ullah]

thanks all

[imti321]

Hi , AleMontero
Please tell me how to change this line according to your settings so thAT I CAN INSTALL IT ON ANOTHER COMPUTER .i TRIED TO INSTALL MY APPLICATION ON ANOTHER COMPUTER USING WINDOWS 7 64 BIT.tHE APPLICATION WORKS FINE ON MY COMPUTER USING 32 BIT VINDOWS 7 .

<connectionStrings>
    <add name="Model1Container" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

[imti321]

I DONNOT USE ANY DATA CONNCETION IN MY APPLICATION.I CONNECT TO EXCEL THROUGH CODE .

[Reverend Jim]

If you are not using a data connection (?) then why are you using a connection string and why does it specify SQLEXPRESS? How can it possibly work on your computer? Can you be more specific than

I CONNECT TO EXCEL THROUGH CODE.

And can you please not post in CAPS? Also, for future reference, please do not hijack a thread. Your question belongs in a new thread.