Solved

How can I put "updated by" and "date updated" in three different milestone columns of a GridView when they are updated

Posted on 2015-01-22
15
104 Views
Last Modified: 2015-01-26
I've got a GridView with columns for entering the Date of three different "milestones" of customers. (It colors the background of the date cell if the cell is empty AND has passed a calculated date, but that's not relevant to my question)

For each of the three milestones there is a column for "* Date", "* Updated Date", and "* Updated By" with corresponding columns in the database.

How would I update the appropriate "* Updated Date" and "* Updated BY" columns in the database when one of the milestone dates are entered in the GridView.

Desired Process:  User clicks "Edit" on the row for a customer and enters a date into one of the three milestones and then clicks "Update." Done.  GridView displays the new date, and the staff's login name and today's date.

I have a sub in the VB below, called ESUpdatedBy(), but I don't know how to trigger that when the "Employability Skills Date" is entered.  There needs to be subs for "Work Experience Date" and "Placement Services Date" for when those two dates are entered.

Thank you for your suggestions.

Default.aspx:
MERGE [TempTables].[dbo].[WIAYouthITADates] da
USING [TempTables].[dbo].[vw_WIAYouthITA] vw
ON da.PID = vw.ParticipantId

WHEN MATCHED AND
  da.ActualEndDate IS NOT NULL THEN
  DELETE
  
WHEN MATCHED THEN
  UPDATE
  SET 
	da.Client = vw.client,
	da.SSN = vw.last4ssn,
	da.ProgramStart = vw.ProgramStart,
	da.AnticipatedEndDate = vw.AnticipatedEndDate,
	da.ActualEndDate = vw.ActualEndDate

WHEN NOT MATCHED BY TARGET THEN
  INSERT (PID, Client, SSN, ProgramStart, AnticipatedEndDate, ActualEndDate)
  VALUES (vw.ParticipantId, vw.client, vw.last4ssn, vw.ProgramStart, vw.AnticipatedEndDate, vw.ActualEndDate);
   

Open in new window


Default.aspx.vb:
MERGE [TempTables].[dbo].[WIAYouthITADates] da
USING [TempTables].[dbo].[vw_WIAYouthITA] vw
ON da.PID = vw.ParticipantId

WHEN MATCHED AND
  da.ActualEndDate IS NOT NULL THEN
  DELETE
  
WHEN MATCHED THEN
  UPDATE
  SET 
	da.Client = vw.client,
	da.SSN = vw.last4ssn,
	da.ProgramStart = vw.ProgramStart,
	da.AnticipatedEndDate = vw.AnticipatedEndDate,
	da.ActualEndDate = vw.ActualEndDate

WHEN NOT MATCHED BY TARGET THEN
  INSERT (PID, Client, SSN, ProgramStart, AnticipatedEndDate, ActualEndDate)
  VALUES (vw.ParticipantId, vw.client, vw.last4ssn, vw.ProgramStart, vw.AnticipatedEndDate, vw.ActualEndDate);
   

Open in new window

0
Comment
Question by:megnin
  • 8
  • 4
  • 3
15 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 40565902
I am uncertain I understand your question.
your update directive should include the getdate() for updated date column, and the user performing the update for updated by column .

you should also have a transactional audit table that records the customer, date, who updated, and the changes they made.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40566391
Hi megnin,

If I understand you correctly, you only want to enter values for milestone date but have dateupdated and updateby  update themselves automatically?

If so, first, you will need to have store the user that logs into the your system in session.

So, in your update statement, you would do something like:

Update yourTable, set dateUpdated=getDate(), updatedby=session("user"), miltestoneDate='the date you enter'

GetDate() is current date.

I am sure you have these fields on your gridview markup.

If you are misunderstood, please explain further.
0
 
LVL 1

Author Comment

by:megnin
ID: 40566593
Yes, that's correct.  A staff will enter a date into one of the three milestone columns and the UpdatedBy and DateUpdated will be automatic.  I've already grabbed the Logged in username.

