Use the Before Show Event to Alter a Label's Value

1. Select the Label user_id_assign_by in the Project Explorer.
2. In the Properties window click on the Data tab.
3. Select Text in the Data Type property.
4. Switch to the Events tab.
5. Right-click on Before Show event and select Add Code.... .

   CodeCharge Studio should then automatically switch to Code view.
   

   

6. Once in Code view, replace the following text:

   C#

   // Write your own code here.

   VB.Net

   ` Write your own code here.

   with the following:

   C#

   DataAccessObject NewDao = Settings.IntranetDBDataAccessObject;
   if (IsInsertMode)
   {
     SqlCommand userNameCmd = new SqlCommand("SELECT emp_name FROM employees " + 
                              "WHERE emp_id=" + NewDao.ToSql(DBUtility.UserId.ToString(), FieldType.Integer), NewDao);
     tasksuser_id_assign_by.Text = userNameCmd.ExecuteScalar().ToString();
   }
   else
   {
     SqlCommand userNameCmd = new SqlCommand( "SELECT emp_name FROM employees " +
                              "WHERE emp_id=" + NewDao.ToSql(item.user_id_assign_by.GetFormattedValue, FieldType.Integer), NewDao);
     tasksuser_id_assign_by.Text = userNameCmd.ExecuteScalar().ToString();
   }

   VB.Net

   Dim NewDao As DataAccessObject = Settings.IntranetDBDataAccessObject
   If IsInsertMode = True Then
     tasksuser_id_assign_by.Text = Convert.ToString(NewDao.ExecuteScalar( _
                                   "SELECT emp_name FROM employees WHERE emp_id="& _
                                   DBUtility.UserId ))
   Else 
     tasksuser_id_assign_by.Text = Convert.ToString(NewDao.ExecuteScalar( _
                                   "SELECT emp_name FROM employees WHERE emp_id="& _
                                   Convert.ToString( tasksuser_id_assign_by.Text)))
   End if

   
   

   In the above code snippet, we have an if block which checks the value of the Boolean variable IsInsertMode. This variable is generated by CodeCharge Studio within the Before Show event of the record forms. Since the same CodeCharge Studio record form can be used to update records, delete records as well as insert new records, this variable is used to determine the state of the record form at runtime. If this variable is true, then the record form is inserting a new record, else it's either updating or deleting the record.

   Both the if and else blocks define a SqlCommand object, which as discussed earlier helps you to execute commands against the database. The SQL query passed to both methods returns the name of the user stored in the emp_name field of the employees table. Once the name of the user is retrieved, it's set to the Label tasksuser_id_assign_by. The only difference between the two SQL queries is that if the user is inserting a new record i.e. the variable IsInsertMode is true, you pass the user ID of the currently logged user from the DBUtility.UserId variable. If the user is updating or deleting the record, then you pass the user ID of the user who was previously assigned the task. The user_id_assign_by property of the item object contains that value retrieved from the database.

   The whole code reads approximately as follows:

   • If a new record is being created:
     Use the value of DBUtility.UserId to obtain the name of the currently logged in user and assign this name to the value of the tasksuser_id_assign_by Label.
   • If a record is being edited:
     Use the value of tasksuser_id_assign_by to obtain the name of the user to whom the task was assigned and assign this name to the value of the tasksuser_id_assign_by Label.

Next: Add an Hidden "Assigned By" Field to Auto-Update New Tasks