locked
Date/Time Stamp in classic asp RRS feed

  • Question

  • User-1211567575 posted

    I have a legacy website (Frontpage 2000) using an Access 2000 database.

    I added a date/time field to a table and set the default to:
    =Now()

    I added the time/date field to a form in Frontpage.

    Is there something I can add to the .asp page to force the date/time field to enter the current date/time when the record is updated?

    here is the current way the time/date field (ChangeDate1) looks in the .asp page:

    <input NAME="ChangeDate1" SIZE="8" VALUE="<%=FP_FieldHTML(fp_rs,"ChangeDate1")%>"></td>

    Here is the current SQL update statement I  created in the frontpage update .asp page:

    ------------
    Update StatusQuery set changedate1='::changedate1::'

    ,projectassessmentstatus='::projectassessmentstatus::'

    ,projectassessmentstatuscomment='::projectassessmentstatuscomment::'

    ,projectassessmentstatusmonth='::projectassessmentstatusmonth::'
    ,projectassessmentstatusdate='::projectassessmentstatusdate::'
    ,projectassessmentstatusyear='::projectassessmentstatusyear::'


    where id=::id::

    ----------------
    I realize that Microsoft no longer supports either frontpage or access 2000, but can you give me a hint as to where to look for the solution if you are unable to help me?
    I still need to work with this platform for now and cannot go to asp.net just yet
    Thanks,

    Ron

    Friday, May 3, 2019 11:47 AM

Answers

  • User-1211567575 posted

    SUCCESS!!!

    I just had to add the comma in front of the expression in the sql update statement

    ,lastupdated=now()

    It works!

    I want to thank you so much for helping me with this.


    Ron

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Tuesday, May 14, 2019 9:35 AM

