vb.net VB2010：连接到 SQL Server 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8017845/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB2010: Connect to SQL Server 2008
提问by Rick
I have a database on SQL Server 2008 and am trying to run code on Visual Basic 2010 to connect to it. I have the following code but am getting the error A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)on the line SQLConn.Open():
我在 SQL Server 2008 上有一个数据库，我正在尝试在 Visual Basic 2010 上运行代码以连接到它。我有以下代码，但A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)在线上出现错误SQLConn.Open()：
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim SQLConn As SqlClient.SqlConnection
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim i As Integer
Dim connectionstring As String
connectionstring = "Data Source=MySQLServer\MyInstance;Database=MyDatabase;Integrated Security=true;"
Try
SQLConn = New SqlConnection(connectionstring)
SQLConn.Open()
Catch ex As Exception
MsgBox(ex.Message & " Error Connecting to database!", MsgBoxStyle.Critical)
Exit Sub
End Try
Dim da As SqlDataAdapter
da = New SqlDataAdapter("SELECT * from DR_Users", SQLConn)
Dim dt As DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
Dim dr As DataRow = dt.Rows(i)
Debug.Print(dr.Item("UserId").ToString)
Next
End Sub
End Class
Edit: I have been working on VBA code and know the server\instance and database names are correct. Not sure why it's not working under VB.NET 2010.
编辑：我一直在研究 VBA 代码并且知道服务器\实例和数据库名称是正确的。不知道为什么它不能在 VB.NET 2010 下工作。
回答by Amegon
first step is to install "SQL Server management studio". If you can connect to your sql server with it, the nyou know that the server is accessible. otherwise you need to fix firewall, start sql server, etc...
第一步是安装“SQL Server management studio”。如果您可以使用它连接到您的 sql 服务器，则您知道该服务器是可以访问的。否则你需要修复防火墙，启动sql server等......
About the connection string: I prefer to "build" it on-the-fly, maybe that works for you, too
关于连接字符串：我更喜欢即时“构建”它，也许这也适合你
Public Property ServerName() As String
Public Property DatabaseName() As String
Public Property Login() As String
Public Property Password() As String
Private Function SqlConn(Optional timeout As Integer = 0) As String
' Initialize the connection string builder for the
' underlying provider.
Dim sqlBuilder As New SqlClient.SqlConnectionStringBuilder()
' Set the properties for the data source.
sqlBuilder.DataSource = _serverName
sqlBuilder.InitialCatalog = _databaseName
sqlBuilder.IntegratedSecurity = False
sqlBuilder.MultipleActiveResultSets = True 'to avoid exception if a query uses anothe rquery internal
sqlBuilder.UserID = _Login
sqlBuilder.Password = _Password
If timeout > 0 Then
sqlBuilder.ConnectTimeout = timeout
End If
Return sqlBuilder.ToString
End Function
so you can write
所以你可以写
Using sqlConn As New SqlClient.SqlConnection(sqlConnString)
sqlConn.Open()
[...]
sqlConn.Close()
End Using
for server name, use the name that worked to connect to with Server Management Studio, same with Login and Password. Database = the one you can see when connected.
对于服务器名称，使用与 Server Management Studio 连接的名称，与登录和密码相同。数据库 = 连接时您可以看到的那个。
And if you use Windows credential, you can keep usign IntegratedSecurity. But I guess for programs it makes more sense to be a bit more independent, so just setup a normal sql login.
如果您使用 Windows 凭据，则可以继续使用 IntegratedSecurity。但我想对于程序来说，更独立一点更有意义，所以只需设置一个普通的 sql 登录即可。
回答by competent_tech
Possible causes:
可能的原因：
1) The name of the database server is not MySQLServer. If it is your local machine, change this to . or (local) or the name of your machine.
1) 数据库服务器的名称不是 MySQLServer。如果是您的本地机器，请将其更改为 . 或（本地）或您机器的名称。
2) Either SQL Server is not running in an instance or the instance is not named MyInstance.
2) SQL Server 未在实例中运行或该实例未命名为 MyInstance。
3) The SQL Server service is not actually running. Try starting it.
3) SQL Server 服务实际上并未运行。尝试启动它。
4) SQL Server is separated from the machine you are running this code on by a firewall which is not letting the SQL traffic through.
4) SQL Server 与运行此代码的机器被防火墙隔开，防火墙不允许 SQL 流量通过。
Update:
更新：
5) The local SQL Server Client Network Utility either is not configured to support the protocols that SQL Server does or has an old alias that points to the wrong machine or address. To open this utility, you can run cliconfg from the Run... prompt or the command line.
5) 本地 SQL Server 客户端网络实用程序要么未配置为支持 SQL Server 执行的协议，要么具有指向错误计算机或地址的旧别名。要打开此实用程序，您可以从 Run... 提示符或命令行运行 cliconfg。
Update 2:
更新 2：
This blog entryhas an excellent explanation of exactly how to troubleshoot this specific issue.
这个博客条目对如何解决这个特定问题有一个很好的解释。
