打开数据库时遇到问题 [英] Having a problem with opening a database

问题描述

我是一个平庸的Visual Basic程序员/业余爱好者。我写了一个程序，对我的DVD电影收藏进行编目。到目前为止，该计划运作良好，足以满足我的需求。该集合已经达到了这样的程度，即搜索特定电影充其量是乏味的，所以我开发了一个程序，它将搜索标题名称的一部分和/或演员名称的一部分。该程序本身运行良好，但当我尝试合并到主程序时，我收到一个错误，指出它无法打开数据文件。



我正在使用VB 2013 Express和SQL 2014 Express，并且在开发开始时通过数据源菜单连接数据文件。它打开，构建数据集并关闭。主程序一次显示一条记录数据，允许编辑记录，添加新记录和删除记​​录，以便所有内容按设计工作。当我请求搜索时，它会打开搜索表单，允许输入，构建连接字符串并尝试打开数据库。这是我收到错误的地方，

系统中发生了'System.Data.SqlClient.SqlException'类型的未处理异常.Data.dll 
附加信息：无法打开登录请求的数据库D：\ Data Files \DVD_List.mdf。登录失败。
用户'DarbysDen\Darby'登录失败。

我检查了命令的准确性，命令语法和它们都是正确的。我唯一能想到的是数据库仍在主程序中打开。这是可能的，如果是这样，我可以使用不同的select命令创建一个额外的数据集来获取过滤后的信息吗？我在下面列出了搜索项目代码。



注意：'frmSearch'是指我们选择搜索条件的用户界面。用户可以输入标题标准和/或演员标准和/或电影评级标准，并将其传递给sqlForm ..

  Imports  System.Data 
  Imports  System.Data.SqlClient 
  Public  类 sqlForm 
  Dim  objDataSet 正如 新 DataSet（）
  Dim  objConnection 作为 SqlConnection = 新 SqlConnection（  server =& 
   DARBYSDEN \SQLEXPRESS14; & 
   database = D：\ Data Files \DVD_List.mdf; & 
   Trusted_Connection = True）
 < span class =code-keyword> Dim  objDataAdapter  As  新 SqlDataAdapter（）
  Dim  srchString  As   String  = < span class =code-string>  
  Dim  frmDialog < span class =code-keyword> As   New  frmSearch 
 
  Private   Sub  sqlForm_Load（sender  As   Object ，e  As  EventArgs）句柄  MyBase  .Load 
 
 如果 frmDialog.ShowDialog = Windows.Forms.DialogResult.Cancel 那么 
 ' 如果用户按取消取出 
 srchString =    
 MsgBox（ 取消按下）
 ' 清理它 
 objDataAdapter = 没什么 
 objConnection = 没什么 
 frmSearch =  Nothing  
