I am having a problem in 2 areas of a DVD Movie tracking program I am developing.
I have designed and written a program to keep track of my DVD-Movie collection which has grown to over 600 so far. The program allows me to display records 1 at a time and page forward and backward one record at a time. It also allows me to add and delete records as needed. It also allows me to search for records based on any of 4 criteria. The 2 areas of concern are in the search functions. I have written a synapsis of the basic program, and I have included the code in the areas where I am having problems.
The program starts by opening the data source, building the main dataset (DVD_ListDataSet) and closing the data source, and then displays the first record in the dataset on the main screen form. From this form the user can page through the records forward and backwards one record at a time. The user can also add or delete records as needed. This all works as expected with no problems or errors. I had included images showing the problems but the system wouldn't allow it so I hope you can figure out what I am referring to.
I have included a search function that open a form that the user can enter certain search criteria including a combination of a full or partial movie name, a full or partial actor’s name, a movie rating, and genre. Then it takes these criteria and concatenates a SELECT command. With this SELECT command I then build a secondary DataSet (srchDataSet) and then set up and display a DataGridView (gedvwDVD_List) from that.
Code setting up the srchDataSet and grdvwDVD_List:
Imports System.Data
Imports System.Data.SqlClient
Public Class sqlForm
Dim srchDataSet As New DataSet()
Dim srchConnection As SqlConnection = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='D:\Data Files\DVD_List.mdf';Integrated Security=True;Connect Timeout=30")
Dim srchDataAdapter 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 pressed cancel get out
srchString = ""
MovieListForm1.again = False
'Clean it up
srchDataAdapter = Nothing
srchConnection = Nothing
frmSearch = Nothing
Close()
Exit Sub
ElseIf Windows.Forms.DialogResult.OK Then
If frmSearch.tbTitle = "" And frmSearch.tbCast = "" And frmSearch.tbRate = "" And frmSearch.tbGenre = "" Then
srchString = ""
If MessageBox.Show("No Data Entered!" & vbCrLf & vbLf & " Try Again?", "UH-OH", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
MovieListForm1.again = False
End If
'Clean it up
srchDataAdapter = Nothing
srchConnection = Nothing
frmSearch = Nothing
Close()
Exit Sub
End If
'Add the SELECT command and
'select the fields to show in the GridView
srchString = "SELECT Title, Cast, Rated, Genre, Book, Page, Slot FROM DVD_List WHERE "
'If the user enters Title information
If frmSearch.tbTitle <> "" Then
srchString = srchString & "Title LIKE '%" & frmSearch.tbTitle & "%'"
End If
'If the user enters Cast Information
If frmSearch.tbCast <> "" Then
If frmSearch.tbTitle <> "" Then
srchString = srchString & " AND "
End If
srchString = srchString & "Cast LIKE '%" & frmSearch.tbCast & "%'"
End If
'If the user enters a Rating
If frmSearch.tbRate <> "" Then
If frmSearch.tbTitle <> "" Or frmSearch.tbCast <> "" Then
srchString = srchString & "AND "
End If
srchString = srchString & "Rated =" & "'" & frmSearch.tbRate & "'"
End If
'If the user enters a Genre
If frmSearch.tbGenre <> "" Then
If frmSearch.tbTitle <> "" Or frmSearch.tbCast <> "" Or frmSearch.tbRate <> "" Then
srchString = srchString & "AND "
End If
srchString = srchString & "Genre LIKE '%" & frmSearch.tbGenre & "%'"
End If
End If
srchDataSet.Clear()
'set the SELECT command properties
srchDataAdapter.SelectCommand = New SqlCommand()
srchDataAdapter.SelectCommand.Connection = srchConnection
srchDataAdapter.SelectCommand.CommandText = srchString
srchDataAdapter.SelectCommand.CommandType = CommandType.Text
Try
'open the database connection
srchConnection.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'fill the dataset object with data
srchDataAdapter.Fill(srchDataSet, "DVD_List")
'Close the database connection
srchConnection.Close()
'if there isn't anything there. ask the user to
'try again or Close up shop
If srchDataSet.Tables("DVD_List").Rows.Count <= 0 Then
'MsgBox("No Records Found, try again?")
If MessageBox.Show("No Records Found with that Criteria." & vbCrLf & vbLf & " Try Again?", "UH-OH", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
MovieListForm1.again = False
End If
'clean up
srchString = ""
srchDataAdapter = Nothing
srchConnection = Nothing
frmSearch = Nothing
Close()
End If
'Set the DataGridView properties to bind it to our data
grdvwDVD_List.AutoGenerateColumns = True
grdvwDVD_List.DataSource = srchDataSet
grdvwDVD_List.DataMember = "DVD_List"
'set column names and sizes
grdvwDVD_List.Columns(0).HeaderText = "Title"
grdvwDVD_List.Columns(0).Width = 250
grdvwDVD_List.Columns(1).HeaderText = "Cast"
grdvwDVD_List.Columns(1).Width = 300
grdvwDVD_List.Columns(2).HeaderText = "Rated"
grdvwDVD_List.Columns(2).Width = 50
grdvwDVD_List.Columns(3).HeaderText = "Genre"
grdvwDVD_List.Columns(3).Width = 100
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
srchDataAdapter = Nothing
srchConnection = Nothing
End Sub
Private Sub grdvwDVD_List_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles grdvwDVD_List.CellMouseClick
Close()
MovieListForm1.cellSelect = ""
Dim cell As DataGridViewCell
For Each cell In grdvwDVD_List.SelectedCells
MovieListForm1.cellSelect = cell.Value.ToString()
Next
MovieListForm1.again = False
End Sub
End Class
Selected record is ‘FIVE PEOPLE YOU MEET N HEAVEN’.
The first problem is, how can I limit the user to select from only the first column (Title) of the grdvwDVD_List . The second problem is once the user has selected a title how do I take that back to the original DVD_ListDataSet and move the pointer to display the full record? My present code is:
If MovieListForm1.cellSelect <> "" Then
Dim userRow As DVD_ListDataSet.DVD_ListRow
userRow = DVD_ListDataSet.DVD_List.FindByTitle(MovieListForm1.cellSelect)
Me.DVD_ListBindingSource.DataSource = userRow
End If
Basically it works, the data is displayed in the main form, but it is the only record in the DataSet. I no longer have the ability to page forward or backwards in the original database. I need to restart the program for that.