Tutorial :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


Solution: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


Solution: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.


Solution:3

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.


Solution: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 columnCount - 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.

ed. columsCount -> columCount


Solution: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! =)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »