Clever Geek Handbook

Method / property error on new SQL Server connection

I am running VB.Net in VS2013, on Windows 8 over SQL Server 2008 R2, and my SQL connection creation fails with the error:

Property access must be assigned 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 passed to sub and has a runtime value "SVRSUN07"

.

The error occurs at the line:

oCnn = New SqlConnection(sCnn)

So, at runtime, sCnn contains:

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

I removed this connection string from the .udl file that returns successfully when I test the connection.

I can run SQLSMS over this database.

+3


source to share


1 answer


Below is an ASCII diagram that shows how the components are related to each other. You are trying to use 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 native SQL OLEDB provider, you can use OleDbConnection

. This can be useful if you want your VB.NET code to be flexible and be able to connect with other OLEDB providers like Access, Postgres, Mysql, etc.

However, if you know for sure that you will only connect to SQL Server, it is easier to use SqlConnection

instead of a connection string, for example "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SunSystemsData;Data Source=SVRSUN07"

.

+1


source






More articles:


All Articles