I am a big fan a writing utilities that make everyone’s (especially mine) life a little easier. On of the small tasks that are performed on a regular basis where I work is transaction lookups. Not everyone likes writing SQL and can remember the syntax of a query. An HTA is an easy way to put together a little app to allow a user to execute a query against a database or allow you to graphically manage your administrative scripts. An HTA is unfortunately inherently insecure but as long as you use current security contexts or, in the case of data access, ODBC connections. Below is a little sample of how you can combine HTML and VBScript to create a flexible and useful tool for everyday tasks.
An HTA starts like a basic HTML page, but in the head you define an HTA section with important information like application name, window state, border type, etc.
After the HTA section you can define your styles and then you write the juicy part of the HTA, the scripts! Outside of writing back to the HTA window, your VBScripts work in almost the same exact way. You define subroutines and functions and call them as you normally would. Using the HTA allows you to add dropdown lists or radio buttons to define option pieces of your scripts. In this example we allow for access to multiple databases and different search types.
In the body of our HTML we define the graphical side of our HTA. We use a dropdown list to select our database (could easily be available Active Directory domains) and radio buttons to choose our search type, Business Date or Transaction Number (maybe we would make this a search based on object type, to stay in the same theme).
I hope to post another example of how you can use an HTA in more of an administrative capacity.
<html>
<head>
<title>Transaction Lookup</title>
<HTA:APPLICATION
ID = "TLApp"
APPLICATIONNAME = "Transaction Lookup"
BORDER = "thick"
SHOWINTASKBAR = "yes"
RESIZABLE = "no"
SINGLEINSTANCE = "yes"
SYSMENU = "yes"
WINDOWSTATE = "Maximize"
SCROLL = "yes"
SCROLLFLAT = "yes"
VERSION = "1.0"
INNERBORDER = "no"
SELECTION = "no"
MAXIMIZEBUTTON = "no"
MINIMIZEBUTTON = "no"
NAVIGABLE = "yes"
CONTEXTMENU = "yes"
BORDERSTYLE = "normal">
</head>
<style>
BODY {
background-color: buttonface;
font-family: arial, helvetica;
}
</style>
<script language="VBScript">
Sub Window_Onload
‘ execute_button.Disabled = True
‘ save_button.Disabled = True
code.InnerHTML = ""
GetData.FreeFrm.Disabled = True
GetData.QFreeFrm.Disabled = True
End Sub
Function TransLookUp
‘START DATA VALIDATION
If GetData.dbselect.value = "NONE" Then
Msgbox "Please select a database",16,"Database Select Error"
Exit Function
End If
If getdata.qbusdate.checked = true then
If isdate(GetData.busdate.value) Then
busdate2 = cdate(GetData.busdate.value)
Else
Msgbox "Please enter a valid date" & vbcrlf & vbcrlf & " MM/DD/YYYY",16,"Date Format Error"
GetData.BusDate.Value=""
Exit Function
End If
End If
‘END DATA VALIDATION
‘SET QUERY
If getdata.qbusdate.checked = true then
SQLString = "select * from trn_trans with(nolock) where rtl_loc_id = ‘" & getdata.storenum.value & "’ and business_date = ‘" & busdate2 & "’"
ElseIf getdata.qtransno.checked = true then
SQLString = "select * from trn_trans with(nolock) where rtl_loc_id = ‘" & getdata.storenum.value & "’ and trans_seq = ‘" & getdata.transno.value & "’"
End If
‘CREATE MYDB OBJECT
set MyDB = CreateObject("ADODB.Connection")
‘SET CONNECTION STRING
Select Case GetData.dbselect.value
Case "DATABASEP"
sqlsrvr = "SERVERP"
sqldb = "DATABASEP"
Case "DATABASEQ"
sqlsrvr = "SERVERQ"
sqldb = "DATABASEQ"
Case "DATABASED"
sqlsrvr = "SERVERD"
sqldb = "DATABASED"
End Select
MyDB.ConnectionString = "driver={SQL Server};server=" & sqlsrvr & ";uid=" & UnObs("7C68616A6F6E6C79") & ";pwd=" & UnObs("6D6F6594627A6173e3") & ";database=" & sqldb
‘OPEN DB AND PERFORM QUERY
MyDB.Open
set qresults = MyDB.Execute(SQLString)
‘FORMAT DATA AS HTML TABLE
recno = 1
strhtml = strhtml & "<h2>Results:</h2>"
strhtml = strhtml & "<table rules=all><tr><td>Record #</td>"
For Each c in qresults.fields
strhtml = strhtml & "<td>" & c.name & "</td>"
Next
strhtml = strhtml & "</tr>"
Do Until qresults.EOF
strhtml = strhtml & "<tr>"
strhtml = strhtml & "<td>" & RecNo & "</td>"
For Each r in qresults.fields
If r.value <> "" Then
val = r.value
Else
val = " "
End If
strhtml = strhtml & "<td>" & val & "</td>"
Next
strhtml = strhtml & "</tr>"
RecNo=RecNo + 1
qresults.MoveNext
Loop
strhtml = strhtml & "</table>"
‘CLOSE MYDB OBJECT
MyDB.Close
‘ASSIGN DATA TO C
code.innerHTML = strhtml
end Function
Sub ClearFields
getdata.reset()
code.innerHTML=""
End Sub
‘ Decode PW’s for connection string – NOT SECURE, just makes it more difficult to read. : )
Function UnObs(obs)
For i = 1 to Len(obs) Step 2
UnObs = UnObs & CStr(ChrW(CLng("&H" & (mid(obs,i, 2)))))
Next
End Function
</script>
<body>
<h2>Transaction Lookup</h2>
<form name="getdata">
<table>
<tr><td width=450px valign=top>
<table>
<tr><td colspan=2>
<fieldset><legend>Database Selection</legend>
<select id="dbselect" style="width: 350px">
<option value="NONE">Please select a database…</option>
<option value="DATABASEP">Production Database</option>
<option value="DATABASEQ">QA Database</option>
<option value="DATABASED">DEV Database</option>
</select>
</fieldset>
</td></tr>
<tr><td colspan=2>
<fieldset><legend>Store Number</legend>
<input type="text" name="storenum"></input></td></tr>
</fieldset>
</td></tr>
<tr><td>
<fieldset><legend>Query Selection</legend>
<table>
<tr><td><input name="qtype" id="qbusdate" type="radio">Business Date:</input></td><td><input type="text" name="busdate"></input></td></tr>
<tr><td><input name="qtype" id="qtransno" type="radio">Transaction #:</input></td><td><input type="text" name="transno"></input></td></tr>
<tr><td><input name="qtype" id="qfreefrm" type="radio">Free Form:</input></td><td><textarea name="freefrm" rows=10 cols=30></textarea></td></tr>
</table>
</fieldset>
</tr>
</table>
</td>
<td>
<div id="code" name="code" style="width: 650px;height: 600px; overflow:scroll; background-color:White;"></div>
</td>
</tr>
</table>
</form>
<button onclick="TransLookUp"
accesskey="P" style="background-color: #b0b0f0">
<b><u>P</u>rocess</b></button>
<button onclick="ClearFields"
accesskey="C" style="background-color: #b0b0f0"><b><u>C</u>lear</b></button>
</body>
</html>