Don,
Here's how it works.
We need two tables. One to record the DateTime that a request began. And
another table which records when requests complete.
For each record in tRequestStart, there may or may not be a record in
tRequestComplete depnding on whether the whole page was received at the
Client.
I'm using MsSQL7, stored procedures and ISS. You may need to use SQL
statements in your code if using Access.
You'll need MDAC installed to use ADO.
Table structure (tRequestStart)
iUniqueID int (AutoIncrement) PK
iCrewID int
DTRequestStart DateTime
Table structure (tRequestComplete)
iRequestID int PK (NOTE: Not AutoIncrement)
iCrewID int
DTRequestEnd DateTime
I my case, I pick users for a CFTree, so the CFTREEITEMKEY contains the
UserID.
At the start of the page the user has requested, use the following CF code
to call a stored procedure.
Change the Datasource name to match yours. You may need to use a CFQUERY
depending on DB
<CFSTOREDPROC PROCEDURE="sp_InsertReqStartRecord"
DATASOURCE="DSN"
RETURNCODE="No">
<!--- CFPROCPARAM tags --->
<CFPROCPARAM TYPE="OUT" CFSQLTYPE=CF_SQL_INTEGER
VARIABLE=Result DBVARNAME=@SUCCESS NULL="Yes">
<CFPROCPARAM TYPE="IN"
CFSQLTYPE=CF_SQL_INTEGER
VALUE="#CFTREEITEMKEY#" DBVARNAME=@iCrewID>
<!--- CFPROCRESULT tags --->
<CFPROCRESULT NAME = GetUsersbyLetter>
<!--- Close the CFSTOREDPROC tag --->
</CFSTOREDPROC>
The SQL for the SP is simple
CREATE PROCEDURE sp_InsertReqStartRecord
@SUCCESS int OUTPUT,
@iCrewID int
AS
INSERT INTO tRequestStart
(
[iCrewID],
[DTRequestStart]
)
VALUES
(
@iCrewID,
GETDATE()
)
SELECT @SUCCESS = @@IDENTITY
This creates the time-stamped record and returns the RecordID, which we use
at the end of the page.
If you don't have Remote Scripting,it's a small download from Microsoft's
Web site from
http://www.msdn.microsoft.com/scripting/remotescripting/rsdown.htm
You may also need the latest Windows Scripting engine which you can install
from any IE5 installation.
Now the fairly complex bit. Remote scripting lets you call a function on the
server from the client.
You create a Active Server Page with the function you will to call from the
Client and declare them in a special way.
Getting the Server side working was the hardest part for me.
Here's the final piece of code that runs on the Client. There a two pieces
of JavaScript required. One to enable Remote Scripting and the other to call
the Server Side function.
1) Enable Remote Scripting. This should be place immediately after the
<body> tag. Note: NOT in the <HEADER>
<BODY>
<SCRIPT LANGUAGE="JavaScript" src="../_ScriptLibrary/RS.HTM"></script>
<SCRIPT LANGUAGE="JavaScript">
RSEnableRemoteScripting("../_ScriptLibrary/RScripting")
</SCRIPT>
I had a lot of problems with paths. The installation of Remote Scripting put
stuff into C:\Inetpub\WWWRoot\_ScriptLibrary which is normally in the root
level of your Web Server. Try to get samples working with this path before
you go copying stuff to other directories, otherwise you'll never figure
what's causing errors (coding or path errors).
2) Call the Exported Server Function. This should be the last line of code
in your page. You know the user recieved th rest of the page if this script
runs.
<cfoutput>
<SCRIPT LANGUAGE="JavaScript">
co =
RSExecute("LogRosterRequest.asp","logreq",#CFTREEITEMKEY#,#Result#);
if (co.return_value ==1) {
window.alert("Your Roster Request has been logged.");
}
</SCRIPT>
</cfoutput>
</BODY>
Here I'm calling an Exported function called logreq (Javascript is case
sensitive) from an Active Server Page called LogRosterRequest.asp. Again be
careful with paths. I'm passing in The UserID (#CFTREEITEMKEY#) and the
RecordID (#Result#) we got when CF called the stored procedure and created a
record in tRequestStart.
On the server.
Make sure RS.htm, RS.Asp and RSProxy.class exist in
c:\inetpub\wwwroot\_scriptlibrary.
Finally here is the code for LogRosterRequest.asp which exist in the same
directory and the CFM page that get's sent to the Client.
<%@ LANGUAGE=VBSCRIPT %>
<% Option Explicit %>
<% RSDispatch %>
<!--#INCLUDE FILE="../Classes/RScripting/RS.ASP"-->
<!--#INCLUDE FILE="../Classes/RScripting/ADOVBS.INC"-->
<SCRIPT RUNAT=SERVER LANGUAGE="JavaScript">
var public_description = new MyServerMethods();
function MyServerMethods()
{
this.logreq = Function( 'n1','n2','return addRecord(n1,n2)' );
}
</SCRIPT>
<SCRIPT RUNAT=SERVER LANGUAGE="VBScript">
Function addRecord(crewstr,recstr)
Dim objCmd
Dim objConn
' Data types are converted because they are passed as strings
addRosterRecord = 0
Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command")
objConn.ConnectionString = "PROVIDER=SQLOLEDB" & _
";SERVER=ServerName;UID=UserName;PWD=Password;DATABASE=DatabaseName"
objConn.Open
If (objConn.State = adStateOpen) Then
Set objCmd.activeconnection=objconn
objCmd.CommandText = "sp_InsertReqRecEnd"
objCmd.Parameters.Append objCmd.CreateParameter("@SUCCESS", adInteger,
adParamOutput, 0, 0)
objCmd.Parameters.Append objCmd.CreateParameter("@iCrewID", adInteger,
adParamInput, , CInt(crewstr))
objCmd.Parameters.Append objCmd.CreateParameter("@iRecID", adInteger,
adParamInput, , CInt(recstr))
objCmd.CommandType = adCmdStoredProc
objCmd.Execute
addRosterRecord = ObjCmd("@SUCCESS")
End if
Set ObjCmd.ActiveConnection = Nothing
Set ObjCmd = Nothing
Set objConn=Nothing
End Function
</SCRIPT>
You'll to change the ADO connection string to use your Server, Database and
log in with a valid Username and Password.
This code calls a store procedure which inserts the matching time-stamped
record. Here's the Stored procedure.
CREATE PROCEDURE sp_InsertReqRecEnd
@SUCCESS int OUTPUT,
@iCrewID int,
@iRecID int
AS
INSERT INTO tRequestComplete
(
[iRequestID],
[iCrewID],
[DTRequestEnd]
)
VALUES
(
@iRecID,
@iCrewID,
GETDATE()
)
SELECT @SUCCESS = @@ROWCOUNT
Make sure the user you use in the connection string has permissions to run
the stored procedure.
All the above code assumes you know the users in your system and have a
userid for them. If the system is a public system you could log their IP
address.
You can check whether a page request completed by querying the tables as
follows
SELECT iCrewID,DTRequestStart,DTRequestEnd
FROM tRequestStart,tRequestComplete
WHERE (tRequestStart.iUniqueID = tRequestComplete.iRequestID) AND (iCrewID=)
Hope this isn't too verbose.
Mail me if your unclear on anything.
Regards
Michael O'Reilly
TransAer
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