关闭（）
 退出  Sub  
  ElseIf  Windows.Forms.DialogResult.OK 然后 
 
 ' 添加SELECT命令和 
 ' 选择要填写的字段在GridView中显示 
 
 srchString =   
 
 ' 如果用户输入了标题，则将其添加到srchString  
 如果 frmSearch.tbTitle<>   然后 
 srchString =   SELECT Id，Title，Cast，Rated，Book，Page，Slot& 
   FROM DVD_List WHERE Title LIKE'％& 
 frmSearch.tbTitle&  ％' 
 ' 如果用户还提交了一些Cast信息，请将其添加到srchString  
 如果 frmSearch.tbCast <>   然后 
 srchString = srchString&   AND Cast LIKE'％& 
 frmSearch.tbCast&  ％' 
 结束 如果 
 
 ' 如果用户还输入评级，将其添加到srchString  
 如果 frmSearch.tbRate<>   然后 
 srchString = srchString&   AND RATED =& 
  & frmSearch.tbRate&   
 结束 < span class =code-keyword>如果 
 
  ElseIf  frmSearch.tbTitle =   和 frmSearch.tbCast<>   然后 
 
 ' 如果用户未输入任何标题信息但确实输入了演员信息 
 ' 像这样启动srchString  
 srchString =   SELECT Id，Title，Cast，Rated，Book，Page，Slot& 
   FROM DVD_List WHERE Cast LIKE'％& 
 frmSearch.tbCast&  ％' 
 结束 如果 
 如果 frmSearch.tbRate<>   然后 
 srchString = srchString&   AND Rated =& 
  & frmSearch.tbRate&   
 结束 < span class =code-keyword>如果 
  ElseIf  frmSearch.tbTitle =   和 frmSearch.tbCast =   
 和 frmSearch.tbRate<>   然后 
 srchString =   SELECT Id，Title，Cast，Rated，Book，Page，Slot& 
   FROM DVD_List WHERE Rated =&  & frmSearch.tbRate&   
 结束 < span class =code-keyword>如果 
 
 objDataSet.Clear（）
 ' 设置SELECT命令属性 
 objDataAdapter.SelectCommand = 新 SqlCommand（）
 objDataAdapter.SelectCommand.Connection = objConnection 
 objDataAdapter.SelectCommand.CommandText = srchString 
 objDataAdapter.SelectCommand.CommandType = CommandType.Text 
 
 尝试 
 ' 打开数据库连接 
 objConnection.Open（）.....这 IS 我在哪里 GET  错误 
  Catch  ex  As  Exception 
 MessageBox.Show（ex.Message）
 结束 尝试 
 
 ' 用数据填充数据集对象 
 objDataAdapter.Fill（objDataSet，  DVDList ）
 
 ' 关闭数据库连接 
 objConnection.Close（）
 
 ' 如果没有任何内容。然后告诉用户并 
 ' 关闭商店 
 < span class =code-keyword>如果 objDataSet.Tables（  DVD_List） .Rows.Count< =  0  然后 
 MsgBox（ 找不到记录，再试一次）
 
 ' 清理 
 srchString =    
 objDataAdapter = 没什么 
 objConnection = 没什么 
 frmSearch =  Nothing  
关闭（）
 退出  Sub  
 结束 如果 
 
 ' 设置DataGridView属性以将其绑定到我们的数据 
 grdvwDVD_List.AutoGenerateColumns = < span class =code-keyword> True  
 grdvwDVD_List.DataSource = objDataSet 
 grdvwDVD_List.DataMember =   DVD_List 
 
 ' 设置列名称和大小 
 grdvwDVD_List.Columns（ 0 ）。HeaderText =   ID 
 grdvwDVD_List.Columns（ 0 ）。Width =  40  
 grdvwDVD_List.Columns（ 1 ）。HeaderText =  标题 
 grdvwDVD_List.Columns（ 1 ）。宽度=  250  
 grdvwDVD_List.Columns（ 2 ）。HeaderText =  施放 
 grdvwDVD_List.Columns（ 2 ）。宽度=  550  
 grdvwDVD_List.Columns（ 3 ）。HeaderText =  已评级 
 grdvwDVD_List.Columns（ 3 ）。宽度=  50  
 grdvwDVD_List.Columns（ 4 ）。HeaderText =   Bk 
 grdvwDVD_List.Columns（ 4 ）。宽度=  35  
 grdvwDVD_List.Co lumns（ 5 ）。HeaderText =   Pg 
 grdvwDVD_List.Columns（ 5 ）。宽度=  40  
 grdvwDVD_List。列（ 6 ）。HeaderText =   Sl 
 grdvwDVD_List.Columns（ 6 ）。宽度=  35  
 
 ' 清理 
 objDataAdapter = 没什么 
 objConnection = 无 
 结束  Sub  
 结束 类 

解决方案

