Exercise 3: Edit Batch Row Data Form a.k.a. "Batch Edit"

The Batch Edit Row Data Form Spec tells the batch how to edit a row in the batch staging table. As in any edit data form template spec, an edit load and edit save procedure is defined. The @ID parameter identifies the row being edited within the USR_BATCHFOODITEMADD table.

Step 1 -  Review the following EditDataFormTemplateSpec.

Compare the spec below with the Add Data Form Spec created in Exercise 2: Add Batch Row Data Form a.k.a. "Batch Add". Note the load and save implementation. Note the duplication of the form fields and ExpectedDBExceptions.

<EditDataFormTemplateSpec 
    xmlns:common="bb_appfx_commontypes"
    ID="e3402fc1-6783-4003-8853-89ca2b90a2cc"
    Name="Food Item Add Batch Row Edit Form"
    Description="Edit dataform template for fooditemadd batch records."
    Author="Technical Training"
    RecordType="Food Item Add Batch"
    DataFormInstanceID="b9bf17f7-97e0-4cd7-9ecb-c8963078ed3a"
    common:SecurityUIFolder="Constituent\Food Bank\Food Item\Batch"
    SecurityUIDisplayFeature="false"
    xmlns="bb_appfx_editdataformtemplate">
  
	<SPDataForm>
		<LoadImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDITLOAD_BATCHFOODITEMADDBATCHROW">
			<common:CreateProcedureSQL>
  <![CDATA[
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDITLOAD_BATCHFOODITEMADDBATCHROW
(
	@ID uniqueidentifier,
	@DATALOADED bit = 0 output,
	@TSLONG bigint = 0 output,
	@SEQUENCE int = null output,
	@NAME nvarchar(100) = null output,
	@DESCRIPTION nvarchar(max) = null output,
	@CURRENTCOST money = null output,
	@LOWINVENTORYTHRESHOLD smallint = null output,
	@WEIGHT decimal(10,2) = null output
)
as
set nocount on;

set @DATALOADED = 0;
set @TSLONG = 0;

select
	@DATALOADED = 1,
	@TSLONG = [TSLONG],
	@SEQUENCE = [SEQUENCE],
	@NAME = [NAME],
	@DESCRIPTION = [DESCRIPTION],
	@CURRENTCOST = [CURRENTCOST],
	@LOWINVENTORYTHRESHOLD = [LOWINVENTORYTHRESHOLD],
	@WEIGHT = [WEIGHT]
from
	dbo.USR_BATCHFOODITEMADD
where
	ID = @ID;

return 0;

]]></common:CreateProcedureSQL>
</LoadImplementation>      
    <SaveImplementation SPName="USR_USP_DATAFORMTEMPLATE_EDIT_BATCHFOODITEMADDBATCHROW">
			<common:CreateProcedureSQL>
  <![CDATA[     
create procedure dbo.USR_USP_DATAFORMTEMPLATE_EDIT_BATCHFOODITEMADDBATCHROW
(
	@ID uniqueidentifier,
	@CHANGEAGENTID uniqueidentifier,
	@SEQUENCE int,
	@NAME nvarchar(100),
	@DESCRIPTION nvarchar(max),
	@CURRENTCOST money,
	@LOWINVENTORYTHRESHOLD smallint,
	@WEIGHT decimal(10,2)
)
as
set nocount on;

declare @CURRENTDATE datetime;

if @CHANGEAGENTID is null
	exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

set @CURRENTDATE = getdate();

begin try
update dbo.USR_BATCHFOODITEMADD
	set
		[SEQUENCE] = @SEQUENCE,
		[CHANGEDBYID] = @CHANGEAGENTID,
		[DATECHANGED] = @CURRENTDATE,
		[NAME] = coalesce(@NAME, ''),
		[DESCRIPTION] = coalesce(@DESCRIPTION, ''),
		[CURRENTCOST] = coalesce(@CURRENTCOST, 0.00),
		[LOWINVENTORYTHRESHOLD] = coalesce(@LOWINVENTORYTHRESHOLD, 0),
		[WEIGHT] = coalesce(@WEIGHT, 0.00)
	where
		ID = @ID;
end try
begin catch
	exec dbo.USP_RAISE_ERROR;
	return 1;
end catch

return 0;

]]></common:CreateProcedureSQL>
      <common:ExpectedDBExceptions>
        <common:Constraints>
          <common:Constraint Name="CK_USR_BATCHFOODITEMADD_NAME" Field="NAME" Type="Required" />
          <common:Constraint Name="UIX_USR_BATCHFOODITEMADD_BATCHID_NAME" Field="NAME" Type="Unique" />
        </common:Constraints>
      </common:ExpectedDBExceptions>
</SaveImplementation>
</SPDataForm>
  
	<common:FormMetaData>
		<common:FormFields>
      <common:FormField FieldID="SEQUENCE" Required="true" Hidden="true" Caption="Sequence" />
      <common:FormField FieldID="NAME" Required="true" MaxLength="100" Caption="Name" />
      <common:FormField FieldID="DESCRIPTION" Caption="Description" DefaultValueText="" />
      <common:FormField FieldID="CURRENTCOST" DataType="Money" Caption="Current Cost" DefaultValueText="0.00" />
      <common:FormField FieldID="LOWINVENTORYTHRESHOLD" DataType="SmallInt" Caption="Low Inventory Threshold" DefaultValueText="0" />
      <common:FormField FieldID="WEIGHT" DataType="Decimal" Caption="Weight" Description="The weight of a food item." Precision="10" Scale="2" DefaultValueText="0.00" />
		</common:FormFields>
	</common:FormMetaData>
</ EditDataFormTemplateSpec >
    

Step 2 -  Adjust the add and edit data forms and load.

Use the Edit Data Form above as a guide and create your Edit Data Form ensuring the form fields and parameter names match with your Add Data Form before loading the Edit Data Form Spec.