How to connect to SQL Server using VB?

Question

I am trying to connect to SQL server from VB. An SQL server across the network uses my 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 SELECT result. However, I am trying to use SqlClient.SqlConnection in VB and it cannot connect. I tried several connection lines, 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, and it gives me the error message "An error occurred with the network or a specific instance ... (provider: named pipe provider, error: 40 - Could not open connection to SQL Server)

I am sure this is my connection string, but nothing I found gave me any solid examples (server = mySQLServer is not a very good example) of what I need to use.

Thanks! -Wayne

Answer 1

You are using ODBC DSN as the SqlClient server name. This will not work. You must use the SqlClient connection string, and for SqlClient, the DataSource property is the server name or alias of the SQL server of your own client (which does not match the ODBC ODN).

Replace signinspection with the actual name of your SQL Server host. If it is a named instance or listening on a non-standard port, you should also specify it, for example: hostname\instancename

Answer 2

Check connectionstrings.com for samples. It looks like in your python example, you are accessing the database through ODBC.

The string you are using connects to the built-in .NET SQL Server database provider, so you need to use the ODBC connection string OR change the data source to the actual server name (if there are no other instances) or server name / instance name.

Answer 3

Are you sure your server and database have the same name?

Here you have a link that allows you to create a connection string and test it

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

Answer 4

Ok, I went ahead and used ODBC Connection. It seems that is exactly what I wanted.

To use ODBC, I had to go to http://support.microsoft.com/kb/310985 and install several files. Following the instructions, I came up with the following code, which seems to work very well:

 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 columnCount - 1 output = output & " " & data(i).ToString() Next Debug.WriteLine(output) End While conn.Close() 

Of course, I will have a lot of it, but I think that someone will eventually find the same solution, and maybe they will see my code before they spend too much time.

Ed. columsCount → columCount

Answer 5

You might want to take a look at the Microsoft Enterprise Library Data Access Application Block to simplify the connection and support of several basic data stores.

Good success! =)