How do I connect to SQL Server with VB?

Question

I'm trying to connect to a SQL server from VB. The SQL server is across the network uses my windows login for authentication.

I can access the server using the following python code:

import odbc
conn = odbc.odbc('SignInspection')
c = conn.cursor()
c.execute("SELECT * FROM list_domain")
c.fetchone()

This code works fine, returning the first result of the SELECT. However, I've been trying to use the SqlClient.SqlConnection in VB, and it fails to connect. I've tried several different connection strings but this is the current code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim conn As New SqlClient.SqlConnection
    conn.ConnectionString = "data source=signinspection;initial catalog=signinspection;integrated security=SSPI"
    Try
        conn.Open()
        MessageBox.Show("Sweet Success")
        ''#Insert some code here, woo
    Catch ex As Exception
        MessageBox.Show("Failed to connect to data source.")
        MessageBox.Show(ex.ToString())
    Finally
        conn.Close()
    End Try

End Sub

It fails miserably, and it gives me an error that says "A network-related or instance-specific error occurred... (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'm fairly certain it's my connection string, but nothing I've found has given me any solid examples (server=mySQLServer is not a solid example) of what I need to use.

Thanks! -Wayne

Answer 1

You are using an ODBC DSN as a SqlClient server name. This is not going to work. You have to use a SqlClient connection string, and for SqlClient the DataSource property is the server name or a SQL Native Client server alias (which is not the same as an ODBC DSN).

Replace signinspection with the actual name of your SQL Server host. If is a named instance or listening on a non default port, you have to specify that too, eg: hostname\instancename