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.

Couple of things.. If vsSunServer is string then in If compare it against String.empty instead <>. Second try adding '' single quote to vsSunServer like "Data Source='" & vsSunServer &"'" — Amnesh Goel
– Amnesh Goel, Commented May 15, 2015 at 12:27
I can't match that error message (its a compiler error, right, rather than a runtime one, despite you talking about runtime values) with the line you've identified as causing it. Have you tried re-starting (Visual Studio, and if that fails, your machine) and re-loading the code? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 15, 2015 at 12:31
Amnest - I've tried both those suggestions, but get the same error. (So connection string now reads Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SunSystemsData;Data Source='svrsun07' . — Simon Wray
– Simon Wray, Commented May 15, 2015 at 12:38
That's a compiler error - you either get it when you compile the code, or you're getting it from the VS Debugger because you've got some expression in a debug window (such as immediate) that it can't compile. Either way, it's not coming, specifically, from that line of code you've identified. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 15, 2015 at 12:39

Jared Moore · Accepted Answer · 2015-10-20 21:27:59Z

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".

Collectives™ on Stack Overflow

Method/Property Error on New SQL Server Connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related