Adding Row_Number Column to a DataGridView

DarbyBrown · March 24, 2016, 10:01pm

I have a database full of DVD movies that I own. The fields in this database are Title, Plot, Cast, Genre, Book, Page, & Slot. The Book, Page & Slot fields show me where the DVD’s are stored. I am trying to build a process that will list movies to a DataGrid based on any combination of 4 criteria (Title, Cast, Genre, and Rating). The user can use any part of the data in the first 3 fields(ie for Title they could enter only “Lone” and the search function will find any and all movies with “Lone” in the title) The rating field is limited to one of a selection of movie ratings.(NR, G, PG, PG-13, R, X)
I have no problem building a search string with the exception of adding the Row Number of the record in the original dataset. I’ve tried

srchString = "SELECT ROW_NUMBER() OVER(ORDER BY Title),Title, Plot, Cast, Rated, Genre,
Book, Page, Slot FROM DVD_List WHERE "

But I get an error:

Additional information: The expression contains undefined function call ROW_NUMBER().

This was the only function Icoul find to give me a Row Number.

The second part is, the filter to use to get this all in a DataGridView, eerythinI have read says that the Filter command will list AL columns in the database and cannot be limited to just a select few. Any ideas on overcoming this?

    srchDataView.RowFilter = srchString

JamesK · March 25, 2016, 3:36am

I don't see anything wrong with the ROW_NUMBER function the way you are using it. If you are on SQL Server 2005 or later, it should work. What version are you using?

Regarding the datagridview, if you bind to a table, you will get all columns, but you use a query, you can select which columns to select in the query.

ScottPletcher · March 25, 2016, 3:02pm

Btw, the "cast" data/column needs to go into a separate table, since it will be multiple values, and you could have additional data that applies only to that particular cast member and not to the entire movie.

DarbyBrown · March 25, 2016, 3:39pm

Scott; The "Cast" data is a string. ie "Robert Redford, Sisy Spacek, John Travolta, etc, etc"

DarbyBrown · March 25, 2016, 4:05pm

JamesK; I am using SQL Server Express 2014 and VB 2015 Community Below is the code for the search Class

Public Class sqlForm

Dim srchDataSet = New MovieDevelope_3._D__DATA_FILES_DVD_LIST_MDFDataSet()
Dim srchDataView = New DataView(srchDataSet.Tables("DVD_List"))
Dim DVD_ListTableAdapter = New _D__DATA_FILES_DVD_LIST_MDFDataSetTableAdapters.DVD_ListTableAdapter()
Dim srchDataAdapter = New DVD_ListTableAdapter()
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 = DialogResult.Cancel Then

        'if user pressed cancel get out

        srchString = ""
        DevelopeForm.again = False

        'Clean it up

        frmSearch = Nothing
        Exit Sub

        'If the user pressed OK then start search sequence

    ElseIf DialogResult.OK Then

       ''If there is no entries, ask for retry

        If frmSearch.tbTitle = "" And frmSearch.tbCast = "" _
            And frmSearch.tbRate = "" And frmSearch.tbGenre = "" Then
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Exclamation)

            srchString = ""

            If MessageBox.Show("No Data Entered!" & vbCrLf & vbLf &
                    "    Try Again?", "UH-OH", MessageBoxButtons.YesNo,
                               MessageBoxIcon.Question) = DialogResult.No Then
                DevelopeForm.again = False

            End If
           ' 'Clean it up
            frmSearch = Nothing
            Exit Sub
        End If

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

        srchString = "ROW_NUMBER() OVER(ORDER BY Title),Title, Plot, 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

    ''fill the dataview object with data

    srchDataView.RowFilter = srchString

   ' '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

   '     ' If search came up empty, alert user.

        My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Exclamation)

        If MessageBox.Show("No Records Found with that Criteria." & vbCrLf &
          vbLf & "            Try Again?", "UH-OH", MessageBoxButtons.YesNo,
         MessageBoxIcon.Question) = DialogResult.No Then
            DevelopeForm.again = False
        End If

   '     'clean up

        srchString = ""
        srchDataAdapter = 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"
    grdvwDVD_List.RowsDefaultCellStyle.BackColor = Color.PowderBlue
    grdvwDVD_List.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue

   ' 'set column names and sizes

    grdvwDVD_List.Columns(0).HeaderText = "Row"
    grdvwDVD_List.Columns(0).Width = 40
    grdvwDVD_List.Columns(1).HeaderText = "Title"
    grdvwDVD_List.Columns(1).Width = 200
    grdvwDVD_List.Columns(2).HeaderText = "Plot"
    grdvwDVD_List.Columns(2).Width = 220
    grdvwDVD_List.Columns(3).HeaderText = "Cast"
    grdvwDVD_List.Columns(3).Width = 220
    grdvwDVD_List.Columns(4).HeaderText = "Rated"
    grdvwDVD_List.Columns(4).Width = 45
    grdvwDVD_List.Columns(5).HeaderText = "Bk"
    grdvwDVD_List.Columns(5).Width = 30
    grdvwDVD_List.Columns(6).HeaderText = "Pg"
    grdvwDVD_List.Columns(6).Width = 35
    grdvwDVD_List.Columns(7).HeaderText = "Sl"
    grdvwDVD_List.Columns(7).Width = 30

   ' ' clean up

    srchString = ""
    srchDataAdapter = Nothing
    frmSearch = Nothing

End Sub

End Class

ScottPletcher · March 25, 2016, 4:06pm

Yes, which is why cast data should be separated into a different table, 100%, no doubt about it. Otherwise:
(1) It violates first normal form.
(2) You can't add data about just that specific cast member, such as "character name".
(3) You can't distinguish between actors/etc. with the same name.

For example:

Movies Table:
( movie_id=1, title='The Sting', ...)

Cast Table:
( cast_id=1, role='actor', first_name= 'Robert', last_name='Redford', birth_date=..., ... )

MovieCast Table:
( movie_id=1, cast_id=1, character_name='Johnny Hooker', ... )

Kristen · March 25, 2016, 6:21pm

Is there an issue that CAST is a reserve word (and would be valid in this context?)

Perhaps that column name needs quoting:

..., Plot, [Cast], Rated, ...

ScottPletcher · March 25, 2016, 6:54pm

That doesn't seem to be the case :

select Cast from (select 1 as Cast) as x

Kristen · March 26, 2016, 7:32am

I'd hate to have to write YACC !!

DarbyBrown · March 26, 2016, 6:13pm

ScottPletcher;

The Cast field is for movie specific purpose only, I have no intention of including personal information about the cast members therefore do not need to create a separate table for such..if I want to find all movies that a particular actor was in, I just enter the name and search for it and it will give me all movies in my collection with that name in it.That is if the user types in "Robert Redford", all my movies with Robert Redford in them will be listed, Further if the user cannot remember the entire name of the actor then they just need to enter a part of the name like "robert" and the search function will find all movies with an actor with robert in their name.As you noticed I portrayed the name in all lower case, the search function is not case sensitive.

ScottPletcher · March 28, 2016, 3:45pm

Actually you need a separate table to replace any list of values in a single column. A single column is supposed to be a single value, never a list. Good luck with this. You'll regret the decision to use a list at some point.