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.