Problems using SqlConnection

[Mika M]

When my application uses OleDb-provider like...

Private Sub TryOleDbConnection()
Try
Dim i As Integer

Using cn As OleDbConnection = New OleDbConnection()
cn.ConnectionString =
"Provider=sqloledb;MYSERVERNAME\MYINSTANCE;Password=xxx;User
ID=sa;Initial Catalog=MYDBNAME"

Using cm As OleDbCommand = New OleDbCommand()
cm.Connection = cn
cm.CommandText = "SELECT COUNT(*) AS X FROM MYTABLE"
cn.Open()
i = CType(cm.ExecuteScalar, Integer)
cn.Close()
End Using
End Using

MessageBox.Show(String.Format("answer={0}", i))

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub


....It's working fine. BUT when my application uses Sql-provider similar
way like...


Private Sub TrySqlConnection()
Try
Dim i As Integer

Using cn As SqlConnection = New SqlConnection()
cn.ConnectionString = "Data
Source=MYSERVERNAME\MYINSTANCE;password=XXX;User ID=sa;Initial
Catalog=MYDBNAME;"

Using cm As SqlCommand = New SqlCommand()
cm.Connection = cn
cm.CommandText = "SELECT COUNT(*) AS X FROM MYTABLE"
cn.Open()
i = CType(cm.ExecuteScalar, Integer)
cn.Close()
End Using
End Using

MessageBox.Show(String.Format("answer={0}", i))

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

cn.Close()-line fails with Error Message:

"An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)"

Database is SQL Server 2005 Express running on other (WinXP Pro UK + SP2
+ latest updates) computer. I think some setting(s) is incorrect in SQL
Server settings, but what might it be?

I can't connect this database on other computer using Microsoft SQL
Server Management Studio Express of my (client) computer using Sql
Server authentication with user name and password. Using Microsoft SQL
Server Management Studio Express from localhost where the database is is
working fine.

 

[William \(Bill\) Vaughn]

I wrote a blog entry on this some time ago to answer just this sort of
question see:
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

--
William (Bill) Vaughn
President and Founder Beta V Corporation
Redmond, WA
(425) 556-9205
Microsoft MVP, Author, Mentor
Microsoft MVP