SQLTeam.com | Weblogs | Forums

Force selection column in DataGridView and using the returning data

sql2014

#1

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.