Solved

Update Query from HTA

Posted on 2010-09-17
3
564 Views
Last Modified: 2012-05-10
I have an hta app that I am trying to use to update sql server but it doesn't update.
<html>
<head>
<title>Certificate of Conformance Information</title>

<HTA:APPLICATION
ID = "objHTA"
APPLICATIONNAME = "VISUAL C of C"
BORDER = "dialog"
CAPTION = "yes"
ICON = "visual.ico"
SHOWINTASKBAR = "yes"
SINGLEINSTANCE = "yes"
SYSMENU = "yes"
WINDOWSTATE = "normal"
SCROLL = "no"
SCROLLFLAT = "yes"
VERSION = "1.0"
INNERBORDER = "no"
SELECTION = "no"
MAXIMIZEBUTTON = "no"
MINIMIZEBUTTON = "yes"
NAVIGABLE = "yes"
CONTEXTMENU = "yes">

<OBJECT ID="WindowsShell"
    CLASSID="clsid:13709620-C279-11CE-A49E-444553540000">
</Object>

<script language=VbScript>
Option Explicit
On Error Resume Next

Dim objFSO, objShell, objWMI, objScreen, objItem, arrCommands, i
Dim stType, stBase, stLot, stSplit, stSub, stPart
Dim stDwgRevision, stQuantity, stConformQty, stNonconQty, stConSampQty, stUnworkQty
Dim strHTML, fOkClicked, appVisual, VisualDir
Dim intHoriz, intVert, intLeft, intTop

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
Set objWMI = GetObject("winmgmts:\\.\root\cimv2")
Set objScreen = objWMI.ExecQuery("Select * From Win32_DisplayConfiguration")

Sub Window_onLoad
    arrCommands = Split(objHTA.commandLine, chr(34))
    For i = 3 to (Ubound(arrCommands) - 1) Step 2
		stType = arrCommands(3)
		stBase = arrCommands(5)
		stLot = arrCommands(7)
		stSplit = arrCommands(9)
		stSub = arrCommands(11)
		stPart = arrCommands(13)
    Next
End Sub
	
For Each objItem in objScreen
	intHoriz = objItem.PelsWidth
	intVert = objItem.PelsHeight
Next

If intvert > 1024 Then 
	Window.ResizeTo 340, 285
	intLeft = (intHoriz - 340) / 2
	intTop = (intVert - 285) / 2
Else
	Window.ResizeTo 340, 285
	intLeft = (intHoriz - 340) / 2
	intTop = (intVert - 285) / 2
End If

Window.MoveTo intLeft, intTop


strHTML = strHTML & "<CENTER><b>" & stType & "&nbsp;&nbsp;&nbsp" & stBase & "/" & stLot & "/" & stSplit & "&nbsp;&nbsp;&nbsp" & stPart &"<b><br><br>"
strHTML = strHTML & "<LEFT><b>Drawing Rev</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input id='stDwgRev' type='text' size='12' value='" & stDwgRevision & "'><br>"
strHTML = strHTML & "<b>Quantity</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input id='stQty' type='text' size='12' value='" & stQuantity & "'><br>"
strHTML = strHTML & "<b>Conforming Qty</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input id='stConform' type='text' size='12' value='" & stConformQty & "'><br>"
strHTML = strHTML & "<b>Non-conforming Qty </b>&nbsp;&nbsp;&nbsp;&nbsp;<input id='stNoncon' type='text' size='12' value='" & stNonconQty & "'><br>"
strHTML = strHTML & "<b>Conforming Samples</b>&nbsp;&nbsp;&nbsp;&nbsp;<input id='stConSamp' type='text' size='12' value='" & stConSampQty & "'><br>"
strHTML = strHTML & "<b>Unworked Parts</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input id='stUnwork' type='text' size='12' value ='" & stUnWorkQty & "' onKeyPress='checkEnter'><br>"


'- - - - - - - - - - - - - - - - - - - - S U B R O U T I N E S - - - - - - - - - - - - - - - - - - - - - - - - 
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub HTA_Load()
	document.getElementById("Span1").innerHTML = strHTML
'	If stDwgRevision = "" Then 
		document.GetElementById("stDwgRev").focus
'	Else
'		document.GetElementById("stDwgRev").focus
'	End If
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

'looks for Enter key press from logon screen
'-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-
Sub CheckEnter
	If window.event.keyCode = 13 Then chkSubmit
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


'Alternate method to sleep in HTA
'-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-
Sub HTASleep(seconds)
Dim cmd
	cmd = "%COMSPEC% /c ping -n " & seconds & " 127.0.0.1>nul"
	objShell.Run cmd,0,1
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

'-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-
Function chkSubmit

On error resume next
	Dim objShell, strCnn
Set objShell = CreateObject("WScript.Shell")
strCnn = "Provider=SQLOLEDB;Data Source=HAL;Initial Catalog = VETEST;User ID=SYSADM;Password=SYSADM;"
Dim DwgRev, Qty, ConfQty, NonConQty, SamplQty, UnworkedQ
Dim stUpdateSQL

DwgRev = document.GetElementById("stDwgRev").value
Qty = document.GetElementById("stQty").value
ConfQty = document.GetElementById("stConform").value
NonConQty = document.GetElementById("stNonCon").value
SamplQty = document.GetElementById("stConSamp").value
UnworkedQ = document.GetElementById("stUnwork").value

'strCnn = "Provider=SQLOLEDB;Data Source=HAL;Initial Catalog = VETEST;User ID=SYSADM;Password=SYSADM;"
stUpdateSQL = "Update WORK_ORDER set USER_2 = '" & DwgRev & "', USER_6 = '" & Qty & "', USER_7 = '" & ConfQty & "'," & _
	" USER_8 = '" & NonConQty & "', USER_9 = '" & SamplQty & "', USER_10 = '" & UnworkedQ & "'" & _
	" where TYPE = '" & stType & "' and BASE_ID = '" & stBase & "' and LOT_ID = '" & stLot & "' and SPLIT_ID = '" & stSplit & "'" & _
	" and SUB_ID = '" & stSub & "'"";"
msgbox stUpdateSQL
Set rsPROJ=CreateObject("ADODB.Recordset") 
	rsPROJ.Open stUpdateSQL, strCnn

QuitHTA
	
End Function
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub QuitHTA
	window.close()
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

</script>
</head>
<style>.t1 {font-family: Times; font-size: 14pt;}.s1 {font-family: Arial; font-size: 8pt;}.p1 {font-family: Verdana; font-size: 9pt;}</style>
<body bgcolor="#CCCCCF" onload="HTA_Load()"</center>
<form name="dbx">
<span id="Span1"></span>
<p align="center">
<span id="Span2"><input type='button' id='cmdOK' value='  Submit  ' onclick='chkSubmit'></Span></p>
</form>
</body>
</html>

Open in new window

0
Comment
Question by:jeremyj54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33707026
hi

does it gives any error?
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 33707047
Hi, in your chkSubmit function, change
On Error Resume Next
to
Err.Clear
On Error GoTo 0

and you will see the error that might be raised by your query.

Regards,

Rob.
0
 
LVL 5

Expert Comment

by:Varghese Jacob
ID: 33707048
Instead of directly using the connection string when creating the recordset object, try connecting to the database using the connection object and check whether it is successful.

For debugging HTA applications, you can use the below link

http://www.tech-archive.net/Archive/Scripting/microsoft.public.scripting.wsh/2005-02/0176.html
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question