Where I need the help is with determining which cell was modified and putting the updated by and date updated information into the correct cells.

I know how to do an UpdatedBy on the record level, but in this application I need to do it at the column level.  Which GridView column was updated?    The three milestone dates each have their own "updated by" column.

Thanks. :-)
0
 
LVL 1

Author Comment

by:megnin
ID: 40566605
Hi Arnold,

My question is; how do I determine which of three columns of the GridView was updated then put the GetDate() into the appropriate column for that milestone.

There are three different milestones, each with their own "date updated."  

Thanks!
0
 
LVL 76

Expert Comment

by:arnold
ID: 40566623
I've not dealt with this, but ....

I would think that the change on gridview is transmitted referencing the column=newvalue

I think you can handle the event without caring which milestone is updated through the use of a transaction table.
the transaction table will have
date, individualid,milestoneid,updatedby

The construct of your db will ....

how is your table currently structured?
Do you have all in one table?
i.e. ...... milestone1, milestone1date,milestone1updatedby,milestone2,milestone2date,milestone2updatedby.....
0
 
LVL 1

Author Comment

by:megnin
ID: 40566724
Arnold,

Yes, my table structure is as you described.  One table and a column for each milestone and each UpdatedBy field.
I need the code specific to referencing a specific cell in the GridView so when that date is updated I can update the appropriate "updated by" column.  If you haven't dealt with it, you may not be able to tell me the specific code for the GridView.  I'm not experienced enough to translate general technique into the code.
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 40566746
Ok, I see.

You will need to use rowIndex (dataKeyNames)

Let's that your gridview Id is called gvCustomers, you will have a unique key identifier like username or primary key on the gridview markup like this:

<asp:GridView ID="gvCustomers" DataKeyNames = "userid" ...>

Open in new window


