DSN-less connection to SQL server in VB.NET

Question

Tried seemingly everything here to get this to work but I keep getting "Keyword not supported" errors for just about every iteration of dsn-less connection strings I can find out there in internet land, two are shown below.

Public cnSystem As New SqlClient.SqlConnection

Public Sub ConnectToSQL()

    Dim sConnectionString As String
    Dim sServer As String

    Try
        'Always connect to production server to get startup environment variables
        If gbIsProduction Then
            If gsProductionServer = "" Then
                sServer = "xxxxx-SQL"
            Else : sServer = gsProductionServer
            End If
        Else : sServer = gsDevelopmentServer
        End If
        //Doesn't work
        sConnectionString = "Network Library=DBMSSOCN;Data Source=xxxxx-SQL,1433;Inital Catalog=xxxxx;User ID=sa;Password=xxxxx;"
        //Doesn't work
        sConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;UserId=sa;Initial Catalog=xxxxx;Data Source=xxxxx-SQL;Password=xxxxx;"  
        cnSystem.ConnectionString = sConnectionString
        cnSystem.Open()
        cmdSystem.Connection = cnSystem
    Catch ex As Exception
        RaiseError("", "modGeneral." & System.Reflection.MethodBase.GetCurrentMethod().Name, Err.Number, Err.Description)
    End Try

End Sub

Any ideas on what is the proper connection string for a DSN-less connection to a SQL server using the data objects I am using?

Thanks!

Answer 1

try creating a udl file to create your connection string. then you can test it to ensure it is working - http://www.codeasp.net/blogs/hajan/microsoft-net/857/working-with-udl-universal-data-link-files

Answer 2

While not the exact answer, this website helps me all the time:

http://www.connectionstrings.com/

Also, when using System.Data.SQLClient you do not have to specify the provider and I believe you will get the error you are receiving. Remove that part.