DSO Tabular Paging Demo

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

OLE COM ADO CDO ADSI LDAP

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

DSO Tabular Paging Demo

The script is a fairly generic way to stream a Recordset into a Table for browsing. It is also very simple and may be useful to newcomers to WB, OLE and XML. Please read the comments in the script and adjust the ini settings of the datasource to reflect the path\subdir where you will test from.

This example is based of an Access database (.MDB) with a table called 'Zips'. It contains 4 columns: Zip, City, County, and State.

You will need the following inorder to run:
.MDB zip code database (Access 2000)
.WBT script
.INI file
.TEM template file

Stan Littlefield

DSO_IE.WBT

;//////////////////////////////////////////////////////////////////
;// Winbatch - Streaming Recordset To Browser
;//
;// Requires: IE5 or later as it uses the (D)ata (S)ource
;//                                           (O)bject
;//
;// Winbatch Release 2004F:
;//    functions FileGet(), FilePut() will need to be
;//    re-written to FileRead(),FileWrite() for earlier
;//    Winbatch releases ( prior to 2003C )
;//
;// Uses standard ADO to Obtain and persist Recordset
;//
;// Demo MDB file with U.S. zipcodes included
;//   data source and connection string are 'hard-coded' in
;//   the dso.ini file. Also, the SQL query is coded as SQL=
;//   in the ini (3 sample queries are included). Try all 3.
;//
;// Browser template dso.tem is NOT set up for fixed width which
;// permits faster loading and display of data. Template
;// is very basic, but can easily have style and color added.
;//
;// Practical Value? - situations where users need a simple
;//                    lookup to refer to, and you don't want
;//                    them staying attached to a Database
;//
;//
;// Stan Littlefield August 15, 2003
;//////////////////////////////////////////////////////////////////

;// standard browser display subroutine
#definesubroutine startMSIE(url)
   Browser = objectopen("InternetExplorer.Application")
   Browser.addressbar = @false
   Browser.statusbar = @false
   Browser.menubar = @false
   Browser.toolbar = @false
   browser.visible = @false
   browser.navigate(url)
   WaitForPageLoad()
   browser.visible = @true
   browserDoc = Browser.Document
   all = browserdoc.all
   return(browser)
#endsubroutine

#DefineSubroutine WaitForPageLoad()  ; assume Browser
   While browser.busy || browser.readystate == 1
      TimeDelay(0.5)
   EndWhile
   While browser.Document.ReadyState != "complete"
      TimeDelay(0.5)
   EndWhile
   return
#EndSubroutine


:init
intcontrol(73, 1, 0, 0, 0)

;// check for existence of template and ini files
cTEM = StrCat( dirget(),"dso.tem")
If ! FileExist( cTEM ) Then Exit
cINI = StrCat( dirget(),"dso.ini")
If ! FileExist( cINI ) Then Exit
cXML = StrCat( dirget(),"dso.xml")
If FileExist( cXML ) Then FileDelete( cXML )
cHTM = StrCat( dirget(),"dso.htm")
If FileExist( cHTM ) Then FileDelete( cHTM )

;// obtain connection information from ini file
cConn = IniReadPvt("Main","Conn","N/A",cINI)
n     = IniReadPvt("Main","SQL","0",cINI)
cSQL  = IniReadPvt("Main",StrCat("s",n),"N/A",cINI)
t     = IniReadPvt("Main",StrCat("t",n),"Data Display",cINI)

:collect
BoxOpen("Browser View","Opening Data Source")
DB  = ObjectOpen("ADODB.Connection")
;// NOTE: I am using a Recordset Object rather than
;// DB.Execute() to persist updateable features of
;// the data in anticipation of future editing
RS  = ObjectOpen("ADODB.Recordset")
DB.Open(cConn)
RS.Open(cSQL,cConn,1,4,1)

BoxText("Issuing SQL To Create Recordset")
;// the Save() function will persist the Recrodset
;// to an XML file
RS.Save(cXML,1)
ObjectClose(RS)
DB.close()
ObjectClose(DB)

:config
BoxText("Configuring Browser Table")
;// set the template up to load the file
;// and display the Table Title
file = FileGet(cTEM)
file = StrReplace(file,"/file/",cXML)
file = StrReplace(file,"/T/",t)

;// now traverse the XML file to obtain field
;// information for the Table
RS        = ObjectOpen("ADODB.RecordSet")
RS.Open(cXML,"Provider=MSPersist;",,,256)
file = StrReplace(file,"/R/",RS.RecordCount) ; nice feature
flds = RS.Fields
n = flds.Count
cRow = ""
cData = ""
For i = 0 to (n-1)
   fld = RS.Fields(i)
   cRow = StrCat(cRow,"",fld.Name,"",@CRLF)
   cData = StrCat(cData,'',@CRLF)
Next
file = StrReplace(file,"/head/",cRow)
file = StrReplace(file,"/fields/",cData)
RS.Close()
ObjectClose(RS)

:display
;// re-vamped template is saved as dso.htm
;// and passed to browser as URL
FilePut(cHTM,file)
Drop(file,cRow,cData)
br = startMSIE(cHTM)
BoxShut()
ObjectClose(br)
Exit

DSO.INI

[Main]
Conn=Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=C:\TEMP\ZIPS.MDB;
SQL=1
s1=SELECT * FROM zip WHERE State='NC';
t1=North Carolina Zip Codes
s2=SELECT * FROM zip WHERE ( State='NC' AND County='Wake');
t2=Wake County, North Carolina
s3=SELECT * FROM zip WHERE ( State='CA' AND MID(city,1,1)='A');
t3=California Cities Beginning with A

DSO.TEM

<HTML>
<XML ID="chgs" SRC="/file/"></XML>
<B>/T/</B><P>
Rows Found [/R/]
<TABLE DATASRC="#chgs">
   <TR><TD>
      <TABLE DATASRC="#chgs" DATAFLD="rs:data">
         <TR><TD>
            <TABLE ID="tbl" DATASRC="#chgs" DATAFLD="z:row" BORDER="1" DATAPAGESIZE=6 >
               <THEAD>
                  <TR>
                     /head/
                  </TR>
               </THEAD>
               <TBODY>
                  <TR>
                     /fields/
                  </TR>
               </TBODY>
            </TABLE>
         </TD></TR>
      </TABLE>
   </TD></TR>
</TABLE>
<P>
<BUTTON ONCLICK="tbl.previousPage()">PreviousPage</BUTTON>
<BUTTON ONCLICK="tbl.nextPage()">NextPage</BUTTON>
<BUTTON ONCLICK="tbl.firstPage()">FirstPage</BUTTON>
<BUTTON ONCLICK="tbl.lastPage()">LastPage</BUTTON>


</HTML>

Article ID:   W16066

File Created: 2004:08:02:11:17:42

Last Updated: 2004:08:02:11:17:42