How do I connect to SQL Server with VB?

Question

Hi, 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

Sure your Server and Database have the same name?

Here you have a link that would allow you to generate a connection string and test it

http://blogs.msdn.com/dhejo_vanissery/archive/2007/09/07/One-minute-Connection-string.aspx.

Answer 2

Check out connectionstrings.com for samples. It looks like in your python example, you are accessing the DB via ODBC.

The string you are using is connecting with the built in .NET SQL Server DB provider, so you need to use an ODBC connection string OR change your data source to the actual server name (if no other instances) or servername/instance name.

Answer 3

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

Answer 4

Well, I went ahead and used an ODBC Connection. It appears that that is what I was wanting in the first place.

In order to do use the ODBC I had to go to http://support.microsoft.com/kb/310985 and install a few files. Following the directions I came up with the following code that seems to work just fine:

Dim conn As OdbcConnection
conn = New OdbcConnection("DSN=SignInspection")
Dim mystring as String = "SELECT * FROM list_domain"
Dim cmd As OdbcCommand = New OdbcCommand(mystring, conn)
Dim reader As OdbcDataReader
Dim columnCount As Integer
Dim output As String
Dim data as Object() = New Object(10) {}
conn.Open()
MsgBox("Connected!")
reader = cmd.ExecuteReader()
While reader.Read()
    columnCount = reader.GetValues(data)
    output = ""
    For i As Integer = 0 To columnsCount - 1
        output = output & " " & data(i).ToString()
    Next
    Debug.WriteLine(output)
End While
conn.Close()

Of course I'll have it cleaned up a lot, but I figure maybe someone will end up looking for the same solution, and maybe they'll see my code before they spend too much time.

Answer 5

You might want to take a look on Microsoft Enterprise Library Data Access Application Block in order to make it easier to connect and to support multiple underlying datastores.

Good success! =)