如果想要附上数据库，您应该使用不同的连接字符串。尝试：

  Dim  objConnection  As  SqlConnection = 新 SqlConnection（  server = & 
   DARBYSDEN \SQLEXPRESS14;& 
   attachbbfilename = D：\ Data Files \DVD_List.mdf; database = mydbname;& 
   Trusted_Connection = True）

麦克;使用你的建议我得到相同的错误信息，（纠正'bb'到'db'）。我'假设''mydbname'可以用数据库名称替换它，所以输入'DVD_List.mdf'仍然得到相同的消息。



更新2015年7月21日。迈克，我撒了谎，你的建议确实奏效了。在做了一些挖掘之后，我在代码中发现了一个问题。然后寻找你的代码的一些澄清，我发现你的建议正是我需要的。非常感谢你。接下来的添加就是能够点击gridview中的记录并在原始屏幕上完整显示。那是另一天。

I am a mediocre Visual Basic programmer/hobbyist. I have written a program that catalogues my DVD movie collection. The program worked well and sufficed for my needs, until now. The collection has gotten to the point that searching for a particular movie is tedious at best so I developed a program that will search on part of a title name and/or on part of a cast member name. The program works well on its own, but when I tried to incorporate into the main program I get an error stating that it cannot open the data file.

I am using VB 2013 Express and SQL 2014 Express and the data file was attached at the start of development by connecting it through the Data Source menu. It opens, builds the dataset and closes. The main program displays the data one record at a time, allows editing records, adding new records and deleting records so everything there is working as designed. When I request the search, it opens the search form, allows input, builds the connection string and attempts to open the database. This is where I get the error,

 "An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Cannot open database "D:\Data Files\DVD_List.mdf" requested by the login. The login failed.
Login failed for user 'DarbysDen\Darby'."

I have checked the Command accuracy, and the command syntax and they are both correct. The only thing I can think of is that the database is still open in the main program. Is this possible, and if so can I create an additional dataset using a different select command to obtain the filtered information? I have included the search project code below.

NOTE: ‘frmSearch’ refers to the user interface where we select the criteria for the search. The user can enter the Title criteria and/or the Cast criteria and/or the Movie Rating criteria and pass it to sqlForm..

