Time Stamp 1

[marvelisticme]

I have a some web pages (forms) someone else created, that feed a foxpro database. I need a time stamp in each table that is being fed, but am not sure what is the best way of going about this. It seem like it would be easier for the database to create a time stamp for each record added, but I don't know how to do this in foxpro. I sure hope someone can help! Thanks in advance!

 

[wgcs]

In your code, you can simply store DATETIME() in a DateTime-type field (or ttoc(DATETIME(),1) into a Character field) as your program creates or changes the record.

If you have many places touching the table, you could use an Insert or Update trigger on that table in the database to REPLACE timestamp WITH DATETIME()

Look up Triggers in the VFP help.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[marvelisticme]

I didn't create the program, and don't know VFP, so would you please show me how it might look in the below code, assuming I have added a field in the table called time_stamp? Thank you!


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/GIS_Payroll_Connect.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

MM_editConnection = MM_GIS_Payroll_Connect_STRING
MM_editTable = "travelpay"
MM_editRedirectUrl = "done.htm"
MM_fieldsStr = "username|value|employee|value|ssn|value|company|value|comments|value|
tv_startdt|value|tv_client|value|tv_flat|value|tv_payable|value|tv_origin|
value|tv_dest|value|tv_mileage|value|tv_permile|value|tv_amount|value|
tv_pay2|value"
MM_columnsStr = "username|',none,'|employee|',none,'|ssn|',none,'|company|',none,'|
comments|',none,'|tv_startdt|',none,'|tv_client|',none,'|tv_flat|
',none,'|tv_payable|',none,'|tv_origin|',none,'|tv_dest|',none,'|
tv_mileage|',none,'|tv_permile|',none,'|tv_amount|',none,'|tv_pay2|
',none,'"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","'") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

'<!--- Employee Information Block ----!>
strUsername = Request.Form("username")
strCompany = Request.Form("company")
strEmployee = Request.Form("employee")
strSSN = Request.Form("ssn")
strComments = Request.Form("comments")
strRequesttype = Request.Form("Requesttype")
'<!--- Travel Pay Request Block --->
strTv_startdt = Request.Form("tv_startdt")
strTv_client = Request.Form("tv_client")
strTv_flat = Request.Form("tv_flat")
strTv_payable = Request.Form("tv_payable")
strTv_origin = Request.Form("tv_origin")
strTv_dest = Request.Form("tv_dest")
strTv_mileage = Request.Form("tv_mileage")
strTv_permile = Request.Form("tv_permile")
strTv_amount = Request.Form("tv_amount")
strTv_pay2 = Request.Form("tv_pay2")
'<!--- Our mailserver --->
strHost = "10.1.1.10"
'<!---end of string defining fun! (aww, man!) --->

'<!--- Begin Mail Creation --->
Set Mail = Server.CreateObject("Persits.Mailsender")
Mail.Host = strHost
Mail.From = "Global User"
Mail.FromName = strUsername
Mail.AddAddress "dmcclung@teamglobal.com"
Mail.AddCC "skostele@teamglobal.com"
Mail.Subject = "Travel Pay Request recieved from " &strUsername

'<!--- Begin Ludicrously Huge Message Body --->
Mail.Body = "Travel Pay Request received from " &strUsername & vbCrLf & vbCrLf & "Employee Name: " &strEmployee & vbCrLf & vbCrLf & "SSN: " &strSSN & vbCrLf & vbCrLf & "Comments: " &strComments & vbCrLf & vbCrLf & "Company: " &strCompany & vbCrLf & vbCrLf & "----Travel Pay Request----" & vbCrLf & vbCrLf & "Start Date: " &strTv_startdt & vbCrLf & vbCrLf & "Client: " &strTv_client & VbCrLf & VbCrLf & "Flat Amount: " &strTv_flat & vbCrLf & vbCrLf & "Payable: " &strTv_payable & vbCrlf & VbCrLf & "--OR--" & vbCrlf & VbCrLf & "Origin: " &strTv_origin & vbCrLf & vbCrLf & "Destination: " &strTv_dest & vbCrLf & vbCrLf & "Mileage: " &strTv_mileage & vbCrLf & vbCrLf & "Cents Per Mile: " &strTv_permile & vbCrlf & VbCrLf & " Amount: " &strTv_amount & vbCrlf & VbCrLf & "Payable: " &strTv_pay2 & vbCrlf & VbCrLf
Mail.Send


