CODEWITHSUNDEEP
powershellms-accessvbscripthta
FabFab
FabFab

Reputation: 13

Doing an Access Query with PS Script over VB and writing output in HTA Textbox

I am new to PowerShell and got the following Task.
I have to Create a HTA GUI where you can write the Name to search for in the Access Query.
The VB Script in the HTA File starts the PS Script and passes the Parameter of the user Input in the HTA TextBox. After that the PowerShell Script does a Access Query with the user Input to get some results. These results should somehow get back to the VB/HTA File so it can Output every single result in an other TextBox.

Is this even possible to do? If yes, i would appreciate some solution ideas.

EDIT:

VB/HTA

VB/HTA

Wrong Format, should be like a table

Upvotes: 0

Views: 236

Answers (2)

Gordon
Gordon

Reputation: 1165

You have strLine in the HTA code that reads the text file. Your code is reading the text file and taking 1 line and putting it in a text input box. In order to show the results as a table you need to create the table HTML and pass that HTML to a div element. 

strLine = "<table>"
Do While Not strLines.AtEndOfStream
   strLine = strLine & "<tr><td>" & strLines.ReadLine() & "</td></tr>"
Loop
strLine = strLine & "</table>"
Weiterleitung_div.innerHTML = strLine

You will need to change Weiterleitung_id.value = strLine and instead pass the strLine HTML to a element in the body. Add to the body and then

Upvotes: 1

Gordon
Gordon

Reputation: 1165

Yes it is possible. Try the code below with an access database that has 3 fields first_name, last_name, email

Save the file as Employees.mdb (you can use .accdb but you'll have to change the name in code) MAKE SURE THE ACCESS FILE IS IN THE SAME FOLDER AS THE HTA.

I copied in some data from www.mockaroo.com to test it out (I don't get paid to say that it's just very useful)

This is a very basic example but it is possible to create a nicer GUI in an HTA than in Access.

<title>Employee Directory</title>
<head>
<HTA:APPLICATION
ID="EMPDIR"
APPLICATIONNAME="Employee Directory"
SINGLEINSTANCE="YES"
>
<!-- makes the hta run using IE9 otherwise it runs as IE5 or 6.  You can change this to edge -->
<meta http-equiv="x-ua-compatible" content="IE=9"/>

<style>

body {font-family:arial; background:#efefef; color:#333}


</style>


<script language="vbscript">
' ///// this creates the connection when the file is first run.

' //// get the current path if the database file is in the same folder as the hta file.  if it's not then enter the full path manually below
Set objFSO = CreateObject("Scripting.FileSystemObject")
curDir = objFSO.GetAbsolutePathName(".") & "\"


Dim oCon: Set oCon = CreateObject("ADODB.Connection")
Dim oRs: Set oRs = CreateObject("ADODB.Recordset")
strCon = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq="& curDir & "Employees.mdb;"

oCon.ConnectionString= strCon

sub getEmployeeList
' gets a list of employees based on the value in the searchbox input.

oCon.open

strSQL = "SELECT * FROM Employees WHERE (first_name like '%" & searchbox.value & "%') or (last_name like '%" & searchbox.value & "%')"

Set oRs = oCon.Execute(strSQL)
strHTML = "<table>"
do while not oRs.EOF

strHTML = strHTML & "<tr><td>" & oRs.fields("first_name") & "</td><td>" & oRs.fields("last_name") & "</td><td>" & oRs.fields("email") & "</td></tr>"

oRs.movenext
loop

oCon.close

strHTML = strHTML & "</table>"

divEmployeeList.innerHTML = strHTML

end Sub

</script>


</head>

<body>

<div id="search"><input type="text" id="searchbox" style="font-size:16pt; margin:10px;"/> <input type="button" value="search" name="submitsearch" style="font-size:16pt;" onclick="getEmployeeList" language="vbscript"></div>


<div id="divEmployeeList" style="width:90%; height:300px; overflow-y:scroll; border:solid 1px #666; margin:10px; background:#fff">-</div>


</body>

Upvotes: 0

Related Questions