Edit a Saved Order - Part 1

Introduction

The previous post showed how we can view a purchase order after it has been saved, however, there are still a number of steps we need to take to edit and re-save the order.

The problems we encountered in the last posting were that if we try to edit (or add) an order line in an existing order:

the Product drop-down list is blank, showing 'No Records'
the Tax Rate is not shown in the drop-down list, although the list is populated once it is clicked.

The first thing to sort out is the problem of the Product drop-down list not showing any records when the Add/Edit dialog for order lines is opened.

The reason nothing is shown in the product drop-down list is that the data source for the list (products) is linked to the OnChangeSupplier event (because the products are supplier dependent), and we have disabled the supplier drop-down list in the order header and therefore there has been no change in supplier!

To get round this problem we need to find an alternative event that we can use to trigger the population of 'products'. An obvious one is the 'ToolbarClickHandler'; this is activated when the user chooses to add or edit an order line and will always be triggered. I originally thought of inserting to code into the 'Edit' option, but instead decided to put it at the top of the method because it can be used for both add and edit functions. It also means that it can be removed from the OnChangeSupplier event. Insert the following code at the top of the ToolbarClickHandler:

product = await ProductService.ProductListBySupplier(orderaddedit.POHeaderSupplierID);

Note that the parameter is orderaddedit.POHeaderSupplierID, this is not quite the same as the version used in OnChangeSupplier.

Hindsight is wonderful. When I originally designed the POLine SQL table I thought that recording the Tax Rate would be sufficient and that saving the TaxID would not be necessary. It turns out that this was a mistake. Although the POLine model has POLineTaxID, the database doesn't. This has the consequence that when an existing POLine is retrieved from the database the TaxID is unknown, so the TaxRate dop-down list is left blank.

To remedy this I am going to have to back-track and add the TaxID to the POLine table in the SQL database. (It is also means that all existing POHeader and POLine data is junk and will need to be deleted!) We will also need to re-visit a number of stored procedures that involved POLine.

Open SQL Management Studio, select the PurchaseOrders database and delete all records from POHeader and POLine.

USE [PurchaseOrders]
GO

DELETE FROM POHeader
GO

DELETE FROM POLine
GO

ALTER SEQUENCE NextPurchaseOrderNumber RESTART WITH 1
GO

Use the following SQL code to drop and recreate the POLine table, this time including the POLineTaxID column.

USE [PurchaseOrders]
GO

ALTER TABLE [dbo].[POLine] DROP CONSTRAINT [DF_POLine_POLineTaxRate]
GO

ALTER TABLE [dbo].[POLine] DROP CONSTRAINT [DF_POLine_POLineProductUnitPrice]
GO

ALTER TABLE [dbo].[POLine] DROP CONSTRAINT [DF_POLine_POLineProductQuantity]
GO

ALTER TABLE [dbo].[POLine] DROP CONSTRAINT [DF_POLine_POLineProductID]
GO

ALTER TABLE [dbo].[POLine] DROP CONSTRAINT [DF_POLine_POLineHeaderID]
GO

