无法使用VB.NET动态连接到SQL Server数据库 [英] Can't dynamically connect to SQL Server database using VB.NET

问题描述

我正在尝试使用数据库应用程序，但是遇到了麻烦.我正在VB.NET 2008中编写它.
在应用程序中，我有一个函数，该函数应在运行时为MS-Access数据库或SQL Server数据库动态创建连接字符串.
连接字符串构建器功能:

I’m experimenting with a database application and I have run into a snag. I’m writing it in VB.NET 2008.
In the application I have a function that is suppose to dynamically create a connection string for either an MS-Access database or a SQL Server database at runtime.
Connection string builder function:

Public Function cnnDataBase(ByVal strDBType As String, _
                            ByVal strDBName As String, _
                            Optional ByVal strServer As String = "") As String

   Select Case UCase(strDBType)
      Case "OLEDB"
          Dim cnnOLEDB As New OleDbConnectionStringBuilder
          cnnOLEDB.Provider = "Microsoft.Jet.OLEDB.4.0"
          cnnOLEDB.DataSource = strDBName
          Return cnnOLEDB.ConnectionString
      Case "SQL"
          Dim cnnSQL As New SqlConnectionStringBuilder
          'This section is SUPPOST to connect to the
          'AdoStepByStep_Data.MDF SQL Server file, but it fails
          cnnSQL.DataSource = "(local)\SQLEXPRESS" 'strServer
          cnnSQL.InitialCatalog = "AdoStepByStep_Data.MDF"
          cnnSQL.IntegratedSecurity = True
          'This connection string will let the application
          'connect to the AdoStepByStep database file
          'Dim strCnWorks As String = "Data Source=(local)\SQLEXPRESS;" & _
          '                            "Initial Catalog=AdoStepByStep;" & _
          '                            "Integrated Security=True"
          Return cnnSQL.ConnectionString
          'Return strCnWorks
      Case Else
          Return Nothing
      End Select
End Function

目前，我正在对这些值进行硬编码，以找出无法正常工作的原因.实际上，该函数接收三个参数: strDBType变量是一个字符串值，指示我要建立的连接类型是MS-Access(OLEDB)还是SQL Server(SQL). strDBName变量是我要连接的数据库的文件名.我正在从另一个函数内部通过OpenFileDialog类检索此值.最后一个变量是SQL Server名称和实例，这是我的桌面和SQLEXPRESS.
OLEDB部分工作正常，但SQL Server部分导致我溃疡.一整天玩了之后，我发现了一个有趣的发现.我用于与AdoStepByStep进行测试的初始目录文件是使用存储在Microsoft Press SampleDBs文件夹中的ConfigDB应用程序创建的.我不知道此应用程序的功能，但是它以某种方式将AdoStepByStep_Data.MDF主SQL Server数据库文件压缩为连接对象可以识别的文件. OLEDB端可以正常工作，因为它使用Access JET引擎.

当我将初始目录更改为AdoStepByStep_Data.MDB时，程序将引发异常:无法通过登录请求打开" AdoStepByStep_Data.MDB".登录失败.我看到的唯一区别是，这是SQL Server主数据库文件，并且设置为"AdoStepByStep"的初始目录有效.我唯一能想到的就是AdoStepByStep，它已经以某种方式转换为ConnectDB应用程序可以使用的东西.

现在的问题是:我必须怎么做才能直接访问.MDF文件以便创建数据集?

谢谢
Quecumber256

Currently I’m hard coding in these values to isolate why it isn’t working. In practice the function receives three parameters; The strDBType variable is a string value indicate what kind of connection I want to build, either a MS-Access(OLEDB) or SQL Server(SQL). The strDBName variable is the file name of the database I want to connect to. I am retrieving this value through the OpenFileDialog class from inside another function. The last variable is the SQL Server name and instance, which is my desktop and SQLEXPRESS.
The OLEDB part works fine, but the SQL Server part is causing me ulcers. After playing with it all day I have made an interesting discovery. The Initial catalog file I was using to test with AdoStepByStep was created with the ConfigDB application stored on the Microsoft Press SampleDBs folder. I don’t know what this application did, but it somehow massaged the AdoStepByStep_Data.MDF primary SQL Server database file into something the connection object recognized. OLEDB side works okay because it uses the Access JET Engine.

When I change the Initial Catalog to AdoStepByStep_Data.MDB the program throws the exception: "Cannot Open ''AdoStepByStep_Data.MDB'' requested by login. The Login failed." The only difference I see is that this one is the SQL Server Primary database file and the Initial Catalog that is set to "AdoStepByStep" works. The only thing I can come up with is the AdoStepByStep one was somehow transformed into something usable by the ConnectDB application.

Now the question: What do I have to do so I can access the .MDF file directly so I can create DataSets?

Thank you,
Quecumber256

推荐答案

问题中的代码段给出了答案.

正如Wes Aday在评论中所说:

更改:

The Code snippet in the question has the answer in it.

As Wes Aday said in the comments:

Change:

cnnSQL.InitialCatalog = "AdoStepByStep_Data.MDF"

更改为:

cnnSQL.InitialCatalog = "AdoStepByStep"

如果这不能解决您的问题，请发布对建议的回复.

关于这个问题:我必须怎么做才能直接访问.MDF文件以便创建数据集?

您无法直接"访问.MDF文件，必须先将数据库安装到SQL Server并使用连接字符串访问该数据库以附加到SQL Server，然后才能创建数据集.

If this does not solve your problem, please post a response to the suggestions.

As for the question: What do I have to do so I can access the .MDF file directly so I can create DataSets?

You can not access the .MDF file "Directly", you must mount the database to an SQL Server and access the database using a connection string to attach to the SQL server before you can create datasets.

这篇关于无法使用VB.NET动态连接到SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！