Imports System.Data
Imports System.Data.SqlClient
Public Class sqlForm
    Dim objDataSet As New DataSet()
    Dim objConnection As SqlConnection = New SqlConnection("server= " &
                                      "DARBYSDEN\SQLEXPRESS14; " &
                                      "database=D:\Data Files\DVD_List.mdf; " &
                                      "Trusted_Connection=True")
    Dim objDataAdapter As New SqlDataAdapter()
    Dim srchString As String = ""
    Dim frmDialog As New frmSearch
    	
    Private Sub sqlForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  
            If frmDialog.ShowDialog = Windows.Forms.DialogResult.Cancel Then
                'If user presses cancel get out
                srchString = ""
                MsgBox("Cancell Pressed")
                'Clean it up
                objDataAdapter = Nothing
                objConnection = Nothing
                frmSearch = Nothing
                Close()
                Exit Sub
            ElseIf Windows.Forms.DialogResult.OK Then

                'Add the SELECT command and 
                'select the fields to show in the GridView

                srchString = ""

                'If the user entered a Title, then add it to srchString
                If frmSearch.tbTitle <> "" Then
                    srchString = "SELECT Id,Title,Cast,Rated,Book,Page,Slot " &
                        "FROM DVD_List WHERE Title LIKE '%" &
                        frmSearch.tbTitle & "%'"
                    'If the user also submitted some Cast info add it to the                                                                srchString
                    If frmSearch.tbCast <> "" Then
                        srchString = srchString & "AND Cast LIKE '%" &
                            frmSearch.tbCast & "%'"
                    End If

                    'If the user also enters a rating, add it to the srchString
                    If frmSearch.tbRate <> "" Then
                        srchString = srchString & " AND RATED = " &
                            "" & frmSearch.tbRate & ""
                    End If

            ElseIf frmSearch.tbTitle = "" And frmSearch.tbCast <> "" Then

                'If the user didn't enter any title info but did enter Cast info
                ' Start the srchString like this
                srchString = "SELECT Id,Title,Cast,Rated,Book,Page,Slot " &
                    "FROM DVD_List WHERE Cast LIKE '%" &
                    frmSearch.tbCast & "%'"
            End If
            If frmSearch.tbRate <> "" Then
                srchString = srchString & " AND Rated =" &
                    "" & frmSearch.tbRate & ""
            End If
        ElseIf frmSearch.tbTitle = "" And frmSearch.tbCast = ""
         And frmSearch.tbRate <> "" Then  
            srchString = "SELECT Id,Title,Cast,Rated,Book,Page,Slot " &
                    "FROM DVD_List WHERE Rated =" & "" & frmSearch.tbRate & ""
        End If

        objDataSet.Clear()
        'set the SELECT command properties
        objDataAdapter.SelectCommand = New SqlCommand()
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.SelectCommand.CommandText = srchString
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        Try
            'open the database connection
            objConnection.Open()  .....THIS IS WHERE I GET THE ERROR
        Catch ex As Exception
              MessageBox.Show(ex.Message)
        End Try

        'fill the dataset object wit data
        objDataAdapter.Fill(objDataSet, "DVDList")

        'Close the database connection
        objConnection.Close()

        'if there isn't anything there. then tell the user and
        'Close up shop
        If objDataSet.Tables("DVD_List").Rows.Count <= 0 Then
            MsgBox("No Records Found, try again")

            'clean up
            srchString = ""
            objDataAdapter = Nothing
            objConnection = Nothing
            frmSearch = Nothing
            Close()
            Exit Sub
        End If

        'Set the DataGridView properties to bind it to our data
        grdvwDVD_List.AutoGenerateColumns = True
        grdvwDVD_List.DataSource = objDataSet
        grdvwDVD_List.DataMember = "DVD_List"

        'set column names and sizes
        grdvwDVD_List.Columns(0).HeaderText = "ID"
        grdvwDVD_List.Columns(0).Width = 40
        grdvwDVD_List.Columns(1).HeaderText = "Title"
        grdvwDVD_List.Columns(1).Width = 250
        grdvwDVD_List.Columns(2).HeaderText = "Cast"
        grdvwDVD_List.Columns(2).Width = 550
        grdvwDVD_List.Columns(3).HeaderText = "Rated"
        grdvwDVD_List.Columns(3).Width = 50
        grdvwDVD_List.Columns(4).HeaderText = "Bk"
        grdvwDVD_List.Columns(4).Width = 35
        grdvwDVD_List.Columns(5).HeaderText = "Pg"
        grdvwDVD_List.Columns(5).Width = 40
        grdvwDVD_List.Columns(6).HeaderText = "Sl"
        grdvwDVD_List.Columns(6).Width = 35

        'clean up
        objDataAdapter = Nothing
        objConnection = Nothing
    End Sub
End Class

解决方案

If the idea is to attach the database, you should use a different connection string. Try:

Dim objConnection As SqlConnection = New SqlConnection("server= " &
          "DARBYSDEN\SQLEXPRESS14; " &
          "attachbbfilename=D:\Data Files\DVD_List.mdf;database=mydbname;" &
          "Trusted_Connection=True")

Mike; Using you suggestion I get the same error message, (corrected the 'bb' to 'db'). I 'assumed' that the 'mydbname' could have ment to replace it with the Database name so entered 'DVD_List.mdf' and still get the same message.

Update 7/21/2015.. Mike, I lied, your suggestion did work. After doing some digging I found a problem in my code. Then looking for some clarification to your code I found that your suggestion was exactly what I needed. THANK YOU very much. Next addition would be to be able to click on the record in the gridview and display it in full on the original screen. That's for another day.

这篇关于打开数据库时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！