Linking Tables


GUI for Linking Tables

A linking table joins objects from several tables, and in some cases the linking table may require additional information such as the date or the time. The GUI for linking tables requires the following steps:
  1. A complete GUI (or a GUI control) to SELECT each of the objects involved in the linking. When there are few items a drop down list may be used. When there are a lot of items a complete GUI with filters, and search tools must be implemented. Each SELECT GUI (or SELECT control) must return a valid primary key to create the link.
  2. A set of GUI controls (GUI elements) to collect additional information related to the linking.
  3. Create a INSERT SQL statement (or statements) to perform the linking
  4. Execute the INSERT (ExecuteNonQuery)
  5. Inform the user of the results (reviewing how many rows were affected)

Problem 1
In the best_buy database, create the view vw_item_price_date to display the items, their prices and their effective dates.

vw_item_price_date

Problem 2
In the best_buy database, create the view vw_item_price to display the current price of the items (that is the item price on the greatest effective date that is less than the current date or equal to the current date).

vw_item_price

Problem 3
In the best_buy database, create the view vw_item_pricex to display the current price of the items and brand name as shown below. You may insert new records with future dates to test the view. For instance, you may insert a record for tomorrow to test the view tomorrow.

vw_item_pricex

Problem 4
Modify the best_buy.sql file to create a stored procedure to move the items from the cart to the buy_sell and buy_sell_detail tables. The stored procedure must:
  1. Begin a transaction
  2. Insert a new record into the buy_sell table for the given employee_id and client_id. Use the variable @@ROWCOUNT to find out the number of rows affected.
  3. Retrieve the automatically generated buy_sell_id using the variable @@IDENTITY
  4. SELECT each record from the cart table that belongs to a given client.
  5. For each record in the cart table, insert a new record into the buy_sell_detail table
  6. Delete the respective record from the cart table
  7. Commit the transaction
  8. Rollback the transaction, if something goes wrong

best_buy.sql
-- ______________________________________________ proc_buy
CREATE PROCEDURE proc_buy
     @in_client_id INT,
     @in_employee_id INT
AS
     DECLARE @item_count INT;
     DECLARE @item_id INT;
     DECLARE @count INT;
     DECLARE @buy_sell_id INT;
     DECLARE @price MONEY;

     SET @item_count = 0;
     SET @item_id = -1;
     SET @count = -1;
     SET @buy_sell_id = -1;

     BEGIN TRANSACTION;--_________________________________________________

     --____________________________________________ INSERT INTO buy_sell
     ...
     SET @count=@@ROWCOUNT;
     ...
     --
     -- Be sure an INDENTITY value was generated
     IF (@@IDENTITY = NULL)
     BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END
     SET @buy_sell_id = @@IDENTITY; -- _____________Retrieve the automatically generated buy_sell_id

     DECLARE cur_cart CURSOR READ_ONLY FOR
          SELECT item_id, item_count
          FROM cart
          WHERE client_id = @in_client_id;
     --_______________________________________________ Go item by item in the cart
     OPEN cur_cart;
          FETCH NEXT FROM cur_cart INTO @item_id, @item_count;
          WHILE (@@FETCH_STATUS=0)
          BEGIN
               --_____________________________ Get the price
               ...
               --_____________________________ INSERT INTO buy_sell_detail
               ...
               --_____________________________ DELETE FROM cart
               ...
               --_____________________________ GO TO NEXT item
               FETCH NEXT FROM cur_cart INTO @item_id, @item_count;
          END
     CLOSE cur_cart;
     DEALLOCATE cur_cart;
     COMMIT TRANSACTION;--_________________________________________________
     RETURN 1;
GO


Tip
When calling a stored procedure from a program, it is possible to use the value returned by the procedure to monitor the execution of the procedure and find errors.
Cuando un procedimiento almacenado se llama desde un programa, es posible usar el valor regresado por el procedimiento para monitorear la ejecución del procedimiento y encontrar errores.

Tip
To execute a store procedure from a program, you may use the templates. From the Microsoft Visual Studio Toolbox menu: Tools > Add Wintempla Item... > Clipboard Code > Add > SQL Application programming > Stored Procedure .
Para ejecutar un procedimiento almacenado desde un programa, usted debe las plantillas. Desde el menú Microsoft Visual Studio: Tools > Add Wintempla Item... > Clipboard Code > Add > SQL Application programming > Stored Procedure .

Templates

Problem 5
Create a Wintempla Dialog application called Store to place an order from the best_buy database. Points to evaluate:
  1. The system has a login dialog to control access
  2. The system displays a list of items
  3. It is possible to select a client
  4. It is possible to place items in the cart
  5. It is possible to see the cart
  6. It is possible to see the total amount of the items in the cart
  7. It is possible to place an order (by calling the proc_buy stored procedure, use the Wintempla template: SQL Stored procedure)

Login

ItemList

SelectClient

PlacingInCart1

PlacingInCart2

Cart

Problem 6
Create a Wintempla Web application called StoreWeb to place an order from the best_buy database. The web site has two pages: Index and CartPage. Do NOT FORGET to include the "CartPage.h" file in the StoreWeb.cpp file using the command #include. The "Add To Cart" button has the click event (it must not be a Submit button). Points to evaluate:
  1. The system requires a login dialog to control access in ALL PAGES
  2. The system displays a list of items (Index page)
  3. It is possible to select a client (Index page)
  4. It is possible to place items in the cart (Index page)
  5. It is possible to see the cart (CartPage page)
  6. It is possible to see the total amount of the items in the cart (CartPage page)
  7. It is possible to place an order (by calling the proc_buy stored procedure, use the Wintempla template: SQL Stored procedure)
  8. The web application was published in a web server, use Basic Authentication.

LoginWeb

ItemListWeb

SelectClientWeb

PlacingInCartWeb

CartWeb

Problem 7
Modify the best_buy.sql file to create the view vw_order_price to see the column total = price*item_count.

vw_order_price

Problem 8
Modify the best_buy.sql file to create the view vw_order_total of each order.

vw_order_total

Problem 9
Create a Wintempla Dialog application called OrderView to see the orders place by each client. Points to evaluate:
  1. The system displays a list of orders by client
  2. It is possible to see the details of each order

OrderView

OrderDetail

Problem 10
Create a Wintempla Web application called OrderViewWeb to see the orders place by each client. Points to evaluate:
  1. The system displays a list of orders by client
  2. It is possible to see the details of each order
  3. The web application was published in a web server, use Basic Authentication.

OrderViewWeb

OrderViewWeb2

OrderDetailWeb

© Copyright 2000-2019 Wintempla selo. All Rights Reserved. Sep 05 2019. Home