在 VB.NET 中的 Access 数据库中执行 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20295284/
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
Executing an SQL query in an Access Database in VB.NET
提问by luke.irvin21
I have already posted one question about this piece of code before lol. But now I am having a different problem and need help with it. Basically this is code for a search button on a Windows Form coded in VB.NET.
我已经在 lol 之前发布了一个关于这段代码的问题。但现在我遇到了不同的问题，需要帮助。基本上这是在 VB.NET 中编码的 Windows 窗体上的搜索按钮的代码。
Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click
Dim cnnOLEDB As New OleDbConnection
Dim cmdOLEDB As New OleDbCommand
Dim strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & System.Environment.CurrentDirectory & "\All Keyed Up, Lock & Safe, LLC.accdb"
cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()
Dim Connection As New SqlClient.SqlConnectionStringBuilder
Connection.DataSource = "file:///C:\Users\thelukester145\Documents\All%20Keyed%20Up\All%20Keyed%20Up,%20Lock%20&%20Safe,%20LLC.accdb"
Dim objSQLConnection = New SqlClient.SqlConnection(Connection.ConnectionString)
Dim cmd As New SqlCommand()
Dim myTable As New DataTable()
Dim SearchFor = SearchBox.Text
If AddressButton.Checked Then
cmd.Connection = objSQLConnection
cmd.CommandText = "SELECT * FROM [McDonald's-Corporate Stores] WHERE [Address] LIKE '%" & SearchFor & "%'"
Dim myAdapter As New SqlDataAdapter(cmd)
myAdapter.Fill(myTable)
DataGrid.DataGridView1.DataSource = myTable
ElseIf NumberButton.Checked Then
cmd.Connection = objSQLConnection
cmd.CommandText = "SELECT * FROM [McDonald's-Corporate Stores] WHERE [McOpCo#] LIKE '%" & SearchFor & "%'"
Dim myAdapter As New SqlDataAdapter(cmd)
myAdapter.Fill(myTable)
DataGrid.DataGridView1.DataSource = myTable
End If
DataGrid.Show()
cnnOLEDB.Close()
End Sub
The database that is attempting to be searched is a database of customers for our family buisness. I am trying to search by either the address or store number of our customer. I've worked through who knows how many fatal errors but the newest one has me sort of stumped. It is crashing on myAdapter.Fill(myTable). The error message it gives is this:
试图搜索的数据库是我们家族企业的客户数据库。我正在尝试按客户的地址或商店编号进行搜索。我已经解决了谁知道有多少致命错误，但最新的一个让我有点难倒。它在 myAdapter.Fill(myTable) 上崩溃。它给出的错误信息是这样的：
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
System.Data.dll 中发生类型为“System.Data.SqlClient.SqlException”的未处理异常
Additional information: 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: 25 - Connection string is not valid)
附加信息：建立与 SQL Server 的连接时发生与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确以及 SQL Server 是否配置为允许远程连接。（提供程序：SQL 网络接口，错误：25 - 连接字符串无效）
Any help would be much appreciated :)
任何帮助将非常感激 ：）
回答by Steve
Well, you are freely mixin OleDbclasses with SqlClientclasses. The two are for different database types. OleDb is needed for Access, SqlClient classes are used for Sql Server.
好吧，您可以自由地将OleDb类与SqlClient类混合。两者适用于不同的数据库类型。Access 需要 OleDb，Sql Server 使用 SqlClient 类。
So you need to change all the classes SqlCommand, SqlCommandBuilder, SqlDataAdapter,SqlConnectionStringBuilder with the corresponding classes OleDbCommand, OleDbCommandBuilder, OleDbDataAdapter, OleDbConnectionStringBuilder
因此，您需要将所有类 SqlCommand、SqlCommandBuilder、SqlDataAdapter、SqlConnectionStringBuilder 更改为相应的类 OleDbCommand、OleDbCommandBuilder、OleDbDataAdapter、OleDbConnectionStringBuilder
Infact, the error message is emitted by the SqlClient library that tries to use your connection string as it was a connectionstring for an SQL Server database and, of course, it fails to find it.
事实上，错误消息是由 SqlClient 库发出的，该库尝试使用您的连接字符串，因为它是 SQL Server 数据库的连接字符串，当然，它无法找到它。
Also, I am not sure about that, but probably the DataSource property doesn't need the prefix file:///and the Encoding of spaces with %20
另外，我不确定，但可能 DataSource 属性不需要前缀file:///和空格编码%20
