How can I put "updated by" and "date updated" in three different milestone columns of a GridView when they are updated
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:
Default.aspx.vb:
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);
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);
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"),
GetDate() is current date.
I am sure you have these fields on your gridview markup.
If you are misunderstood, please explain further.
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"),
GetDate() is current date.
I am sure you have these fields on your gridview markup.
If you are misunderstood, please explain further.
ASKER
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. :-)
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. :-)
ASKER
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!
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!
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,u
The construct of your db will ....
how is your table currently structured?
Do you have all in one table?
i.e. ...... milestone1, milestone1date,milestone1u
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,u
The construct of your db will ....
how is your table currently structured?
Do you have all in one table?
i.e. ...... milestone1, milestone1date,milestone1u
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you Arnold, yes that does help make it clear.
Sammy, yes that's exactly it. The, ".FindControl("txtmileston
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!
Sammy, yes that's exactly it. The, ".FindControl("txtmileston
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!
On your gridview markup, add this:
OnRowUpdating = "UpdateCustomer"ASKER
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)
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)
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.
i.e. the check on which subroutine to call, would be the mechanism by which you can differentiate which sets of columns to update.
ASKER
Arggg, sorry about that.
Here is the .aspx:
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. ;-)
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: <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 © 2015 CareerSource Broward
</div>
</form>
</body>
</html>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 Hence the length of 6" OR: IsNullOrEmpty: when(string.IsNullOrEmpty(e.Row.Cells[i].Text))
If e.Row.Cells(8).Text = " " 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 = " " 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 = " " 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 ClassWhat 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. ;-)
ASKER
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.
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.
ASKER
Thank you very much for the help.
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.