All replies

  • User-848649084 posted

    Hi Ron,

    Update StatusQuery set changedate1='::changedate1::'

    ,projectassessmentstatus='::projectassessmentstatus::'

    ,projectassessmentstatuscomment='::projectassessmentstatuscomment::'

    ,projectassessmentstatusmonth='::projectassessmentstatusmonth::'
    ,projectassessmentstatusdate='::projectassessmentstatusdate::'
    ,projectassessmentstatusyear='::projectassessmentstatusyear::'


    where id=::id::

    From your post I can see that you just set the date using now() in the database, I suggest you update date field when you fire update query.

    "Update tablename SET date = "& NOW() &" WHERE ID = 5"

    Regards,

    Jalpa.

    Monday, May 6, 2019 7:56 AM
  • User-1211567575 posted

    Hi, Japla. Thanks for responding.

    Just to be clear, in the update statement above, fields from table(s) in a query (startquery) are being updated (I took a few lessons for AspNet, and I think Access's definition or construction of a query is a bit different than the way a query is built in MySQL. Correct me if I'm wrong on that.

    The fields:

    projectassessmentstatus

    projectassessmentstatuscomment, etc. are fields from within a table called Status, and are within the query (StatusQuery)

    I added a time field to the same table (changedate1) which is the first field to be updated above.

    I have since removed it, but as per your suggested update statement:
    "Update tablename SET date = "& NOW() &" WHERE ID = 5"

    Are you suggesting that I create another time field within the table (which will go into StatusQuery) replace the above:

    Update StatusQuery set changedate1='::changedate1::' (changedate1 is no longer within the table and/or query)

    ,projectassessmentstatus='::projectassessmentstatus::' etc.
    with
    "Update Statusquery SET date = "& NOW() &" WHERE ID = 5"
    Or is the SET date = "& NOW() &" WHERE ID = 5 what I would place in the access field properties?

    In other words, what would the update statement look like if I want to update a new time field plus the other fields simultaneously. Or is the
    SET date = "& NOW() &" WHERE ID = 5 something I would add within the .asp page itself?

    Sorry to be such an amateur about this.

    Ron



    Monday, May 6, 2019 2:29 PM
  • User-848649084 posted

    Could you share some sample database field and insert and update the code with query string? try to run failed request tracing and get more detail.

    Tuesday, May 7, 2019 1:54 AM
  • User-1211567575 posted

    Jalpa:

    I have created an Access 2000 database for you as requested. Howerever,

    I can't find a button to attach my file into this reply. Please advise.

    Ron

    Tuesday, May 7, 2019 3:47 PM
  • User-848649084 posted

    Hi,

    Could you share your database field with datatype like below:

    Thursday, May 9, 2019 2:06 AM
  • User-1211567575 posted

    Jalpa:

    I took a screenshot showing the format values of each field at the bottom:

    Please click on the following One-Drive link:

    https://1drv.ms/f/s!AkVNIj06hxI7dGv2y5lyXfXfCT0

    Thanks,

    Ron

    Thursday, May 9, 2019 2:52 AM
  • User-1211567575 posted

    Jalpa:

    I could not access your last message, where you asked me:

    Hi Ron,

    Now you could access one drive, could you please share your sample page with the update and insert code which you get error or issue and sample database?  

    Regards,

    Jalpa

    Here is the link to several screenshots:

    https://1drv.ms/f/s!AkVNIj06hxI7gQW_3ccobAV8tqgr


    I did not know if you wanted me to send the sample page in Frontpage Edit Form or just the pages as they appear online.

    1- Online Test Database1: Shows the entry form before submission to open sample .asp page

    2- Online Test Database2: Shows the entry form after submitting the code (S-1111)

    3-Source1-Shows the html code prior to opening the sample page

    4-Source2-Shows the first part of the html code after opening the sample page.

    5-Source3-Shows the 2nd part of the html code after opening the sample page
    6-Source 4-Shows the 3rd part of the html code after opening the sample page


    To view the sample page online, please go to http://www.werenovate.ca/test2/testdatabase.asp

    When you update the opened sample page, I want the filed called "LastUpdated" to automatically stamp the date and time of the update. In the properties (format) setting within the database I sent you, the only setting I made for the field "lastUpdated" was to time/date. I do not know what to put in there (if there is something that needs to be changed in those settings) to get

    the date to automatically time stamp.

    In the update page, as per my last message, the sql update statement that I created within frontpage is:

    Update table1 set city='::city::'

    ,phone='::phone::'
    ,address='::address::'


    where id=::id::

    The name of the sample update page is testdatabaseup.asp, but would only be visible in editing mode of Frontpage. It is not viewable online.

    Yesterday (May 5, 2019) I sent you the database (TekTips.mdb) with OneDrive.

    I hope you receive this message, as the one you sent me yesterday is not available to reply to
    Ron

    Thursday, May 9, 2019 12:50 PM
  • User-848649084 posted

    Hi , I asked you about the code of the page. with a query string like below:

    <html>
    <body> 
    
    
    <form method="post" name="form">
    <!--First Name: <input type="text" name="fname"><br><br />-->
    
    <input type="Submit" value="More_Info" name="btn">
    
    </form>
    <%
        btnv=Request.form("btn")
       ' Response.Write (btnv)
        If btnv = "More_Info" Then
            Dim objConn
            Set objConn = Server.CreateObject("ADODB.Connection")
            objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\aspsamplesite\Database1.mdb;"
            objConn.Open
    
            'f_name=request.form("fname")
            'f_name=request.form("fname")
            'Response.Write (f_name)
            Set str = objConn.execute("SELECT * FROM Table1 WHERE fname='namea';")
     
            Response.Write("<table border=1>")
        Response.Write "<tr><td> fname </td><td> lname</td></tr>"
            If str.BOF And str.EOF Then
            ' No data
            Else
            Do While (Not str.EOF)
            Response.Write "<tr><td>" & str("fname") & "</td><td>" & str("lname") & "</td></tr>"
            str.MoveNext
            Loop
            End If
            
    Response.Write("</table>")
    End If
    
    %>
    </body> 
    </html>



    Friday, May 10, 2019 1:26 AM
  • User-1211567575 posted

    The following one-drive folder contains the database copies of the online page (I think that may be the code you are seeking) in Word documents.

    https://1drv.ms/f/s!AkVNIj06hxI7gQaw9vMontDyfClH

    Please advise if you need anything more.

    Ron

    Friday, May 10, 2019 10:20 AM
  • User-848649084 posted

    Hi Ron,

    Try to add today date in textbox using below code:

    d_today=Now
    Response.Write d_today

    and use that date when you are updating the database.

    You could also get the last modified date of database using below code.

    dim fs,f
    set fs=Server.CreateObject("Scripting.FileSystemObject")
    set f=fs.GetFile("D:\aspfrontpage\TekTips.mdb")
    Response.Write("File last modified on: ")
    Response.Write(f.DateLastModified)
    set f=nothing
    set fs=nothing

    Monday, May 13, 2019 7:32 AM
  • User-1211567575 posted

    Jalpal:

    Where do I place either of the codes above?

    The field "lastupdated" is within table 1 of TekTips.mdb.

    Please view the screenshot of the database editing pag within table1 at

    https://1drv.ms/u/s!AkVNIj06hxI7gQzbgwop6i1YLtWH

    The datatype for the field "lastupdated" is set to Time/Stamp, but I have not entered or added anything else to the filed properties.

    1- Do either of the codes you sent me go somewhere into the database field itself?
    Or somewhere in the bottom section (format, etc.) ?


    2-Or does either code belong somewhere within the SQL Update Statement I created through Frontpage below?

    Currently, the update statement does not
    refer to the time field named "lastupdated" :

    Update table1 set city='::city::'

    ,phone='::phone::'
    ,address='::address::'


    where id=::id::


    Please advise
    Ron

    Monday, May 13, 2019 10:07 AM
  • User-848649084 posted

    You could try to modify the query and try to set 

    Update table1 set city='::city::'

    ,phone='::phone::'
    ,address='::address::',

    lastupdated=now()


    where id=::id::

    or you could run macro as suggested in below article:

    https://support.office.com/en-ie/article/store-the-date-and-time-when-a-record-is-modified-0c46efc5-5b43-4751-b3a8-c246505af66d

    Tuesday, May 14, 2019 5:41 AM
  • User-1211567575 posted

    SUCCESS!!!

    I just had to add the comma in front of the expression in the sql update statement

    ,lastupdated=now()

    It works!

    I want to thank you so much for helping me with this.


    Ron

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Tuesday, May 14, 2019 9:35 AM
  • User-1211567575 posted

    Jalpal:

    I have another related issue to this one. I am including the information, but please let me know if I should start a new ticket for this issue?

    One-Drive Link:

    https://1drv.ms/f/s!AkVNIj06hxI7gQ3vBCZS0jntHQzl

    On the page, Testdatabase.asp, I have added a link to the page Profile.asp see screenshot: Testdatabase with link to Profile

    Profile.asp draws from the query Tradequery, which includes the fields from table1:
    TradeCode
    TradeCode2

    In the page Profile.asp, the criteria are shown in the screenshot: Profile Criteria
    TradeCode=T1
    or
    TradeCode2=T1

    In the link to Profile.asp on page Testdatabase.asp, the hyperlink and parameters show as T1=tradecode2


    See Hyperlink Properties

    If the file is open in TestDatabase.asp, and I click on the link (View Profile)to Profile.asp, the page (Profile.asp) opens to that same client's file.

    Issue:

    Anytime I update such a record, after submission the code needs to be re-entered when I send the update page (see screenshot profileup.asp.)

    After submitting (updating) on Profile.asp, I get returned to Profile.asp, but not with the same client's file open. I have to re-enter the code TradeCode or TradeCode2 .

    Is there a way (after an update) to wind up on the page with the client's file open? (as with the link on testdatabase.asp)?

    Thanks,

    Ron

    Sunday, May 19, 2019 1:03 PM
  • User-1211567575 posted

    Sorry...I didn't complete the sentence regarding the profile update page (profileup.asp)

    Issue:

    Anytime I update such a record, after submission the code needs to be re-entered when I send the update page (see 4th line down from top in screenshot profileup.asp.) :

    <meta http-equiv="refresh" content="1; url=profile.asp">

     

    Sunday, May 19, 2019 1:24 PM
  • User-848649084 posted

    Hi,

    I suggest you create a new post for the new issue.

    Monday, May 20, 2019 7:58 AM