IF IT LOOKS LIKE CLIENT SIDE AND ACTS LIKE CLIENT SIDE
MAYBE IT IS CLIENT SIDE (ALMOST)
OR
UPDATING WEB TABLES FROM VFP6
Phil Bartow
My interest in the article is to update a VFP table, located on a web site, from a VFP form on my desk top as if it were a table in a subdirectory of my desktop. Normally, I could access the web site and view it on my browser; a form appears on the browser (see Figure 2) which is generated from Default.Asp on the web. Changes can be made and when the submit button is pressed the table is updated through the Snd_Ordr.Asp file, in this case using ODBC to access and update the table. My preference would be to load the new values on the web directly from my VFP6 application
I do not have West Wind or AFP and my server is a shared server somewhere in the hinterland over which I have little control. The concept discussed in the article is not a substitute for running native VFP on the web but it does allow full use of VFP and the update of tables on the web.
OVERVIEW OF WEB INTERACTIONS
Figure 1
The ability to interact with the web table is faciltated by use an ActiveX control from /nSoftware, Inc. The control was designed for use with VB but seems to work fine with VFP. Like everything, it is obvious once you get it working!
Figure 3 shows a VFP form that has the same fields as the browser form as well as controls for navigating through a database on my desktop. In the development activity not all the fields are labeled.
The intent is for several people in different organizations and companies to update the web table. Customers can view the web table as a means of tracking their orders. An order is placed, the supplier is notified, The supplier ships goods to a warehouse and loading dock. The warehouse puts goods in containers and sends the containers to a shipper. The goods are loaded and shipped to, say Alaska
BROWSER VIEW OF UPDATE FORM
Figure 2
. With secured access, the web become a convenient data hub for tracking the status of an order. It is convenient if the VFP programs in along the purchasing chain can be used for updating the web as well as internal tracking and reporting.
VFP6 FORM
Figure 3
There are more data fields shown in Figure 3, the desktop, than in Figure 2. In figure 3, in addition to navigation and business logic fields, the fields that would be HIDDEN on the browser are displayed.
The content of the fields is not really important for this article. What is important is that the names of the fields in the VFP structure coincide with those used in the web form. Figure 4 shows the design view of the VFP form. The Form Init event defines variables. The WebUpload control (click event) is used to process the data to conform to the web requirements and initialize the ActiveX control. The WebUpload click event activates the ActiveX submit method which corresponds (conceptually) to clicking the Submit button on the Web form.
VFP FORM DESIGN
Figure 4
FORM INIT EVENT
** XXXXXXXXXXXXX is used to mask the specific URL used in the example **
IF USED('webtest')
SELECT webtest
ELSE
SELECT SELECT(0)
USE webtest AGAIN SHARE ALIAS webtest
ENDIF
************** THE NEXT 4 VARIABLE ARE FOR THE ACTIVEX CONTROL
PUBLIC FVCOUNT
FVCOUNT = 0
PUBLIC FVNAMES
FVNAMES = ""
PUBLIC FVVALUES
FVVALUES = ""
PUBLIC geturl
geturl = "http://XXXXXXXXXX/update/snd_ordr.asp"
THISFORM.txturl.VALUE = geturl
*************THE NEXT SECTION DEFINES VARIABLES TO BE POSTED****
PUBLIC GETPODATE
PUBLIC GETPO
PUBLIC getponumb
PUBLIC GETPOEXT
****************************************************************************
* NOT ALL OF THEM ARE LISTED HERE *
* THERE IS A ONE-TO-ONE RELATIONSHIP BETWEEN THESE VARIABLES *
* AND THOSE IN THE DEFAULT.ASP FILE ON THE WEB *
****************************************************************************
PUBLIC GETLINE
PUBLIC GETSALECODE
PUBLIC GETUPSTAT
PUBLIC GETVERI
*********************** BOTTOM OF FORM INIT*********************
CLICK EVENT ON WEB UPLOAD CONTROL
** PUT TABLE FIELD VALUES INTO VARIABLES
getsalecode = ALLTRIM(salescode)
getpodate = ALLTRIM(podate)
gettrack = ALLTRIM(trackline)
getpo = ALLTRIM(ponumb)
getpoext = ALLTRIM(poext)
GETUPSTAT = ALLTRIM(UPSTAT)
******************************************************************************.
* for brevity the bulk of the assignment of variable values has been deleted from this discussion *
*******************************************************************************
*************************************
* SET WEBFORM PROPERTIES *
*************************************
FVCOUNT = 27 && the number of variables to be posted (array size)
THISFORM.WEBFORM1.Encoding = 0
THISFORM.WEBFORM1.FormVarCount = FVCOUNT
****************************THE GIMMICK****************************
* The FormVarNames are the same as those in the Web form that will be posted. *
* (See Default.Asp and Snd_Otdr.Asp *
*********************************************************************
THISFORM.WEBFORM1.FormVarNames(1) = "getpodate"
THISFORM.WEBFORM1.FormVarValues(1) = getpodate
THISFORM.WEBFORM1.FormVarNames(2) = "getpo"
THISFORM.WEBFORM1.FormVarValues(2) = getpo
THISFORM.WEBFORM1.FormVarNames(3) = "getpoext"
THISFORM.WEBFORM1.FormVarValues(3) = getpoext
***********************************************
* removed middle section for discussion brevity *
* all 27 (fvcount pairs must be included *
***********************************************
THISFORM.WEBFORM1.FormVarNames(25) = "GETVERI"
THISFORM.WEBFORM1.FormVarValues(25) = GETVERI
THISFORM.WEBFORM1.FormVarNames(26) = "getsalecode"
THISFORM.WEBFORM1.FormVarValues(26) = getsalecode
THISFORM.WEBFORM1.FormVarNames(27) = "GETUPSTAT"
THISFORM.WEBFORM1.FormVarValues(27) = GETUPSTAT
******* THE ORDER OF WEB FORM PROPERTIES AND METHODS IS IMPORTANT***
THISFORM.WEBFORM1.URLl=THISFORM.TXTURL.VALUE
THISFORM.WEBFORM1.ACTION = 1
THISFORM.WEBFORM1.SUBMIT
THISFORM.REFRESH()
****************************END WEBFORM UPDATE*************************
SERVER SIDE
The use of the ActiveX control and interaction with the web requires some familiarity with Active Server Pages (ASP). What follows is my meandering and is not intended to be an ASP "how to". Default.Asp is the file that displays the form shown in Figure 2. The ASP code is in italics and comments are in regular type. Only enough code is provided to show the relationship between the code and Figure 2.
DEFAULT.ASP
<!--
//****************************************************************
// AUTHOR: P.E. BARTOW
// ADDRESS: PHIL@BARTOWASSOC.COM
// DATE CREATED: APRIL, 2001
// UPDATE: APRIL,2001
// FILE DEFAULT.ASP
//****************************************************************
// -->
<%
DIM CTODATE,CDATE1
CDATE1 = CSTR(DATE())
DIM nomore
nomore = 0
DIM gotcode
DIM vtrack
DIM VPODATE
DIM VSALECODE
.
20 variables deleted for brevity
DIM VVERI
DIM VEDELDATE
DIM VEXDELI
DIM VUPDATE
DIM VUPSTAT
%>
<%if Request.Form("action") = "" then %>
Set cupdate = Server.CreateObject("ADODB.Connection")
cupdate.open ("driver=Microsoft Visual FoxPro Driver;Exclusive=No;SourceType=DBF; SourceDB=xxxxxxxxxxxxxxxxxx;uid='';pwd=''" )
ISBLANK = " "
FOR TESTING PURPOSES ONLY LOOK FOR STATUS OF 3
LINE3 = "SELECT * FROM UPDATES WHERE UPSTAT = "
LINE3 = LINE3 +CHR(34)+"3"+CHR(34)
SET rsUPDATE = CUPDATE.Execute(LINE3)
if rsUPDATE.EOF then %>
<h2> No records to process </h2>
<% nomore = 1
else
end if %>
<% if nomore = 0 then
if rsUPDATE.bOF then %>
<h2> No records to process </h2>
<% nomore = 1
end if
end if %>
<% if nomore = 0 then
VPODATE = RSUPDATE("PODATE")
.
. (20 variable lines are deleted for brevity)
VEDELDATE = RSUPDATE("EDELDATE")
vstware = rsUPDATE("stware")
Vmandate = RSUPDATE("manDATE")
vstlman = rsUPDATE("stlman")
veqpnbr = rsUPDATE("eqpnbr")
vcarrier = rsUPDATE("carrier")
vvoyageno = rsUPDATE("voyageno")
vshipdate = rsUPDATE("shipdate")
VEXDELI = RSUPDATE("EXDELI")
vstdeli = RSUPDATE("stdeli")
VUPDATE = RSUPDATE("UPDATE")
vtrack = RSUPDATE("trackline")
VVERI = RSUPDATE("VERILINE")
VSALECODE = RSUPDATE("SALESCODE")
vUPSTAT = RSUPDATE("upstat")
%>
<% CUPDATE.Close %>
<% END IF 'no more %>
<HTML>
<title> Order/ Status Update Submittal</title>
<body bgcolor="#aaffffff">
<%
DIM rname,authcode,salecd,GOOTCODE
session("pebtest") = 1
rname = ""
authcode = ""
salecd = ""
GOTCODE = ""
%>
<%IF Request.FORM("ACTION") = "" THEN %>
THIS IS WHERE THE VFP FORM COMES IN . ALL OF THE INPUT
NAME FIELDS AND VALUES WILL HAVE A VFP COUNTERPART
<FORM ACTION = "SND_ORDR.ASP" METHOD="POST" id=form1 name=form1>
<table >
<td width= 500 align = "center"><font size=4> Place Order/Change Order</font></td>
<td><input type="submit" value="Submit" name="ACTION"></TD>
</tr>
</table>
The next few lines are what display the first three fields on the Browser form (Figure 3). The rest of the Browser form follow the same pattern. There would be similar code for the rest of the fields.
<table >
<tr>
<td align=right width = 150 > Sales ID:</td>
<td align=left >
<font size="2"><INPUT NAME ="GETSALECODE" TYPE="TEXT" SIZE = "10" value = "<%=VSALECODE%>" ></TD>
<td align=right width = 100 >Order Date:</td>
<td align=left ><INPUT NAME ="GETPODATE" TYPE="TEXT" SIZE = "11" value = "<%=VPODATE%>" ></TD> </TR>
<td align=right width = 150 >Tracking No:</td>
<td align=left ><INPUT NAME ="GETTRACK" TYPE="TEXT" SIZE = "15" value = "<%=VTRACK%>" ></TD></TR>
</table>
The next two line identify variables that are passed to SND_ORDR.ASP but are HIDDEN on the form.
<INPUT NAME ="GETveri" TYPE="HIDDEN" value = "<%=VVERI%>" ></TD></TR>
<INPUT NAME ="GETline" TYPE="HIDDEN" value = "<%=Vline%>" ></TD></TR>
</FORM>
<%end if%>
<% end if %>
<h6>Copyright © 2000-2001 Bartow Associates, Inc. All rights reserved.</h6>
</body>
</html>
*******************************************************************
***
SND_ORDR.ASP ***Clicking the Submit button on the Browser form (Figure 2) or clicking on the WebUpload control on the VFP6 form (Figure 3) sends (posts) data to SND_ORDR.ASP.
The variables, varname, identified in the REQUEST.FORM("varname") statements are the values posted from the VFP6 form. They correspond to the THISFORM.WEBFORM1.FormVarNames(n) and THISFORM.WEBFORM1.FormVarValues(n) in the Click method of the WebUpload control
<%@language=vbscript%>
<%option explicit%>
<HTML>
<%
DIM rname,authcode,salecd
DIM GOTCODE
dim AMODEREADWRITE
dim recordsaffected,adcmdtext
dim xxx,line3,rsupdate,nomore
dim VPODATE
dim VPOnumb
dim VpoEXT
dim sql
dim connup , sql0,sql1,sql2
dim sql3,sql4,sql5, sql6,sql7, sql8,sql9,sqla,sqlb, sqlc
dim sqld,sqle,sqlf, sqlg,sqlh, sqli,sqlj,sqlk,sqlle,sqlm,sqln,sqlo,sqlp
dim sqlq,sqlr,sql5a,sqlw,sqld1,sqle1
GOTCODE = LTRIM(Request.form("GETTRACK")) %>
<%
Set CONNUP = Server.CreateObject("ADODB.Connection")
CONNUP.Mode=AMODEREADWRITE
CONNUP.open ("driver=Microsoft Visual FoxPro Driver;Exclusive=No;SourceType=DBF;SourceDB=XXXXXXXXXXXXXXXXXXX;uid='';pwd=''" )
SQL0 = "UPDATE UPDATES set "
SQL1 = " PODATE ="& chr(34)& LTRIM(Request.form("GETPODATE")) & chr(34)
SQL2 = ", PONUMB ="& chr(34)& LTRIM(request.form("GETPO")) & chr(34)
SQL3 = ", POEXT ="& chr(34)& LTRIM(request.form("GETPOEXT")) & chr(34)
SQL4 = ", POCONT ="& chr(34)& LTRIM(request.form("GETPOCONT")) & chr(34)
SQL5 = ", VCODE ="& chr(34)& LTRIM(request.form("GETVCODE")) & chr(34)
SQL5a = ", Vendor ="& chr(34)& LTRIM(request.form("GETVCODE")) & chr(34)
SQL6 = ", VENDORNAME ="& chr(34)& LTRIM(request.form("GETVENDOR")) & chr(34)
SQL7 = ", LINENO ="& chr(34)& LTRIM(request.form("GETLINE")) & chr(34)
SQL8 = ", QTY ="& chr(34)& LTRIM(request.form("GETQTY")) & chr(34)
SQL9 = ", POQTY ="& chr(34)& LTRIM(request.form("GETUNITS")) & chr(34)
SQLA = ", ITEMNO ="& chr(34)& LTRIM(request.form("GETITEM")) & chr(34)
SQLB = ", P_DESCRIPT ="& chr(34)& LTRIM(request.form("GETDESCRIPT")) & chr(34)
SQLC = ", STVEND ="& chr(34)& LTRIM(Request.form("GETNOTIF")) & chr(34)
SQLD = ", vship ="& chr(34)& LTRIM(request.form("GETvship")) & chr(34)
SQLD1= ", EDELDATE ="& chr(34)& LTRIM(request.form("GETlonexp")) & chr(34)
SQLE = ", stware ="& chr(34)& LTRIM(request.form("GETLONDEL")) & chr(34)
SQLE1 = ", manDATE ="& chr(34)& LTRIM(request.form("GETmdate")) & chr(34)
SQLF = ", stlman ="& chr(34)& LTRIM(request.form("GETMAN")) & chr(34)
SQLG = ", eqpnbr ="& chr(34)& LTRIM(request.form("GETEQPNBR")) & chr(34)
SQLI = ", carrier ="& chr(34)& LTRIM(request.form("GETcarrier")) & chr(34)
SQLJ = ", voyageno ="& chr(34)& LTRIM(request.form("GETvoyage")) & chr(34)
SQLK = ", shipdate ="& chr(34)& LTRIM(request.form("GETSHIPD")) & chr(34)
SQLLE = ", EXDELI ="& chr(34)& LTRIM(request.form("GETPLANA")) & chr(34)
SQLM = ", stdeli ="& chr(34)& LTRIM(request.form("GETDELIV")) & chr(34)
SQLN = ", UPDATE ="& chr(34)& CSTR(DATE()) & chr(34)
SQLO = ", TRACKLINE ="& chr(34)& LTRIM(request.form("GETTRACK")) & chr(34)
SQLP = ", VERILINE ="& chr(34)& LTRIM(request.form("GETLINE")) & chr(34)
SQLQ = ", SALESCODE ="& chr(34)& LTRIM(request.form("GETSALECODE")) & chr(34)
SQLR = ", UPSTAT ="& chr(34)& LTRIM(Request.form("GETUPSTAT"))+"1" & chr(34)
SQLW= " WHERE alltrim(UPDATEs.trackline) ="& chr(34)& ltrim(GoTCode) &chr(34)
SQL = SQL0 & SQL1 & SQL2 & SQL3 & sql4 & sql5 & sql5a & sql6 & sql7 & SQL8 & SQL9 & sqlA
SQL = SQL & SQLB & SQLC & SQLD & SQLD1 &sqlE & SQLE1 &sqlF & sqlG & SQLI
SQL = SQL & SQLJ & sqlK & SQLLE & SQLM & SQLN & sqlO & sqlP & sqlQ & sqlR
SQL = SQL & SQLW
%>
<% CONNup.Execute(SQL),recordsaffected,adcmdtext %>
<title> Order/ Status Update Submittal</title>
<body bgcolor="#aaffffff">
order was sent
<%response.write "<br>"%>
<A HREF = "DEFAULT.asp" >Click here to return to the Posting screen</a>
</body>
</html>
***************************BOTTOM OF SND_ORDR.ASP************************
MAKING THE CONNECTION
A connection has to be made to the URL before the VFP program is activated. First, connect to the web, go to the URL address and then minimize the internet application Next open the VFP program. Whenever the WebUpload command is clicked the data for the VFP record is used to update the table on the web.
WEBFORM ACTIVEX CONTROL
The Webform Control is contained in the IP*Works! V5 Package from /n software (http://www.nsoftware.com).