Example for Edit Event "Custom Record Update"

To achieve your goal with ASPRunner.NET, you can customize the update query to exclude the calculated fields from being updated while still displaying their values on the edit page. Below are the steps and an example of how you can implement this.

Steps

1. Display Calculated Fields: In your ASPRunner.NET project, ensure that your calculated fields are included in the edit page. You can set them as read-only fields so that users can see their values but cannot modify them.

   
   

2. Customize the Update SQL Query: You need to create a custom SQL update query that excludes the calculated fields during the update process.

   
   

3. Use the OnUpdate Event: You can use the OnUpdate event to customize the update behavior.

   
   

Example Implementation

Assuming you have a SQL table named Table1 with the following fields:

• Identity: (calculated field, not to be updated)
• Field1: (normal field)
• Field2: (normal field)
• Field3: (normal field)
• CalculatedField1: (calculated field, not to be updated)
• CalculatedField2: (calculated field, not to be updated)

Here’s how you can implement it:

1. Display the Calculated Fields:

   • In the ASPRunner.NET interface, go to the edit page settings and make sure CalculatedField1 and CalculatedField2 are set to display but are marked as read-only.
   
   
   
   

2. Customize the Update Query:

   • Go to the Events tab in ASPRunner.NET and find the BeforeUpdate event or OnUpdate event for your table. Here’s an example of how to write your custom SQL update query:
   
   
   
   

protected void Table1_BeforeUpdate(object sender, EventArgs e)
{
 // Get values from the form
 var valueField1 = Request.Form["Field1"];
 var valueField2 = Request.Form["Field2"];
 var valueField3 = Request.Form["Field3"];
 var recordIdentity = Request.Form["Identity"]; // The identity value to identify the record

 // Construct custom update query
 string updateQuery = $"UPDATE Table1 SET Field1 = @Field1, Field2 = @Field2, Field3 = @Field3 WHERE Identity = @Identity";

 // Create a command object
 using (SqlConnection connection = new SqlConnection("YourConnectionStringHere"))
 {
 using (SqlCommand command = new SqlCommand(updateQuery, connection))
 {
 // Add parameters to prevent SQL injection
 command.Parameters.AddWithValue("@Field1", valueField1);
 command.Parameters.AddWithValue("@Field2", valueField2);
 command.Parameters.AddWithValue("@Field3", valueField3);
 command.Parameters.AddWithValue("@Identity", recordIdentity);

 connection.Open();
 command.ExecuteNonQuery();
 }
 }

 // Cancel the default update operation since we have a custom query
 e.Cancel = true;
}
Explanation
Read-Only Fields: The calculated fields are displayed but not editable.
Custom Update Logic: The BeforeUpdate event constructs a custom SQL update query that explicitly includes only the fields you want to update, excluding the calculated fields.
Parameterization: Using parameters in the SQL query helps to avoid SQL injection and ensures that the values are correctly formatted.
Cancel Default Update: By setting e.Cancel = true, you prevent the default update logic from executing, allowing you to fully control the update process.

Conclusion
By following the above steps, you can successfully display calculated fields on the edit page while preventing them from being included in the update operation. Make sure to adapt the field names and connection string to match your actual database schema and configuration.

Does not seem to work

