Video Library in Visual Basic

CPT-185 Term Project Page 1 of 13

You are starting your own video store. I will place a modified copy of the database identified in the Case Study Video Bonanza for Chapter 10 on D2L. This modified database will include 2 additional tables for handling customer contact information and customer video orders. Your project is to build an application to update this database. Your application should provide the capabilities to display and update the information in the Customer Table. You should also provide the ability to display all customer rentals if the user chooses, and to allow the user to check out a video to a customer. In addition, you will utilize a DataGrid to display the information in the Studio and Video tables. For this project you can assume that you have an unlimited quantity of each video on hand so you don’t have to worry about a video being out of stock if the customer wants to check it out. You should have at a minimum 7 forms:

1. Main Form – Processes Customers

2. Check Out a video to a Customer

3. Display the videos a selected customer has rented

4. Display a list of all the videos in your store

5. Display a list of the studios that videos are obtained from

6. About Box

7. Splash Screen.

An example of each of these forms is provided on the pages below. Each of your forms should contain a menu system to allow the other forms to be opened. The menu options are outlined below: File Exit Window Display Studio, Display Video Help About

CPT-185 Term Project Page 2 of 13

Main Form The purpose of this form is to allow the user to add, change, delete, and inquire on Customers. A binding navigator will allow the user to browse through the current records. The navigator also provides a new record button, delete button, and save button for modifying the records.

The Check Out button should open the Check Out Form to allow the user to add a new video checkout record to the database. The Rentals button should open the Rentals form to display all previous videos that the selected customer has rented.

CPT-185 Term Project Page 3 of 13

Display Studio Form

This form uses a Datagrid control to display the data from the Studios table to the user. You can delete the Binding Navigator that Visual Studio will add to the form. Remember to create separate Data Sources in your project.

CPT-185 Term Project Page 4 of 13

Display Video Form

This form uses a Datagrid control to display the data from the Videos table to the user. You can delete the Binding Navigator that Visual Studio will add to the form. Remember to create separate Data Sources in your project.

CPT-185 Term Project Page 5 of 13

About Box Form

CPT-185 Term Project Page 6 of 13

Check Out Form This form will allow the user to check out a new video to a customer. The Customer ID and Name are displayed based on the current selected user on the Main Form. The customer name is concatenated from the textboxes on the Main Form. This is similar to the process you would have used for the Summary form when you completed your Chapter 6 homework.

The Check Out button will add a new record to the CustomerRental table in the database. The code necessary to add this record is shown below. Remember that you will need to add the CustomerRental Dataset to the form to have access to it in code but we don’t need it visually represented on the form so you can delete the DataGrid after adding the DataSet. Code to create new Video Rental: Try

'Declare Variable for Inserting Row

Dim newrow As DataRow = CustomerRentalSQLDataSet.CustomerRental.NewRow

'Move data to new row

newrow("CustomerID") = Integer.Parse(customerIDLabel.Text)

newrow("MovieNumber") = movieTitlesListBox.SelectedValue.ToString

newrow("Date") = DateTime.Now

'Add the row

CustomerRentalSQLDataSet.CustomerRental.Rows.Add(newrow)

'Send changes to database

Me.Validate()

Me.CustomerRentalBindingSource.EndEdit()

Me.CustomerRentalTableAdapter.Update(Me.CustomerRentalSQLDataSet.CustomerRental)

Messagebox.Show(“Record Added”,“Record Added",MessageBoxButtons.OK,MessageBoxIcon.Information)

Catch ex As Exception

MessageBox.Show("Error Adding Record", "Customer", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

CPT-185 Term Project Page 7 of 13

Combo/List Boxes The form also makes use of a listbox to display the available movie titles to the user for checkout. You can use the same DataSource you created for the Display Video form for the listbox. Listboxes and Comboboxes use complex data binding as opposed to simple binding that a textbox or label uses. Several properties associated with complex binding:

1. DataSource – set to the datasource we want to use to retrieve data.

2. Display Member – This property control what is displayed in the combo/list box.

3. Value Member – This property controls what is returned to our program when user makes a selection.

4. Remember to ensure that the fields under the DataBindings property are set to None. When the user makes a selection in a listbox or combobox we need to retrieve the item selected from the listbox with the Value Member property. We can retrieve the property value with a statement such as: newrow("MovieNumber") = movieTitlesListBox.SelectedValue.ToString This line of code was included in the code for the checkout button listed above. Updating a Database: Update Method – The update method of the Table Adapter is used to pass the changes from the dataset in memory to the database. Visual Studio 2010 will create the update logic automatically for you when you drag fields from the dataset to your form. Here are a few links to some interesting articles that discusses how to create a project that updates a database http://msdn2.microsoft.com/en-us/library/0f92s97z(VS.80).aspx http://msdn2.microsoft.com/en-us/library/ms379590(vs.80).aspx

CPT-185 Term Project Page 8 of 13

Rentals Form This form will allow the user to display all of the previous video rentals for a customer. The Customer ID and Name are displayed based on the current selected user on the Main Form. The customer name is concatenated from the textboxes on the Main Form. This is similar to the process you would have used for the Summary form when you completed your Chapter 6 homework.

Parameterized Query This type of query is used to only retrieve records that match some criteria. For example, to retrieve only the videos rented by a certain customer id. In Visual Studio we can create a parameterized query by right clicking on the data source and choosing Edit Dataset With Designer.

CPT-185 Term Project Page 9 of 13

In this example I have created another query called FillByID which can be seen below:

By expanding the CommandText property you can view/edit the SQL Select query.

CPT-185 Term Project Page 10 of 13

In the criteria column enter an “=?”, and then click out of the field. This will add a parameter that will then need to be sent to the query anytime it is called. In your forms code add a statement like this: Me.CustomerRentalTableAdapter.FillByID(Me.VBVideoCustomerRentalVideoDataSet.CustomerRental, Integer.Parse(CustomerIDLabel.Text)) The Integer.Parse(customerIDLabel.Text) will pass whatever data is entered in the CustomerIDLabel to the query. Remember to fill this field before displaying the form from whatever form is opening it. For example, in the Term Project I set the labels on the RentalsForm from the MainForm.

CPT-185 Term Project Page 11 of 13

The Tables in the DB and their fields are as follows:

CPT-185 Term Project Page 12 of 13

CPT-185 Term Project Page 13 of 13