Method/Property Error on New SQL Server Connection

Question

I'm running VB .Net in VS2013, on a Windows 8 over SQL Server 2008 R2, and my creation of an SQL Connection is failing with the error:

Property access must assign to the property or use its value.

Here's my code:

Dim oCnn As SqlConnection Dim sCnn As String Dim bSunCnnOK as Boolean

Try
    If vsSunServer <> "" Then
        sCnn = "Provider=SQLOLEDB.1;" & _
               "Integrated Security=SSPI;" & _
               "Persist Security Info=False;" & _
               "Initial Catalog=SunSystemsData;" & _
               "Data Source=" & vsSunServer
        oCnn = New SqlConnection(sCnn)
        oCnn.Open()
        bSunCnnOK = True
    End If
Catch ex As Exception
    bSunCnnOK = False
End Try

_vsSunserver_ is a string being passed in to the sub, and has a run-time value of "SVRSUN07".

The error is being raised on the line:

oCnn = New SqlConnection(sCnn)

So at run-time, sCnn holds:

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SunSystemsData;Data Source=SVRSUN07"

I've lifted this connection string from a .udl file, which returns successful when I Test Connection.

I can run SQLSMS over this database OK.

Answer 1

Below is an ASCII art diagram that shows how the components related to each other. You are trying to use a SQL OLEDB connection string with SqlConnection. These things don't go together.

C#/VB.NET code -┬------------> SqlConnection -----------------┬-> SQL Server
                |                                             |
                ├--> OleDbConnection -┬-> SQL OLEDB provider -┤
                |                     |                       |
                |       Native code --┤                       |
                |                     |                       |
                └-> OdbcConnection ---┴-> SQL ODBC driver ----┘

If you really want to use the SQL OLEDB native provider then you can use OleDbConnection. This might be useful if you want your VB.NET code to be flexible and possible connect to other OLEDB providers such as Access, Postgres, Mysql, etc.

However if you know for sure that you will only be connecting to SQL Server then it will be easier to use SqlConnection instead with a connection string like "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SunSystemsData;Data Source=SVRSUN07".