values["UpdatedByUserID"] = XSession.Session["UserID"];
values["LastUpdateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

// Place event code here.
// Use "Add Action" button to add code snippets.

return true;

protected void PTFRequest_BeforeUpdate(object sender, EventArgs e)
{
// Get values from the form
var PTFTrackingNumber = Request.Form["PTFTrackingNumber"];
var Name = Request.Form["Name"];
var EMPLID = Request.Form["EMPLID"];
var EffectiveDate = Request.Form["EffectiveDate"];
var RetroEffectiveDateGT60 = Request.Form["RetroEffectiveDateGT60"];
var PosFilledOutsidePosting = Request.Form["PosFilledOutsidePosting"];
var PlaceOnPaidAdminLeave = Request.Form["PlaceOnPaidAdminLeave"];
var PlaceOnUnpaidLeave = Request.Form["PlaceOnUnpaidLeave"];
var Relocation = Request.Form["Relocation"];
var PositionNumber = Request.Form["PositionNumber"];
var SupvName = Request.Form["SupvName"];
var MailUnit = Request.Form["MailUnit"];
var SupvPositionNumber = Request.Form["SupvPositionNumber"];
var Jobcode = Request.Form["Jobcode"];
var JobTitle = Request.Form["JobTitle"];
var JobStepIfUnion = Request.Form["JobStepIfUnion"];
var NewAnnualSalary = Request.Form["NewAnnualSalary"];
var NewHourlySalary = Request.Form["NewHourlySalary"];
var TestingDOT_FMCSA = Request.Form["TestingDOT_FMCSA"];
var TestingDOT_PHMSA = Request.Form["TestingDOT_PHMSA"];
var Testing_CIP = Request.Form["Testing_CIP"];
var Testing_FFD = Request.Form["Testing_FFD"];
var Testing_Other = Request.Form["Testing_Other"];
var InitiatingManagerName = Request.Form["InitiatingManagerName"];
var InitiatingManagerEmplID = Request.Form["InitiatingManagerEmplID"];
var InitiatingManagerEmail = Request.Form["InitiatingManagerEmail"];
var InitiatingManagerApprovalDate = Request.Form["InitiatingManagerApprovalDate"];
var InitiatingManagerTitle = Request.Form["InitiatingManagerTitle"];
var InitiatingManagerApproval = Request.Form["InitiatingManagerApproval"];
var SecondLevelName = Request.Form["SecondLevelName"];
var SecondLevelEmplID = Request.Form["SecondLevelEmplID"];
var SecondLevelEmail = Request.Form["SecondLevelEmail"];
var SecondLevelApprovalDate = Request.Form["SecondLevelApprovalDate"];
var SecondLevelTitle = Request.Form["SecondLevelTitle"];
var SecondLevelApproval = Request.Form["SecondLevelApproval"];
var Comments = Request.Form["Comments"];
var DepartmentCode = Request.Form["DepartmentCode"];
var PhysicalLocation = Request.Form["PhysicalLocation"];
var ResourceCode = Request.Form["ResourceCode"];
var ActivityCode = Request.Form["ActivityCode"];
var BusinessUnit = Request.Form["BusinessUnit"];
var ProjectCode = Request.Form["ProjectCode"];
var EarningsCode = Request.Form["EarningsCode"];
//var Relocation_String = Request.Form["Relocation_String"];
var UpdatedByUserID = Request.Form["UpdatedByUserID"];
var LastUpdateTime = Request.Form["LastUpdateTime"];
var InitatingManagerUserID = Request.Form["InitatingManagerUserID"];
var SecondLevelUserID = Request.Form["SecondLevelUserID"];
var PTFStatus = Request.Form["PTFStatus"];
var Cancelled = Request.Form["Cancelled"];
var Processed = Request.Form["Processed"];

string updateQuery = @"UPDATE PTFRequest Set Name = @Name, EMPLID = @EMPLID,EffectiveDate = @EffectiveDate,
RetroEffectiveDateGT60 = @RetroEffectiveDateGT60, PosFilledOutsidePosting = @PosFilledOutsidePosting, PlaceOnPaidAdminLeave = @PlaceOnPaidAdminLeave,
Relocation = @Relocation, PositionNumber = @PositionNumber, MailUnit = @MailUnit,
Jobcode = @Jobcode, JobTitle = @JobTitle, JobStepIfUnion = @JobStepIfUnion, NewAnnualSalary = @NewAnnualSalary, NewHourlySalary = @NewHourlySalary,
TestingDOT_FMCSA = @TestingDOT_FMCSA, TestingDOT_PHMSA = @TestingDOT_PHMSA, Testing_CIP = @Testing_CIP, Testing_FFD = @Testing_FFD, Testing_Other = @Testing_Other,
InitiatingManagerApprovalDate = @InitiatingManagerApprovalDate, InitiatingManagerApproval = @InitiatingManagerApproval, SecondLevelApprovalDate = @SecondLevelApprovalDate,
SecondLevelApproval = @SecondLevelApproval,Comments = @Comments,DepartmentCode = @DepartmentCode, PhysicalLocation = @PhysicalLocation, ResourceCode = @ResourceCode,
ActivityCode = @ActivityCode, BusinessUnit = @BusinessUnit, ProjectCode = @ProjectCode,UpdatedByUserID = @UpdatedByUserID, LastUpdateTime = @LastUpdateTime,
Cancelled = @Cancelled, Processed = @Processed Where PTFTrackingNumber = @PTFTrackingNumber";

// Create a command object
using (SqlConnection connection = new SqlConnection(@"LITDBETSD061\DVNS160001;NSP;userid=nspinquiry;password=InquiryNSP"))
{
using (SqlCommand command = new SqlCommand(updateQuery,connection))
{

command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@EMPLID", EMPLID);
command.Parameters.AddWithValue("@EffectiveDate", EffectiveDate);
command.Parameters.AddWithValue("@RetroEffectiveDateGT60", RetroEffectiveDateGT60);
command.Parameters.AddWithValue("@PosFilledOutsidePosting", PosFilledOutsidePosting);
command.Parameters.AddWithValue("@PlaceOnPaidAdminLeave", PlaceOnPaidAdminLeave);
command.Parameters.AddWithValue("@PlaceOnUnpaidLeave", PlaceOnUnpaidLeave);
command.Parameters.AddWithValue("@Relocation", Relocation);
command.Parameters.AddWithValue("@PositionNumber", PositionNumber);
command.Parameters.AddWithValue("@SupvName", SupvName);
command.Parameters.AddWithValue("@MailUnit", MailUnit);
command.Parameters.AddWithValue("@SupvPositionNumber", SupvPositionNumber);
command.Parameters.AddWithValue("@Jobcode", Jobcode);
command.Parameters.AddWithValue("@JobTitle", JobTitle);
command.Parameters.AddWithValue("@JobStepIfUnion", JobStepIfUnion);
command.Parameters.AddWithValue("@NewAnnualSalary", NewAnnualSalary);
command.Parameters.AddWithValue("@NewHourlySalary", NewHourlySalary);
command.Parameters.AddWithValue("@TestingDOT_FMCSA", TestingDOT_FMCSA);
command.Parameters.AddWithValue("@TestingDOT_PHMSA", TestingDOT_PHMSA);
command.Parameters.AddWithValue("@Testing_CIP", Testing_CIP);
command.Parameters.AddWithValue("@Testing_FFD", Testing_FFD);
command.Parameters.AddWithValue("@Testing_Other", Testing_Other);
command.Parameters.AddWithValue("@InitiatingManagerName", InitiatingManagerName);
command.Parameters.AddWithValue("@InitiatingManagerEmplID", InitiatingManagerEmplID);
command.Parameters.AddWithValue("@InitiatingManagerEmail", InitiatingManagerEmail);
command.Parameters.AddWithValue("@InitiatingManagerApprovalDate", InitiatingManagerApprovalDate);
command.Parameters.AddWithValue("@InitiatingManagerTitle", InitiatingManagerTitle);
command.Parameters.AddWithValue("@InitiatingManagerApproval", InitiatingManagerApproval);
command.Parameters.AddWithValue("@SecondLevelName", SecondLevelName);
command.Parameters.AddWithValue("@SecondLevelEmplID", SecondLevelEmplID);
command.Parameters.AddWithValue("@SecondLevelEmail", SecondLevelEmail);
command.Parameters.AddWithValue("@SecondLevelApprovalDate", SecondLevelApprovalDate);
command.Parameters.AddWithValue("@SecondLevelTitle", SecondLevelTitle);
command.Parameters.AddWithValue("@SecondLevelApproval", SecondLevelApproval);
command.Parameters.AddWithValue("@Comments", Comments);
command.Parameters.AddWithValue("@DepartmentCode", DepartmentCode);
command.Parameters.AddWithValue("@PhysicalLocation", PhysicalLocation);
command.Parameters.AddWithValue("@ResourceCode", ResourceCode);
command.Parameters.AddWithValue("@ActivityCode", ActivityCode);
command.Parameters.AddWithValue("@BusinessUnit", BusinessUnit);
command.Parameters.AddWithValue("@ProjectCode", ProjectCode);
command.Parameters.AddWithValue("@EarningsCode", EarningsCode);
command.Parameters.AddWithValue("@Relocation_String", Relocation_String);
command.Parameters.AddWithValue("@UpdatedByUserID", UpdatedByUserID);
command.Parameters.AddWithValue("@LastUpdateTime", LastUpdateTime);
command.Parameters.AddWithValue("@InitatingManagerUserID", InitatingManagerUserID);
command.Parameters.AddWithValue("@SecondLevelUserID", SecondLevelUserID);
command.Parameters.AddWithValue("@PTFStatus", PTFStatus);
command.Parameters.AddWithValue("@Cancelled", Cancelled);
command.Parameters.AddWithValue("@Processed", Processed);

connection.Open();
command.ExecuteNonQuery();
}
}

// Cancel the default update operation since we have a custom query
e.Cancel = true;
}

Build errors
\ PTFV6.csproj
Microsoft (R) Build Engine version 4.8.9037.0
[Microsoft .NET Framework, version 4.0.30319.42000]
Copyright (C) Microsoft Corporation. All rights reserved.

include\ptfrequest_TableEvents.cs(36,73): error CS1513: } expected [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(173,1): error CS1519: Invalid token 'return' in class, struct, or interface member declaration [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(178,10): error CS1518: Expected class, delegate, enum, interface, or struct [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(181,47): error CS1001: Identifier expected [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(182,56): error CS1001: Identifier expected [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(183,56): error CS1001: Identifier expected [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(195,2): error CS1022: Type or namespace definition, or end-of-file expected [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]
include\ptfrequest_TableEvents.cs(197,1): error CS1022: Type or namespace definition, or end-of-file expected [C:\Users\hschell\Documents\ASPRunnerNETProjects\PTFV6\output\PTFV6.csproj]

I guess we need to start from the very beginning.

What kind of problem are you trying to solve by using Custom Record Update as opposed to the regular Edit page flow?

I have 2 fields that are calculated, PTFNumber, and Status

I want to display them on the edit Page for the user, but when changes are saved, not try to update them.

I'm using ASPRunner.NET, Is this example for ASPRunner.Net, or PHP

If you need to prevent certain fields from being updated simply remove them from values array in BeforeEdit event i.e.

More info:
https://xlinesoft.com/asprunnernet/docs/update_multiple_tables.htm