?
Solved

Update Query from HTA

Posted on 2010-09-17
3
Medium Priority
?
567 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
In this tutorial viewers will learn how to position overlapping items using z-index in CSS. They will also learn the restrictions on the z-index property.  Create a new HTML document with an internal stylesheet.: Create a div in CSS and name it Red.…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Suggested Courses

752 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