Then your update statement would be:

    Protected Sub UpdateCustomer(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        Dim LoginId As String = gvCustomers.DataKeys(e.RowIndex).Value.ToString()
        Dim miltestoneDate As TextBox = DirectCast(gvCustomers.Rows(e.RowIndex).FindControl("txtmilestoneDate"), TextBox)
 TextBox)
        Dim strConnString As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
        Using con As New SqlConnection(strConnString)
            Dim query As String = "update YourTabe set milestoneDate= @mdate, dateupdated= @currentDate, updatedby= @updatedby where userid = " + gvCustomers.DataKeys(e.RowIndex).Values(0).ToString() + ""
            Using cmd As New SqlCommand(query)
                cmd.Connection = con
                cmd.Parameters.AddWithValue("@currentDate", getDate())
                cmd.Parameters.AddWithValue("@mdate", txtmilestoneDate.Text)
                cmd.Parameters.AddWithValue("@updatedBy", Session("userid")
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Sub

Open in new window


Something like this.

If you have any questions, please ask
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 76

Expert Comment

by:arnold
ID: 40566754
While I can not tell you how to fix it with a specific code exampe, your update to the grid must include the reference to the column that is being updated.

you can use that column reference possibly in a case or an if check to reference the columntypedate and columntypeupdateby.

presumably you have an event
update table set form.columnname.name (which is the grid position being sent back) =form.columnname.value or something like that.

hopefully, this discussion made clear what you want and someone else who has gridview experience/experties will provide you with the code fix you are looking for.


Alternatively, gridview seems to have triggers which can be used as well, on each column update/insert that will do the update in the appropriate event date, event updated by.
0
 
LVL 1

Author Comment

by:megnin
ID: 40566798
Thank you Arnold, yes that does help make it clear.

Sammy, yes that's exactly it.  The, ".FindControl("txtmilestoneDate"), " is the key part for referencing the particular column.

Tell me if I have this correct... I will need one of those subroutines for each of the three milestones.

Where is the subroutine "called" when it's corresponding date entry column is updated?  Manipulating GridViews is not my strong point.  ;-)  Thank you for your help.  Both of you!
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40566805
On your gridview markup, add this:  
OnRowUpdating = "UpdateCustomer"

Open in new window

, assuming your sub remains same as the one I posted.
0
 
LVL 1

Author Comment

by:megnin
ID: 40566843
It looks like that would determine the row/record that was being updated.  How would it determine which of the three milestones were being updated?

The three milestones.... Oh, crap!  I just noticed my original post.  I did not past the code I intended to in either code block.  My apologies.  Just for clarity let me post the correct markup and code here below... (I need to switch computers)
0
 
LVL 76

Expert Comment

by:arnold
ID: 40566844
As I am speaking without the background, I would think a single subrouting can do the trick by checking which column is being updated to correctly update the other two in the same step
i.e. the check on which subroutine to call, would be the mechanism by which you can differentiate which sets of columns to update.
0
 
LVL 1

Author Comment

by:megnin
ID: 40566862
Arggg, sorry about that.

Here is the .aspx:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>WIA ITA</title>
</head>
<body>
    <form id="form1" runat="server">

	           <div style="padding:5px;"><img src="images/CSBD_208x80.png" alt="Logo" /></div>

    <div>

    <h1 style="color: #0D76BD;">Youth Scholarship Program ITA Admin Page: &nbsp; &nbsp; <asp:Label ID="WelcomeCenter" runat="server" Text=" [Office Label]"></asp:Label></h1>


        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowSorting="True" DataKeyNames="RecordID" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" ForeColor="Black" >
            <AlternatingRowStyle BackColor="#EFEFEF" />
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="RecordID" HeaderText="RecordID" InsertVisible="False" ReadOnly="True" SortExpression="RecordID" Visible="False" />
                <asp:BoundField DataField="PID" HeaderText="Participant ID" SortExpression="PID" ReadOnly="True" Visible="False">
                <ItemStyle Wrap="False" />
                </asp:BoundField>
                <asp:BoundField DataField="Client" HeaderText="Client" SortExpression="Client" ReadOnly="True" >
                <ItemStyle Wrap="False" />
                </asp:BoundField>
                <asp:BoundField DataField="SSN" HeaderText="SSN" SortExpression="SSN" ReadOnly="True" />
                <asp:BoundField DataField="ProgramStart" HeaderText="Program Start" SortExpression="ProgramStart" ReadOnly="True" DataFormatString="{0:d}" HtmlEncode="False" />
                <asp:BoundField DataField="AnticipatedEndDate" HeaderText="Anticipated End Date" SortExpression="AnticipatedEndDate" ReadOnly="True" DataFormatString="{0:d}" HtmlEncode="False" />
                <asp:BoundField DataField="ActualEndDate" HeaderText="Actual End Date" SortExpression="ActualEndDate" ReadOnly="True" DataFormatString="{0:d}" HtmlEncode="False" />

                <asp:BoundField DataField="EmployabilitySkillsDate" HeaderText="Employability Skills Date" SortExpression="EmployabilitySkillsDate" DataFormatString="{0:d}" HtmlEncode="False" >
                <HeaderStyle ForeColor="#F26122" />
                <ItemStyle HorizontalAlign="Center" />
                </asp:BoundField>
                <asp:BoundField DataField="ESUpdatedDate" HeaderText="ES Updated Date" SortExpression="ESUpdatedDate" ReadOnly="True" DataFormatString="{0:d}" HtmlEncode="False" Visible="False" >
                <HeaderStyle ForeColor="#F26122" />
                </asp:BoundField>
                <asp:BoundField DataField="ESUpdatedBy" HeaderText="ES Updated By" SortExpression="ESUpdatedBy" ReadOnly="True" Visible="False" >
                <HeaderStyle ForeColor="#F26122" />
                </asp:BoundField>
                <asp:BoundField DataField="WorkExperienceDate" HeaderText="Work Experience Date" SortExpression="WorkExperienceDate" DataFormatString="{0:d}" HtmlEncode="False" >
                <HeaderStyle ForeColor="#0D76BD" />
                <ItemStyle HorizontalAlign="Center" />
                </asp:BoundField>
                <asp:BoundField DataField="WEUpdatedDate" HeaderText="WE Updated Date" SortExpression="WEUpdatedDate" ReadOnly="True" DataFormatString="{0:d}" HtmlEncode="False" Visible="False" >
                <HeaderStyle ForeColor="#0D76BD" />
                </asp:BoundField>
                <asp:BoundField DataField="WEUpdatdBy" HeaderText="WE Updatd By" SortExpression="WEUpdatdBy" ReadOnly="True" Visible="False" >
                <HeaderStyle ForeColor="#0D76BD" />
                </asp:BoundField>
                <asp:BoundField DataField="PlacementServicesDate" HeaderText="Placement Services Date" SortExpression="PlacementServicesDate" DataFormatString="{0:d}" HtmlEncode="False" >
                <HeaderStyle ForeColor="#54B948" />
                <ItemStyle HorizontalAlign="Center" />
                </asp:BoundField>
                <asp:BoundField DataField="PSUpdatedDate" HeaderText="PS Updated Date" SortExpression="PSUpdatedDate" ReadOnly="True" DataFormatString="{0:d}" HtmlEncode="False" Visible="False" >
                <HeaderStyle ForeColor="#54B948" />
                </asp:BoundField>
                <asp:BoundField DataField="PSUpdatedBy" HeaderText="PS Updated By" SortExpression="PSUpdatedBy" ReadOnly="True" Visible="False" >
                <HeaderStyle ForeColor="#54B948" />
                </asp:BoundField>
                <asp:BoundField DataField="IPAddress" HeaderText="IP Address" SortExpression="IPAddress" Visible="False" >
                <ItemStyle Wrap="False" />
                </asp:BoundField>
            </Columns>
            <EditRowStyle BackColor="#CCFFCC" />
            <FooterStyle BackColor="#CCCCCC" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#808080" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#383838" />
        </asp:GridView>   

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TempTablesWIAYouthITADatesConnectionString %>" 
            SelectCommand="SELECT [RecordID], [PID], [Client], [SSN], [ProgramStart], [AnticipatedEndDate], [ActualEndDate],
                                  [EmployabilitySkillsDate], [ESUpdatedDate], [ESUpdatedBy], 
                                  [WorkExperienceDate], [WEUpdatedDate], [WEUpdatdBy], 
                                  [PlacementServicesDate], [PSUpdatedDate], [PSUpdatedBy], 
                                  [IPAddress] 
                FROM [WIAYouthITADates] 
                ORDER BY Client, [AnticipatedEndDate] DESC, [EmployabilitySkillsDate] DESC, WorkExperienceDate DESC, PlacementServicesDate DESC" 
            DeleteCommand="DELETE FROM [WIAYouthITADates] WHERE [RecordID] = @RecordID" 
            UpdateCommand="UPDATE [WIAYouthITADates] 
                SET [EmployabilitySkillsDate] = @EmployabilitySkillsDate, 
                    [WorkExperienceDate] = @WorkExperienceDate, 
                    [PlacementServicesDate] = @PlacementServicesDate 
                WHERE [RecordID] = @RecordID">
            <DeleteParameters>
                <asp:Parameter Name="RecordID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="EmployabilitySkillsDate" Type="DateTime" />
                <asp:Parameter Name="WorkExperienceDate" Type="DateTime" />
                <asp:Parameter Name="PlacementServicesDate" Type="DateTime" />
                <asp:Parameter Name="RecordID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>

    </div>
               Logged in as: <asp:Label ID="lblUserName1" runat="server" Text="Label"></asp:Label>
        <br />
               <%--<asp:Label ID="lblUserName2" runat="server" Text="Label"></asp:Label>--%>


	<div style="text-align: center">
		Copyright &copy; 2015 CareerSource Broward
	</div>

    </form>
</body>
</html>

Open in new window

and the .aspx.vb:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI.WebControls

Partial Class _Default
    Inherits System.Web.UI.Page
    ' Two ways to get logged in User Name:
    Dim iUserName As String = "UserName" ' Environment.UserName       ' Uses ClearDomain Function.
    Dim RRUser As String = "UserName"    ' Request.LogonUserIdentity  ' Use one or the other of these methods of getting the User Name, not both.

    Protected Function ClearDomain(ByVal sItem As String) As String
        'Use this function to get rid of the DOMAIN\ part of the Logged In Username.
        'http://allthingsdotnet.net/854/asp-net-function-to-get-username-from-current-user

        Dim sLoc As Integer = sItem.IndexOf("\") + 1
        Dim sOutPut As String
        sOutPut = sItem.Substring(sLoc)
        Return sOutPut
    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        If Not IsPostBack Then
            Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("TempTablesWIAYouthITADatesConnectionString").ConnectionString)
            Dim DBCmd As New SqlCommand
            Dim sql As String
            sql = "MERGE [TempTables].[dbo].[WIAYouthITADates] da USING [TempTables].[dbo].[vw_WIAYouthITA] vw ON da.PID = vw.ParticipantId  WHEN MATCHED AND   da.ActualEndDate IS NOT NULL THEN   DELETE    WHEN MATCHED THEN   UPDATE   SET  	da.Client = vw.client, 	da.SSN = vw.last4ssn, 	da.ProgramStart = vw.ProgramStart, 	da.AnticipatedEndDate = vw.AnticipatedEndDate, 	da.ActualEndDate = vw.ActualEndDate  WHEN NOT MATCHED BY TARGET THEN   INSERT (PID, Client, SSN, ProgramStart, AnticipatedEndDate, ActualEndDate)   VALUES (vw.ParticipantId, vw.client, vw.last4ssn, vw.ProgramStart, vw.AnticipatedEndDate, vw.ActualEndDate); "

            'sql = sql + "VALUES (@DateIssued, @DeliverTo, @TotalDollars, @ReasonForRequest, @Remarks, @Department, @VendorDate, @VendorInfoID, @VendorPONO, @VendorFundingCode, @VPApproval, @ExecutiveApproval, @StateContract, @StateContractNo, @StateContractEndDate, @SubmittedBy, getdate(), @Policies, @Crimes, @Debarment, @IPAddress, @ClientBrowser); Select SCOPE_IDENTITY()"
            'sql = "INSERT INTO Requisitions (Event, EventLocation, EventDate, LastName, FirstName, Telephone, Email, SSN, Occupation, JobTitle, LastSalary, CompletedDegree, BeenManager, Earned35K, RegisteredEFM, EFMUserID, Comments, JobOrder1, JobOrder2, JobOrder3, AppliedOnWebsite, ApplicationDate,IPAddress,ClientBrowser) VALUES (@ddlPPNService, @ddlCenter, @ddlEventDate, @txtLastName, @txtFirstName, @txtPhone, @txtEmail, @txtSSN4, @txtOccupation, @txtJobTitle, @radLastSalary, @CompletedDegree, @BeenManager, @Earned35K, @RegisteredEFM, @EFMUserID, @txtComments, @cbJobOrder1, @cbJobOrder2, @cbJobOrder3, @radApply, getdate(), @IPAddress, @ClientBrowser); Select SCOPE_IDENTITY()"
            'sql = "IF (SELECT COUNT(SSN) FROM Applicants WHERE (SSN = @txtSSN) AND (keyApplicantID > 200900000)) > 0  SELECT -1 as error  FROM Applicants ELSE BEGIN INSERT INTO Applicants (householdIncomeID,HouseholdArrangementID,NumberInHousehold,NumberofAdults,NumberofChildren,LastName,FirstName,MI,DOB,Gender,Race,Ethnicity,Education,CountryOfBirth,LanguageAtHome,CulturalInfluence,SSN,StreetName,CityID,ZipID,TeenParent,FosterChildID,ddlWorkInUS,txtSuffix,txtAge,txtState,txtHomeTelephone,txtCellPhone,txtOtherPhone,txtEmailAddress,txtParentsName,txtParentsTelephone,txtParentsCellPhone,txtParentsOtherPhone,txtSchool,ddlWorksiteLocationPreference,ddlWorkHours,ddlTransportation,txtOtherLanguages,radMonFriYes,radSaturdayYes,radSundayYes,ddlWorkPreference,cbDeficientBasicSkills,cbSchoolDropout,cbHomeless,cbFosterChild,cbTeenParent,cbLearningDisabilities,radOffender,NameOfOffense,NameOfCourt,DispositionOfCase,CaseDate,ParticipatedBefore,HowManyYears,WhereDidYouWork,txtListSpecialAccommodations,OnlineApplicationDate,txtOrgReferredBy,ddlActivityType,SelectiveService,cbPregnant,radInSchool,ddlHighestGradeComp,CurrentlyEmployed,CurrentEmployer,CurrentEmpName,CurrentEmpAddress,CurrentEmpPhone,CurrentPayRate,DateOfEmpFrom,DateOfEmpTo,FamilyIncome,radPublicAssistance,ddlPublicAssistance,txtPublicAssistance,ddlIncomeSource,txtIncomeSource,Parent1Employer,Parent1PayRate,Parent1Rate,Parent2Employer,Parent2PayRate,Parent2Rate,radMilitaryBrat,IPAddress,ClientBrowser) VALUES (@ddlHouseholdIncome,@ddlHomeArrangement,@txtPersonsInHome,@txtAdultsInHome,@txtChildrenInHome,@txtLastName,@txtFirstName,@txtMiddleInitial,@txtDOB,@radGender,@ddlRace,@ddlEthnicity,@ddlGrade,@ddlCountryOfBirth,@ddlLanguageAtHome,@ddlCulturalInfluence,@txtSSN,@txtStreetName,@ddlCity,@ddlZipCode,@cbTeenParent,@cbFosterChild,@ddlWorkInUS,@txtSuffix,@txtAge,@txtState,@txtHomeTelephone,@txtCellPhone,@txtOtherPhone,@txtEmailAddress,@txtParentsName,@txtParentstelephone,@txtParentsCellPhone,@txtParentsOtherPhone,@txtSchool,@ddlWorksiteLocationPreference,@ddlWorkHours,@ddlTransportation,@txtOtherLanguages,@radMonFriYes,@radSaturdayYes,@radSundayYes,@ddlWorkPreference,@cbDeficientBasicSkills,@cbSchoolDropout,@cbHomeless,@cbFosterChild,@cbTeenParent,@cbLearningDisabilities,@radOffender,@NameOfOffense,@NameOfCourt,@DispositionOfCase,@CaseDate,@ParticipatedBefore,@HowManyYears,@WhereDidYouWork,@txtListSpecialAccommodations,getdate(),@txtOrgReferredBy,@ddlActivityType,@SelectiveService,@cbPregnant,@radInSchool,@ddlHighestGradeComp,@CurrentlyEmployed,@CurrentEmployer,@CurrentEmpName,@CurrentEmpAddress,@CurrentEmpPhone,@CurrentPayRate,@DateOfEmpFrom,@DateOfEmpTo,@FamilyIncome,@radPublicAssistance,@ddlPublicAssistance,@txtPublicAssistance,@ddlIncomeSource,@txtIncomeSource,@Parent1Employer,@Parent1PayRate,@ddlParent1PayRate,@Parent2Employer,@Parent2PayRate,@ddlParent2PayRate,@radMilitaryBrat,@IPAddress,@ClientBrowser); Select SCOPE_IDENTITY() End"
            DBConn.Open()
            Try
                'Add Statement 
                DBCmd = New SqlCommand(sql, DBConn)

                DBCmd.ExecuteNonQuery()
                'DBCmd.ExecuteScalar()

            Catch exp As Exception
                Response.Write(exp.Message)
            End Try
            'Close Database connection 
            DBCmd.Dispose()
            DBConn.Close()
            DBConn = Nothing

        End If
        ' Two ways to get logged in User Name:
        iUserName = ClearDomain(User.Identity.Name.ToString)
        RRUser = Request.LogonUserIdentity.Name.Substring(Request.LogonUserIdentity.Name.LastIndexOf("\") + 1)

        Dim thirdOctet As String
        Dim RemoteAddr As String
        RemoteAddr = Request.ServerVariables("REMOTE_ADDR")
        thirdOctet = RemoteAddr.Substring(8, 3)
        Select Case thirdOctet
            Case "101" ' Corporate
                WelcomeCenter.Text = "Lakeside Office"
                'SqlDataSource1.SelectParameters.Add("@Office", "Lakeside Office")
            Case "135" ' North
                WelcomeCenter.Text = "North One-Stop"
            Case "104" ' Central 2610
                WelcomeCenter.Text = "Central One-Stop"
            Case "130" ' Central 2550
                WelcomeCenter.Text = "Central One-Stop"
            Case "110" ' South
                WelcomeCenter.Text = "South One-Stop"
            Case Else
                WelcomeCenter.Text = "Error"
        End Select
        lblUserName1.Text = iUserName.ToString
        'lblUserName2.Text = RRUser.ToString
    End Sub

    Protected Sub ESUpdatedBy()
        '|----------------------------------------------------------------------|
        '|   Add logged in username to ESUpdatedBy, GetDate() to ESUpdatedDate  |
        '|----------------------------------------------------------------------|
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("TempTablesWIAYouthITADatesConnectionString").ConnectionString)
        Dim DBCmd As New SqlCommand
        DBConn.Open()

        Try
            DBCmd = New SqlCommand("UPDATE [TempTables].[dbo].[WIAYouthITADates] SET(ESUpdatedBy, ESUpdatedDate) VALUES (@ESUpdatedBy, getdate())", DBConn)
            DBCmd.Parameters.Add("@ESUpdatedBy", SqlDbType.VarChar).Value = iUserName.ToString()
            DBCmd.ExecuteNonQuery()
        Catch exp As Exception
            Response.Write(exp)
        End Try

        DBCmd.Dispose()
        DBConn.Close()
        DBConn = Nothing
        '|----------------------------------------------------------------------|
        '|   Add logged in username to ESUpdatedBy, GetDate() to ESUpdatedDate  |
        '|----------------------------------------------------------------------|
    End Sub

    Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound
        If Not IsDBNull(DataBinder.Eval(e.Row.DataItem, "AnticipatedEndDate")) Then

            Dim ps As Date = _
              (DataBinder.Eval(e.Row.DataItem, "ProgramStart"))
            Dim d As Date = _
              (DataBinder.Eval(e.Row.DataItem, "AnticipatedEndDate"))

            'FOR EMPLOYABILITY SKILLS
            'Check if the date cell is empty:  "When a bound GridView cell contains no data, ASP.NET fills it with &nbsp; Hence the length of 6"  OR: IsNullOrEmpty: when(string.IsNullOrEmpty(e.Row.Cells[i].Text))
            If e.Row.Cells(8).Text = "&nbsp;" Then
                Dim es As Date = _
                    DateAdd("d", (DateDiff("d", ps, d) * 0.25), ps)          'Employability Skills.  Add .25 the difference between Start and End to Start.  Gives mid point.
                If (Now() > es) And e.Row.RowType <> DataControlRowType.Header Then
                    'e.Row.Cells(8).Text = es.ToString("d")  'Put the date of the trigger in just for testing.
                    'e.Row.BackColor = Drawing.Color.LightPink
                    'e.Row.BackColor = Drawing.Color.FromArgb(255, 255, 153)
                    e.Row.Cells(8).ForeColor = Drawing.Color.Red
                    e.Row.Cells(8).Font.Bold = True
                    'e.Row.Cells(8).BackColor = Drawing.Color.Yellow
                    e.Row.Cells(8).BackColor = Drawing.Color.FromArgb(255, 255, 153)
                End If
            End If

            'FOR WORK EXPERIENCE
            If e.Row.Cells(11).Text = "&nbsp;" Then 'If it's blank
                Dim we As Date = _
                    DateAdd("d", (DateDiff("d", ps, d) * 0.5), ps)           'Work Experience.  Add .5 the difference between Start and End to Start.  Gives mid point.
                If (Now() > we) And e.Row.RowType <> DataControlRowType.Header Then
                    'e.Row.Cells(11).Text = we.ToString("d")    'Put the date of the trigger in just for testing.
                    'e.Row.BackColor = Drawing.Color.DeepPink
                    'e.Row.BackColor = Drawing.Color.FromArgb(255, 204, 153)
                    e.Row.Cells(11).ForeColor = Drawing.Color.Red
                    e.Row.Cells(11).Font.Bold = True
                    'e.Row.Cells(11).BackColor = Drawing.Color.Yellow
                    e.Row.Cells(11).BackColor = Drawing.Color.FromArgb(255, 204, 153)
                End If
            End If

            'FOR PLACEMENT SERVICES DATE
            If e.Row.Cells(14).Text = "&nbsp;" Then  'If blank
                Dim psDifference As Long = DateDiff(DateInterval.Day, Now(), d)  'difference between Anticipated End and Now
                If (psDifference < 90) And e.Row.RowType <> DataControlRowType.Header Then  '< Days before Anticipated End Date.  [90 days prior to Anticipated End Date]        '(d.Day >= 30) Then    '(d.Minute >= 30) Or (d.Hour > 0) Then
                    'ADD:  And d is Nothing or PlacementServicesDate = "" so it isn't highlighted when there's a date in the field.
                    ' color the background of the row pink and the Placement Services Date cell Yellow.
                    'e.Row.BackColor = Drawing.Color.Pink
                    'e.Row.Cells(14).ForeColor = Drawing.Color.Red
                    'e.Row.BackColor = Drawing.Color.FromArgb(255, 153, 153)
                    e.Row.Cells(14).Font.Bold = True
                    'e.Row.Cells(14).BackColor = Drawing.Color.Yellow
                    e.Row.Cells(14).BackColor = Drawing.Color.FromArgb(255, 153, 153)
                    'e.Row.Cells(8).Text = d.AddDays(90).ToString("d")                      'd.ToString("d")
                    'e.Row.Cells(14).Text = Now.AddDays(psDifference).ToString("d")

                    '    'e.Row.Cells(17).Text = d.AddDays(90)
                    'ElseIf d.Minute >= 15 Then
                    '    ' color the background of the row yellow
                    '    e.Row.BackColor = System.Drawing.ColorTranslator.FromHtml("#F3F781") 'Medium Yellow
                    '    e.Row.Cells(7).ForeColor = Drawing.Color.DarkRed
                    'Else
                    '    'e.Row.BackColor = System.Drawing.Color.Blue
                    '    'e.Row.BackColor = Drawing.Color.Green
                    '    'e.Row.Cells(7).ForeColor = Drawing.Color.Coral
                    '    'e.Row.Cells(7).Font.Bold = True
                End If
            End If
        End If

        'Sets the Header Row background color back to black, except for the cells where data is highlighted.
        'If e.Row.RowType = DataControlRowType.Header Then
        '    e.Row.BackColor = System.Drawing.Color.Black
        'End If

    End Sub

End Class

Open in new window


What I posted in the original question was just the SQL statement to update the table I'm working with.  Doh!  Sorry about that.  You did give me the correct information, despite me not giving you good reference information.  Thank you for you great patience.  ;-)
0
 
LVL 1

Author Comment

by:megnin
ID: 40566878
I don't think you really need the markup and code at this point.  But I wanted to include it because I thought I had already.  I can't edit the original to fix it.

The subroutine you posted looks spot on for specifying one particular cell, but  I'm still not too clear on how to determine which of the three dates are entered and then updating it's corresponding "updated by" column.
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 40570492
Thank you very much for the help.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now