Hmmm… HTA…

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&nbsp;#</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 = "&nbsp;"
  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>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s