CPT 341 Visual Basic.NET

Project 4 – Spring Semester 2018

Database Management, ADO.NET, DataGridView Control

The database ComputerWarehouse.MDB (attached to the email and Moodle) is maintained by the Microland Computer Warehouse, an online computer supply company. There are 3 tables in the database. The table CUSTOMERS identifies each customer by an ID number and gives, in addition to the name and address, the total amount of purchases. The table INVENTORY identifies each product in stock by an ID number and gives, in addition to its description and price (per unit), and the quantity in stock. The table ORDERS gives the orders received.

  Table 1. First three records of the CUSTOMERS Table

  Table 2. First three records of the INVENTORY Table

  Table 3. First three records of the ORDERS Table

Develop a VB .NET program that will do the following:

• Load the data from the ComputerWarehouse database to a DataGridView control allowing a user to make edits within the DataGridView control, and saving the changes back to the database.
• Display the name of who made an order by joining customers and inventory tables. The SQL statement below will join these 2 tables:

Notes:

• To update a record, highlight the field and make the change.

• To insert a new record, click on the bottom row (in the DataGridView) that begins with an asterisk and has blank fields. (The values in the fields are initially filled with “NULL” and should be changed).
• To delete a row, click on the box to the left row (in the DataGridView) so that the entire row is highlighted, then click the DELETE key.
• If the “Load Inventory” button is clicked before clicking the “Update Inventory” button, any changes will be lost.
• To update the database, the OleDbDataAdapter function can be used to generate thre SQL statements based on the changes made in the DataGridView.

SELECT Customers.name, Customers.street, Customers.city, Customers.amtPurchases, Inventory.description, Inventory.quantity FROM (Inventory INNER JOIN (Customers INNER JOIN Orders ON Customers.custID = Orders.custID) ON Inventory.itemID = Orders.itemID) ORDER BY Customers.custID

1. Display in a DataGridView control the items that are out of stock and therefore must be reordered (for example, where the quantity is equal to zero).

******************************************************************************

Project 4 Due Date: 04/9/2018

Note: Send all completed VB Project files to: wxt1955@njit.edu (or use Dropbox, etc.)