/****** Object:  Table [dbo].[POLine]    Script Date: 05/04/2021 10:43:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[POLine]') AND type in (N'U'))
DROP TABLE [dbo].[POLine]
GO

/****** Object:  Table [dbo].[POLine]    Script Date: 05/04/2021 10:43:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[POLine](
	[POLineID] [int] IDENTITY(1,1) NOT NULL,
	[POLineHeaderID] [int] NOT NULL,
	[POLineProductID] [int] NOT NULL,
	[POLineProductDescription] [nvarchar](50) NOT NULL,
	[POLineProductQuantity] [decimal](9, 3) NOT NULL,
	[POLineProductUnitPrice] [money] NOT NULL,
	[POLineTaxRate] [decimal](6, 4) NOT NULL,
	[POLineTaxID] [int] NOT NULL,
 CONSTRAINT [PK_POLine] PRIMARY KEY CLUSTERED 
(
	[POLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineHeaderID]  DEFAULT ((0)) FOR [POLineHeaderID]
GO

ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineProductID]  DEFAULT ((0)) FOR [POLineProductID]
GO

ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineProductQuantity]  DEFAULT ((0)) FOR [POLineProductQuantity]
GO

ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineProductUnitPrice]  DEFAULT ((0)) FOR [POLineProductUnitPrice]
GO

ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineTaxRate]  DEFAULT ((0)) FOR [POLineTaxRate]
GO

Stored Procedures

The following is a script that will drop and recreate all stored procedures that relate to the POLine table.

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spPOLine_GetByPOHeader]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--------------  Stored Proc for SELECT by POHeader, with Product Code and Net, Tax and Gross
CREATE PROCEDURE [dbo].[spPOLine_GetByPOHeader]
-- 
@POHeaderID int
AS 
BEGIN
-- SQL Select for one table row
SELECT dbo.POLine.POLineID, dbo.POLine.POLineHeaderID, dbo.POLine.POLineProductID, dbo.Product.ProductCode as POLineProductCode, 
		dbo.POLine.POLineProductDescription, dbo.POLine.POLineProductQuantity, dbo.POLine.POLineProductUnitPrice, 
		dbo.POLine.POLineProductUnitPrice * dbo.POLine.POLineProductQuantity AS POLineNetPrice, 
        dbo.POLine.POLineTaxRate, dbo.POLine.POLineTaxID,
		dbo.POLine.POLineProductUnitPrice * dbo.POLine.POLineProductQuantity * dbo.POLine.POLineTaxRate AS POLineTaxAmount, 
		(dbo.POLine.POLineProductUnitPrice * dbo.POLine.POLineProductQuantity) * (1 + dbo.POLine.POLineTaxRate) AS POLineGrossPrice
FROM   dbo.POLine INNER JOIN
        dbo.Product ON dbo.POLine.POLineProductID = dbo.Product.ProductID
WHERE POLineHeaderID= @POHeaderID
END

GO

DROP PROCEDURE [dbo].[spPOLine_GetOne]
GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE [dbo].[spPOLine_GetOne]
-- Needs one parameter for primary key
@POLineID int
AS 
BEGIN
-- SQL Select for one table row
SELECT POLineID, POLineHeaderID, POLineProductID, POLineProductDescription, POLineProductQuantity, POLineProductUnitPrice, POLineTaxRate, POLineTaxID FROM POLine WHERE POLineID= @POLineID
END

GO

DROP PROCEDURE [dbo].[spPOLine_Insert]
GO

-------------- Stored Proc for INSERT
CREATE PROCEDURE [dbo].[spPOLine_Insert]
--Parameters for Insert stored procedure
@POLineHeaderID int,
@POLineProductID int,
@POLineProductDescription nvarchar(50),
@POLineProductQuantity decimal(9, 3),
@POLineProductUnitPrice money,
@POLineTaxRate decimal(6, 4),
@POLineTaxID int
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO POLine(POLineHeaderID, POLineProductID, POLineProductDescription, POLineProductQuantity, POLineProductUnitPrice, POLineTaxRate, POLineTaxID) 
VALUES (@POLineHeaderID, @POLineProductID, @POLineProductDescription, @POLineProductQuantity, @POLineProductUnitPrice, @POLineTaxRate, @POLineTaxID)
END

GO

DROP PROCEDURE [dbo].[spPOLine_List]
GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE [dbo].[spPOLine_List]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT POLineID, POLineHeaderID, POLineProductID, POLineProductDescription, POLineProductQuantity, POLineProductUnitPrice, POLineTaxRate, POLineTaxID FROM POLine ORDER BY POLineID DESC
END

GO

DROP PROCEDURE [dbo].[spPOLine_Update]
GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spPOLine_Update]
-- Parameters for Update stored procedure.
@POLineID int,
@POLineHeaderID int,
@POLineProductID int,
@POLineProductDescription nvarchar(50),
@POLineProductQuantity decimal(9, 3),
@POLineProductUnitPrice money,
@POLineTaxRate decimal(6, 4),
@POLineTaxID int
AS
BEGIN
-- SQL for Update stored procedure
UPDATE POLine SET POLineHeaderID = @POLineHeaderID, POLineProductID = @POLineProductID, 
POLineProductDescription = @POLineProductDescription, POLineProductQuantity = @POLineProductQuantity, 
POLineProductUnitPrice = @POLineProductUnitPrice, POLineTaxRate = @POLineTaxRate, POLineTaxID = @POLineTaxID
WHERE POLineID = @POLineID
END

GO

C# Changes

Having made these changes to the SQL database, POLineService also needs modifying to add the new column to the POLineInsert and POLIneUpdate methods, as shown below:

These changes should now populate both the Product and Tax drop-down lists when editing an existing purchase order line.

There is one last change to make, to PurchaseOrderPage.razor. In the OrderLineSave method we need to add the POLineTaxID to the orderLines collection. To do this modify the method as shown below:

Save the project and run it to test.

Project Code

The code for all the files changed in this post are shown here.

YouTube Video

Blazor Purchase Orders - Part 17

Introduction

Product Drop-Down List

Tax Rate Drop-Down List

Stored Procedures

C# Changes

Project Code

YouTube Video