January 17, 2010
While it is helpful that SQL statements may be submitted directly from VBS scripts, in most cases the username and password for the database user will be exposed in the VBS script. So, we need another way. How about having the VBS script pass a value of interest to an ASP web page (note that this is not ASP.Net, this is old style programming without a Net). First, we need to create the ASP web page (after, of course, enabling ASP in Microsoft’s IIS web server configuration):
<html>
<head>
<title>I am a Hidden Web Page - You will Not See Me</title>
</head>
<body>
<%
Dim adVarChar
Dim adParamInput
Dim adCmdText
Dim strSQL
Dim snpData
Dim comData
Dim strPartID
Dim dbDatabase
adVarChar = 200
adParamInput = 1
adCmdText = 1
Set dbDatabase = Server.CreateObject("ADODB.Connection")
Set comData = Server.CreateObject("ADODB.Command")
Set snpData = Server.CreateObject("ADODB.Recordset")
On Error Resume Next
strPartID = cStr(Request("strPartID"))
dbDatabase.Open "Provider=MSDAORA.1;User ID=MyUser;Password=MyPassword;Data Source=MyDB;Persist Security Info=True"
With ComData
strSQL = "SELECT /*+ LEADING(IT) INDEX(IT X_INV_TRANS_1) */" & vbCrLf
strSQL = strSQL & " IT.PART_ID," & vbCrLf
strSQL = strSQL & " TRUNC(SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'O',IT.QTY,0),0))-SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'I',IT.QTY,0),0))+.9999) AS NEW_ANNUAL_USAGE" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " INVENTORY_TRANS IT," & vbCrLf
strSQL = strSQL & " PART P" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " IT.TRANSACTION_DATE>TRUNC(SYSDATE-365)" & vbCrLf
strSQL = strSQL & " AND P.ID=IT.PART_ID" & vbCrLf
strSQL = strSQL & " AND P.ID= ?" & vbCrLf
strSQL = strSQL & "GROUP BY" & vbCrLf
strSQL = strSQL & " IT.PART_ID" & vbCrLf
'
.Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 30, strPartID)
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
End With
Set snpData = ComData.Execute
Response.Write "<input type=""text"" name=""txtPartID"" size=30 value=""" & strPartID & """ disabled=true>"
If Not (snpData Is Nothing) Then
If Not(snpData.EOF) Then
Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""" & cstr(snpData("new_annual_usage")) & """ disabled=true>"
Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""RETRIEVED"" disabled=true>"
Else
Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""NO TRANSACTIONS"" disabled=true>"
End If
Else
Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""ERROR"" disabled=true>"
End If
snpData.Close
dbDatabase.Close
Set snpData = Nothing
Set comData = Nothing
Set dbDatabase = Nothing
%>
</body>
</html>
OK, reading the ASP web page code, we create an ADO database connection object, an ADO recordset object, and an ADO command object. Next, we set the strPartID variable to the value of the passed in strPartID variable from the web session, build a SQL statement with a bind variable set to the value of the strPartID variable, and then execute the SQL statement. If the SQL statement successfully executed, we build two HTML text box, the first with the value of NEW_ANNUAL_USAGE, and the second with a status of either RETRIEVED or NO TRANSACTIONS. If the SQL statement failed to execute, the two HTML text boxes will contain 0 and ERROR.
Now for the VBS script that will call the ASP web page:
Dim intResult
Dim objIE
Dim strHTML
Dim strID
Dim ANNUAL_USAGE_QTY
On Error Resume Next
Set objIE = CreateObject("InternetExplorer.Application")
strID = "ABCDEF123456"
ANNUAL_USAGE_QTY = 100
objIE.Navigate "http://localhost/Update_Annual_Usage_Qty.asp?strPartID=" & strID
objIE.Width=100
objIE.Height=100
objIE.Statusbar=False
objIE.Menubar=False
objIE.Toolbar=False
objIE.Visible = False
Do While objIE.Busy <> False
WScript.Sleep 200
Loop
'loop until the button is clicked
Do While intFlag = 0
If Err <> 0 Then
IntFlag = -1
End If
If objIE is Nothing Then
'User closed ID
intFlag = -1
Else
If objIE.Document.All.txtOK.Value <> " " Then
intFlag = 1
End If
End If
WScript.Sleep 200
Loop
If intFlag = 1 Then
If objIE.Document.Body.All.txtOK.Value = "ERROR" Then
MsgBox "Error sending the query to the database"
Else
If objIE.Document.Body.All.txtOK.Value = "NO TRANSACTIONS" Then
intResult = MsgBox ("No transactions for this part in the last year, OK to set the annual usage qty to 0? The old value is " & cStr(ANNUAL_USAGE_QTY), vbQuestion + vbYesNo, "Annual Usage")
If intResult = vbYes Then
ANNUAL_USAGE_QTY = 0
End If
Else
'Copy in the values from the web page
intResult = MsgBox ("The old annual usage quantity value is " & cStr(ANNUAL_USAGE_QTY) & " - the database indicates that the updated quantity should be " & cstr(objIE.Document.Body.All.txtAnnualUsage.Value) & ". Would you like to update the annual usage quantity?", vbQuestion + vbYesNo,"Annual Usage")
If intResult = vbYes Then
ANNUAL_USAGE_QTY = objIE.Document.Body.All.txtAnnualUsage.Value
End If
End If
End If
objIE.Quit
End If
Set objIE = Nothing
Set objShell = Nothing
The VBS script launches the ASP page in a hidden Internet Explorer window, passing in the value of strID on the address line (this is picked up in the ASP script as the strPartID session variable). The VBS script then waits until the ASP page finishes loading. Once the ASP page finishes, the VBS script reads the values of the two HTML text boxes and acts appropriately based on the values of those text boxes.
The neat thing about straight ASP programming code is that it looks a lot like the VBS programming code, and that looks a lot like the Excel macro programming code, and that looks a lot like the classic Visual Basic programming code, and that kind of looks like the classic BASIC programming code that I started working with in 1981/1982. I have been sitting in on the technology training advisory committee for one of the local colleges. The committee helps determine what computer classes will be taught to earn a degree at the college. The question was asked what languages to teach – I heard C++ and Java being suggested… I wonder if I should have suggested Visual Basic? VBS like languages are also used as macro languages in some ERP products and other packages (I believe that AutoCAD uses a similar macro syntax, as does PC-DMIS).
Charles Hooper's Oracle Notes 
Leave a comment