Having a problem with opening a database

See more:

SQL

VB12

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