If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim travel_rs
Dim travel_rs_numRows

Set travel_rs = Server.CreateObject("ADODB.Recordset")
travel_rs.ActiveConnection = MM_GIS_Payroll_Connect_STRING
travel_rs.Source = "SELECT * FROM travelpay"
travel_rs.CursorType = 0
travel_rs.CursorLocation = 2
travel_rs.LockType = 1
travel_rs.Open()

travel_rs_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "

http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Travel Pay Request Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../SSNCheck/Nstyles.css" type="text/css">
<style type="text/css">
<!--
.style36 {
font-family: Arial;
font-size: 18px;
font-weight: bold;
color: #336699;
}
.style38 {
font-family: Arial, Helvetica, sans-serif;
font-size: 14px;
font-weight: bold;
}
.style41 {
font-family: "Trebuchet MS";
color: #336699;
}
.style42 {font-size: 16px}
.style48 {font-family: Arial; font-size: 16px; font-weight: bold; color: #336699; }
.style43 {color: #FF0000}
.style47 {font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-weight: bold; color: #000000; }
.style49 {color: #000000}
-->
</style>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table width="1102" border="0" cellpadding="0" cellspacing="0" mm:layoutgroup="true"><!--DWLayoutTable-->
<tr>
<td colspan="2" rowspan="3" valign="top"><div align="left"><a href="

http://www.teamglobal.com"><img

src="/images/globaltag.jpg" width="206" height="103" name="GlobalLogo" border="0" align="bottom"></a></div></td>
<td width="118" height="67">&nbsp;</td>
<td width="472">&nbsp;</td>
<td width="305">&nbsp;</td>
</tr>
<tr>
<td height="26"></td>
<td valign="top"> <p align="center" class="Header style36"> Global Information System</p></td>
<td></td>
</tr>
<tr>
<td height="14"></td>
<td></td>
<td></td>
</tr>
<tr>
<td width="131" rowspan="2" valign="top" bgcolor="#336699">
<p>&nbsp;</p>
<p>&nbsp; </p>
<p>&nbsp; </p>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp;</p></td>
<td height="478" colspan="3" valign="top"><div align="center">
<form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">
<p>&nbsp;</p>
<p class="style36 style42">Travel Pay Request Form </p>
<p class="style36 style42"><span class="style48"><span class="style47">All fields marked with an asterisk (<span class="style43">*</span>) are REQUIRED</span></span></p>
<table width="552" border="0" align="left" cellpadding="3">
<tr>
<td class="style41"><div align="right">Requestor Name: </div></td>
<td><input name="username" type="text" id="username" size="30" maxlength="100">
<span class="style43">*</span></td>
</tr>
<tr>
<td class="style41"><div align="right">Employee Name:</div></td>
<td><input name="employee" type="text" id="employee" size="30" maxlength="100">
<span class="style43">*</span></td>
</tr>
<tr>
<td height="35" class="style41"><div align="right">SSN:</div></td>
<td><input name="ssn" type="text" id="ssn" size="20" maxlength="11">
<span class="style43">* <span class="style49">Use format 111-11-1111 </span></span></td>
</tr>
<tr>
<td class="style41"><p align="right">Company:</p></td>
<td><select name="company" id="company">
<option value="GES">Global Employment Solutions</option>
<option value="GTS">Global Technical Services</option>
<option value="GCP">Global Contract Professionals</option>
</select>
<span class="style43">*</span></td>
</tr>
<tr>
<td class="style41"><div align="right">Additional Comments:</div></td>
<td><input name="comments" type="text" id="comments" size="35" maxlength="250">
</td>
</tr>
<tr>
<td class="style41">&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td width="176" class="style41"><div align="right">Start Date:</div></td>
<td width="358"><input name="tv_startdt" type="text" id="tv_startdt" size="15" maxlength="8">
<span class="style43">* <span class="style49">Use format 01/01/05 </span></span></td>
</tr>
<tr>
<td class="style41"><div align="right">Client:</div></td>
<td><input name="tv_client" type="text" id="tv_client" size="30" maxlength="45">
<span class="style43">*</span></td>
</tr>
<tr>
<td class="style41"><div align="right">Flat Amount:</div></td>
<td><input name="tv_flat" type="text" id="tv_flat" size="30" maxlength="20"></td>
</tr>
<tr>
<td class="style41"><div align="right">Payable:</div></td>
<td><input name="tv_payable" type="text" id="tv_payable" size="30" maxlength="254"></td>
</tr>
<tr>
<td class="style41"><div align="left">or</div></td>
<td>&nbsp;</td>
</tr>
<tr>
<td class="style41"><p align="right">Point of Origin:</p>
</td>
<td><input name="tv_origin" type="text" id="tv_origin" size="30" maxlength="65"></td>
</tr>
<tr>
<td class="style41"><div align="right">Destination:</div></td>
<td><input name="tv_dest" type="text" id="tv_dest" size="30" maxlength="65"></td>
</tr>
<tr>
<td class="style41"><div align="right">Mileage:</div></td>
<td><input name="tv_mileage" type="text" id="tv_mileage" size="30" maxlength="20"></td>
</tr>
<tr>
<td class="style41"><div align="right">Cents Per Mile:</div></td>
<td><input name="tv_permile" type="text" id="tv_permile" size="30" maxlength="20"></td>
</tr>
<tr>
<td class="style41"><div align="right">Amount:</div></td>
<td><input name="tv_amount" type="text" id="tv_amount" size="30" maxlength="20"></td>
</tr>
<tr>
<td class="style41"><div align="right">Payable:</div></td>
<td><input name="tv_pay2" type="text" id="tv_pay2" size="30" maxlength="254"></td>
</tr>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p align="left">&nbsp; </p>



<input type="hidden" name="MM_insert" value="form1">
</form>
<p align="left" class="style38">&nbsp;</p>
</div></td>
<td>&nbsp;</td>
</tr>
<tr>
<td width="76" height="242">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
<hr>
</body>
</html>
<%
travel_rs.Close()
Set travel_rs = Nothing
%>

 

[Mike Lewis]

Bill,

If you have many places touching the table, you could use an Insert or Update trigger on that table in the database to REPLACE timestamp WITH DATETIME()

I wouldn't advise that. In fact, it won't work. You can't use a trigger to update the table that fired the trigger. If you could, the update would fire the trigger, which would fire the trigger ..... and so on.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[marvelisticme]

I only have a one form to one table relationship, so not many places touching. I just want there to be a time stamp for each entry into a table. I thought this might be down with an auto entry column for each row that stamps the time the row is added. Is this a possibility?

Thanks!

 

[wgcs]

Thanks, Mike: I've never worked with database triggers (our products use free tables), so I wasn't aware of the possible recursion.

I think what marvelisticme can do is:
1) add the "TimeStamp" field to the database from within VFP:
a) Open VFP
b) Open the database with the command: OPEN DATABASE [x:\path\database name.dbc]
c) Add the TimeStamp field with the command: ALTER TABLE travelpay ADD COLUMN TimeStamp T
d) leave VFP (to close anything opened exclusively)

2) change the insert query from this:

MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

to this:

MM_editQuery = "insert into " & MM_editTable & " ( TimeStamp, " & MM_tableValues & ") values ( DATETIME(), " & MM_dbValues & ")"

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[marvelisticme]

That worked a charm, Bill. Thanks!!

 

[wgcs]

I'm glad it worked. I hesitated to offer all the steps because I wasn't sure that the DATETIME() would work in the SQL, and I didn't know that I could describe the changes in VFP well enough that you could follow them. Thanks for the star!

- Bill

Get the best answers to your questions -- See FAQ481-4875.