Filter Dropdown Based on Another Dropdown using SQL and VB Script

tracyms
tracyms used Ask the Experts™
on
Hello Experts,

I’m trying to filter a drop down list based on another dropdown list using SQL/vbscript/hta. I have it working sort of thanks to Experts Exchange where I can populate the dropdowns from the database.

Now, I need to do two things:

1. Filter the dropdown lists
2. Send the values to a text file

The query in the code below shows the data like this:

ARA_PERSON_ID               ARA_TYPE_DESC                     INVOICE_TERM_DESC
0000000                                Student - Credit                       2004 Fall Reporting Term
0000000                                Student - Credit                       2004 Spring Reporting Term
0000000                                Student - Credit                       2005 Intersession Reporting
0000000                                Student - Credit                       2009 Fall Reporting
0000000                                Student - Credit                       2012 Fall Reporting
0000000                                Student - Credit                       2013 Fall Reporting
0000000                                Student - Credit                       2013 Spring Reporting
0000000                                Student - Credit                       2016 Spring Reporting
0000000                                Student - Credit Free               2003 Fall Reporting Term
0000000                                Student - Credit Free               2004 Spring Reporting Term
0000000                                Student - Credit Free               2006 Fall Reporting Term


 But I need it to show like this:

ARA_PERSON_ID          ARA_TYPE_DESC         INVOICE_TERM_DESC
0000000                          Student - Credit Free         2003 Fall Reporting Term
                                                                                2004 Spring Reporting Term
                                                                                 2006 Fall Reporting Term


So, the ID should be entered in the text area. The first dropdown box should filter the ID by ARA_TYPE_DESC (in this example only two options - Student - Credit Free or Student - Credit), then the second dropdown box should filter based on the selection of the first dropdown box (in this case only 3 options).

Once all three things are entered, when clicking search they show on the screen and get sent to a text file.

I had the options set to output to a hidden text field because the dropdown selections weren’t being sent to the text file and I thought if I stored them in a text field I could reference them that way but not sure how to get that to work. This is a lot of information I know, I hope it make sense. Thanks!


<html>
<head>
<title>Office</title>
<HTA:APPLICATION 
APPLICATIONNAME="Search"
     BORDER="thin"
     SCROLL="no"
     SINGLEINSTANCE="yes"
     WINDOWSTATE="normal"
	 SysMenu="no"
>

	<script language="vbScript">
Sub Window_onLoad
End Sub
 		
Sub Default_Buttons
If Window.Event.KeyCode = 13 Then
btn_runscript.Click
End If
End Sub

Sub RunScript
Const ForReading = 1
Const ForWriting = 2
Dim objFSO 'File System Object
Dim objTS 'Text Stream Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get current logged in username for path
strFileName = createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt"

' Check if file exists, if not create it
If Not objFSO.FileExists(strFileName) then
Set oTxtFile = objFSO.CreateTextFile(createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt") 
oTxtFile.Close
End If

' Open file for reading
If objFSO.FileExists(strFileName) then
Set objTS = objFSO.OpenTextFile(strFileName, ForReading)

' create variable to hold default value
If objTS.AtEndOfStream Then
inContent = ""
Else
inContent = objTS.ReadLine
End If
objTS.Close()
End If

' If input box empty, make application fields empty
If strKey.value = "" then
msgbox "No data entered"
strKey.Focus
		
Else
strConnection = "Provider=SQLOLEDB;Server=myServer;Database=myDataBase;User ID=User;Password=MyPassword"

strSQL = "SELECT" & _
" DISTINCT ([DATA].[dbo].[ODS_AR_INVOICES].[INV_TERM]),"& _
" [DATA].[dbo].[ODS_AR_ACCTS].[ARA_PERSON_ID]," & _
" [DATA].[dbo].[ODS_AR_ACCTS].[ARA_TYPE_DESC]," & _
" [DATA].[dbo].[ODS_AR_INVOICES].[INVOICE_TERM_DESC]," & _
" [DATA].[dbo].[ODS_PERSON].[LAST_NAME]," & _
" [DATA].[dbo].[ODS_PERSON].[PREFIX]," & _
" [DATA].[dbo].[ODS_PERSON].[FIRST_NAME]," & _
" [DATA].[dbo].[ODS_PERSON].[MIDDLE_NAME]" & _
" FROM [DATA].[dbo].[ODS_AR_INVOICES] RIGHT OUTER JOIN [DATA].[dbo].[ODS_AR_ACCTS]" & _
" ON ([DATA].[dbo].[ODS_AR_ACCTS].[ARA_PERSON_ID]=[DATA].[dbo].[ODS_AR_INVOICES].[INV_PERSON_ID]" & _
" and [DATA].[dbo].[ODS_AR_ACCTS].[ARA_AR_TYPE]=[DATA].[dbo].[ODS_AR_INVOICES].[INV_AR_TYPE])" & _
" INNER JOIN [DATA].[dbo].[ODS_PERSON] ON ([DATA].[dbo].[ODS_PERSON].[ID]=[DATA].[dbo].[ODS_AR_ACCTS].[ARA_PERSON_ID])" & _
" WHERE [DATA].[dbo].[ODS_AR_ACCTS].[ARA_PERSON_ID]  = '" & strKey.value & "'"

 AddOption  "Field1","Select Student Type","Value for Option 1"
 AddOption  "Field2","Select Term","Value for Option 1"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly
do while objRecordSet.EOF = false

AddOption "Field1",objRecordSet.Fields("ARA_TYPE_DESC").Value,objRecordSet.Fields("ARA_TYPE_DESC").Value 
AddOption "Field2",objRecordSet.Fields("INVOICE_TERM_DESC").Value,objRecordSet.Fields("INVOICE_TERM_DESC").Value 
	
' Declare variable for output to screen 
strHTML = strHTML & objRecordSet("ARA_PERSON_ID") & "<br>" &  objRecordSet("ARA_TYPE_DESC") & "<br>" & objRecordSet("INVOICE_TERM_DESC")
DataArea.InnerHTML = strHTML
 
' Declare variable to write to file
getOutput0 = objRecordSet("ARA_PERSON_ID")
getOutput1 = objRecordSet("ARA_TYPE_DESC")
getOutput2 = objRecordSet("INVOICE_TERM_DESC")


objRecordSet.MoveNext
loop
objRecordSet.Close
Set objRecordSet=Nothing
objConnection.Close
Set Connection=Nothing


' If database match found, output to screen
if strHTML <> "" then
DataArea.InnerHTML = "<b><font color='red' face='Arial' style='background-color: #ffffff;'>" &  strHTML & "</font></b>"    

' write database values to file
Set objTS = objFSO.OpenTextFile(strFileName, ForWriting)
objTS.WriteLine(getOutput0)
objTS.WriteLine(getOutput1)
objTS.WriteLine(getOutput2)
objTS.Close()

' If database search not found, show message
Else
msgbox "No records found.", vbsystemmodal

End If
End If
End Sub

Sub AddOption(fieldname,SetText,SetValue)
' Pass in the control ID and the text and values to add to it
	  Dim oDD, oOpt
	  Set oDD = document.getElementById(fieldname)
      Set oOpt = oDD.document.createElement("option")
      oOpt.Text  = SetText
      oOpt.Value = SetValue
      oDD.Options.Add oOpt
End Sub


Sub HandleChange(fieldname,SetValue)
' Pass in the ID and selected value of the control to handle a change for
  Select Case fieldname
    Case "Field1"
    	Document.getElementById("Text1").value=SetValue
    Case "Field2"
        Document.getElementById("Text2").value=SetValue
  End Select  
  MsgBox "You have amended " & fieldname & " to " & SetValue
End Sub
 

Sub ClearWindow
'Refresh button clears text box and screen data
DataArea.InnerHTML = ""
strKey.value = ""
End Sub


Sub TextBoxEnter
If strKey.Value = "Enter Student ID" Then
strKey.Value = ""
End If
End Sub


Sub itClosed
'Exit button clears text box, screen data, text file data and closes application
DataArea.InnerHTML = ""
strKey.value = ""
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFileName = createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt"
If objFSO.FileExists(strFileName) then
Set objFile = objFSO.OpenTextFile(createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt", ForWriting)
objFile.Write ""
objFile.Close
End If
Dim oShell
Set oShell = CreateObject("WScript.Shell")
oShell.Run "CMD /C DEL ""%TEMP%\*.hta"" /S"
Set oShell = Nothing
window.close
End Sub
	

</script>
</head>


<body background="Images\5894877-hd-images-of-nature[1].jpg" onkeypress='vbs:Default_Buttons'>
<table width='90%' height = '100%' align='center' border='0'>
<tr>
<td align='right'>
<input type="button" value="Exit" name="btn_exit"  onClick="itClosed">
</td>
</tr>

<tr>
<td align='center'>
<span id = "DataArea"></span>
<br>
<br>
<input type="text" maxlength="30" STYLE='color=#FF0000' size="40" value="Enter Student ID" id="strKey" name="strKey" onFocus = "TextBoxEnter"><br>
 Field1: <select id="Field1" onchange="HandleChange me.id,me.value"></select>Text1:  <input type="hidden" maxlength="30" size="40"  id="Text1"><br>
 Field2: <select id="Field2" onchange="HandleChange me.id,me.value"></select>Text2:  <input type="hidden" maxlength="30" size="40"  id="Text2"><br>
 // Input box to show value selected from dropdown
<br>
<input type="button" value="Search" name="btn_runscript"  onClick="vbs:RunScript"><br><br>
	<input id=runbutton type="button" value="Clear Screen" onClick="ClearWindow"><br>	
</td>
</tr>
	
</table>
</body>
</body>
</html>

Open in new window


Tracy
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:

Author

Commented:
Thanks Rob but this solution won't work for what I'm trying to accomplish. Something like this but they're using asp it seems.

LINK ON CONCURRENT SITE REMOVED

I'm still searching in addition to posting it here. Thanks again.

Author

Commented:
I got this code from a former post and replaced the query.  This query gets everything in a single row and I used the sequence number row to do an if statement in order to get the data in the correct dropdowns. While it doesn't filter like I'd like I'm further along then I was.


;with CTE_Source as
( 
   SELECT
   DISTINCT (DATA_AR_INVOICES.INV_TERM),
   DATA_AR_ACCTS.ARA_PERSON_ID,
   DATA_AR_ACCTS.ARA_TYPE_DESC,
   DATA_AR_INVOICES.INVOICE_TERM_DESC,
   DATA_PERSON.LAST_NAME,
   DATA_PERSON.PREFIX,
   DATA_PERSON.FIRST_NAME,
   DATA_PERSON.MIDDLE_NAME
   FROM
   DATA_AR_INVOICES RIGHT OUTER JOIN DATA_AR_ACCTS ON (DATA_AR_ACCTS.ARA_PERSON_ID=DATA_AR_INVOICES.INV_PERSON_ID and 
   DATA_AR_ACCTS.ARA_AR_TYPE=DATA_AR_INVOICES.INV_AR_TYPE)
   INNER JOIN DATA_PERSON ON (DATA_PERSON.ID=DATA_AR_ACCTS.ARA_PERSON_ID)
   WHERE
   DATA_AR_ACCTS.ARA_PERSON_ID IN ( '00000000' )
),
  CTE_Unpivot as
( 
  select cast(ARA_PERSON_ID as varchar(30)) [1] 
        ,cast(ARA_TYPE_DESC  as varchar(30)) [2]
        ,cast(LAST_NAME  as varchar(30)) [3]
        ,cast(PREFIX  as varchar(30)) [4]
        ,cast(FIRST_NAME  as varchar(30)) [5]
        ,cast(MIDDLE_NAME  as varchar(30)) [6]
        ,cast(INVOICE_TERM_DESC  as varchar(30)) [7]
  from CTE_Source 
) 
  select distinct ColumnSequence,Contents from CTE_Unpivot
  unpivot (Contents for ColumnSequence in ([1],[2],[3],[4],[5],[6],[7]))u

AddOption  "Field1","Select Student Type","Value for Option 1"
 AddOption  "Field2","Select Term","Value for Option 1"



Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly
do while objRecordSet.EOF = false



IF  objRecordSet("ColumnSequence")= 2 Then


  AddOption "Field1",objRecordSet.Fields("Contents").Value,objRecordSet.Fields("Contents").Value 

  
ElseIf objRecordSet("ColumnSequence") = 7 Then

    AddOption "Field2",objRecordSet.Fields("Contents").Value,objRecordSet.Fields("Contents").Value 


	
  End If

Open in new window


I still need to get the selections into the text file by declaring as values but now they aren't recognized as field names due to the if statements (I'm assuming):

' Declare variable to write to file
getOutput0 = objRecordSet("ARA_PERSON_ID")
getOutput1 = objRecordSet("ARA_TYPE_DESC")
getOutput2 = objRecordSet("INVOICE_TERM_DESC")

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Should I give this more time or close this question? Thanks.

Author

Commented:
I've been working on this off and on and I have it where I can use it like I need. My only issue now is that the drop down database fields don't reset when I enter another ID in the text area and hit enter on my keyboard or the Search button.  I tried an exit sub but then it didn't do anything...maybe I just didn't know where to put it? Any help appreciated, I'm close! Thanks!


<html>
<head>
<title>Bursar&rsquo;s Office</title>
<HTA:APPLICATION 
APPLICATIONNAME="DB_Search"
     BORDER="thin"
     SCROLL="no"
     SINGLEINSTANCE="yes"
     WINDOWSTATE="normal"
	 SysMenu="no"
>

	<script language="vbScript">
Sub Window_onLoad



End Sub
 


		
Sub Default_Buttons
If Window.Event.KeyCode = 13 Then
btn_runscript.Click
End If
End Sub
Sub AddOption(fieldname,SetText,SetValue)
' Pass in the control ID and the text and values to add to it
	  Dim oDD, oOpt
	  Set oDD = document.getElementById(fieldname)
      Set oOpt = oDD.document.createElement("option")
      oOpt.Text  = SetText
      oOpt.Value = SetValue
      oDD.Options.Add oOpt
End Sub


Sub HandleChange(fieldname,SetValue)
' Pass in the ID and selected value of the control to handle a change for
  Select Case fieldname
    Case "Field1"
    	Document.getElementById("Text1").value=SetValue
    Case "Field2"
        Document.getElementById("Text2").value=SetValue
	 Case "Field3"
        Document.getElementById("Text3").value=SetValue	
  End Select
  
  'MsgBox "You have amended " & fieldname & " to " & SetValue

End Sub

Sub RunScript
Const ForReading = 1
Const ForWriting = 2
Dim objFSO 'File System Object
Dim objTS 'Text Stream Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get current logged in username for path
strFileName = createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt"

' Check if file exists, if not create it

If Not objFSO.FileExists(strFileName) then
Set oTxtFile = objFSO.CreateTextFile(createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt") 
oTxtFile.Close
End If

' Open file for reading

If objFSO.FileExists(strFileName) then
Set objTS = objFSO.OpenTextFile(strFileName, ForReading)

' create variable to hold default value

If objTS.AtEndOfStream Then
inContent = ""
Else
inContent = objTS.ReadLine
End If
objTS.Close()
End If

' If input box empty, make application fields empty
If strKey.value <> "" then


AddOption  "Field1","Select Student Type","Value for Option 1"
AddOption  "Field2","Select Term","Value for Option 1"	

strConnection = "Provider=SQLOLEDB;Server=myServer;Database=myDataBase;User ID=User;Password=MyPassword"


strSQL = ";with CTE_Source as"& _
"( "& _
"   SELECT"& _
"   DISTINCT DATA_AR_INVOICES.INV_TERM,"& _
"   DATA_AR_ACCTS.ARA_PERSON_ID,"& _
"   DATA_AR_ACCTS.ARA_TYPE_DESC,"& _
"   DATA_AR_INVOICES.INVOICE_TERM_DESC,"& _
"   DATA_PERSON.PREFERRED_NAME"& _
"   FROM"& _
"   DATA_AR_INVOICES RIGHT OUTER JOIN DATA_AR_ACCTS ON (DATA_AR_ACCTS.ARA_PERSON_ID=DATA_AR_INVOICES.INV_PERSON_ID and"& _ 
"   DATA_AR_ACCTS.ARA_AR_TYPE=DATA_AR_INVOICES.INV_AR_TYPE)"& _
"   INNER JOIN DATA_PERSON ON (DATA_PERSON.ID=DATA_AR_ACCTS.ARA_PERSON_ID)"& _
"   WHERE"& _
"   DATA_AR_ACCTS.ARA_PERSON_ID = '" & strKey.value & "'"& _
"),"& _
"  CTE_Unpivot as"& _
"( "& _
"  select cast(ARA_PERSON_ID as varchar(30)) [1]"& _ 
"        ,cast(ARA_TYPE_DESC  as varchar(30)) [2]"& _
"        ,cast(PREFERRED_NAME as varchar(30)) [3]"& _
"        ,cast(INVOICE_TERM_DESC  as varchar(30)) [4]"& _
"  from CTE_Source"& _ 
")"& _ 
" select distinct ColumnSequence,Contents from CTE_Unpivot"& _
"  unpivot (Contents for ColumnSequence in ([1],[2],[3],[4]))u"



Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly

do while objRecordSet.EOF = false

If objRecordSet("ColumnSequence")= 3  Then
stuName.value = objRecordSet.Fields("Contents").Value 



ElseIf objRecordSet("ColumnSequence")= 2  Then
 AddOption "Field1",objRecordSet.Fields("Contents").Value,objRecordSet.Fields("Contents").Value
 stuType = Document.getElementById("Text1").value
 
 
 
ElseIf  objRecordSet("ColumnSequence")= 4 Then

AddOption "Field2",objRecordSet.Fields("Contents").Value,objRecordSet.Fields("Contents").Value 
 stuTerm = Document.getElementById("Text2").value      

  
ElseIf objRecordSet("ColumnSequence")= 1 Then 

' Declare variable for output to screen 
strHTML = strHTML & strKey.value  & "<br>"  
'& "<br>" &  objRecordSet("Full_Name")
'DataArea.InnerHTML = strHTML


End If


 
objRecordSet.MoveNext


loop

objRecordSet.Close
Set objRecordSet=Nothing
objConnection.Close
Set Connection=Nothing


' If database match found, output to screen
if strHTML <> "" then
DataArea.InnerHTML = "<b><font color='red' face='Arial' style='background-color: #ffffff;'>" &  strHTML & "</font></b>"  


' Declare variable to write to file
getOutput0 = strKey.value
getOutput1 = stuName.value
getOutput2 = stuType 
getOutput3 = stuTerm
  

' write database values to file
Set objTS = objFSO.OpenTextFile(strFileName, ForWriting)
objTS.WriteLine(getOutput0)
objTS.WriteLine(getOutput1)
objTS.WriteLine(getOutput2)
objTS.WriteLine(getOutput3)
objTS.Close()

' If database search not found, show message
Else

msgbox "No records found.", vbsystemmodal


End If
End If
 
End Sub






Sub ClearWindow
'Refresh button clears text box and screen data
DataArea.InnerHTML = ""
strKey.value = ""
stuName.value = ""
End Sub


Sub TextBoxEnter
If strKey.Value = "Enter Student ID" Then
strKey.Value = ""
End If
End Sub


Sub itClosed
'Exit button clears text box, screen data, text file data and closes application
DataArea.InnerHTML = ""
strKey.value = ""
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFileName = createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt"
If objFSO.FileExists(strFileName) then
Set objFile = objFSO.OpenTextFile(createobject("wscript.shell").expandenvironmentstrings("%TEMP%") & "\TextFile.txt", ForWriting)
objFile.Write ""
objFile.Close
End If
Dim oShell
Set oShell = CreateObject("WScript.Shell")
oShell.Run "CMD /C DEL ""%TEMP%\*.hta"" /S"
Set oShell = Nothing
window.close
End Sub
	
Sub showHelp
'Help pop up box
MsgBox "Search:" &(Chr(13) & Chr(10))& "Enter session number, click the Search button or tap Enter on your keyboard. You can type over recently entered data.",0,"HELP"
End Sub




</script>

</head>


<body background="Images\5894877-hd-images-of-nature[1].jpg" onkeypress='vbs:Default_Buttons'>

<table width='90%' height = '100%' align='center' border='0'>

<tr>
<td align='right'>
<input type="button" value="Exit" name="btn_exit"  onClick="itClosed">
</td>
</tr>


<tr>
<td align='center'>
<span id = "DataArea"></span>

<br>
<br>

<input type="text" maxlength="30" STYLE='color=#FF0000' size="40" value="Enter Student ID" id="strKey" name="strKey" onFocus = "TextBoxEnter">&nbsp

<input type="button" value="Search" name="btn_runscript"  onClick="vbs:RunScript"><br>
<input type="text" maxlength="30" STYLE='color=#FF0000' size="40"  id="stuName"><br><br>

 Student Type: <select id="Field1" onchange="HandleChange me.id,me.value"></select>
 
 Term: <select id="Field2" onchange="HandleChange me.id,me.value"></select><br>

<br>


 
<input type="button" value="Verify" name="btn_test"  onClick="vbs:RunScript"><br><br>

<input type="text" maxlength="30" size="40"  id="Text1"><br><br>
<input type="text" maxlength="30" size="40"  id="Text2"><br><br>


	<input id=runbutton type="button" value="Clear Screen" onClick="ClearWindow"><br>
	
</td>
</tr>


 
 

<!--
&nbsp
<tr>
<td align='center'>
<input id=runbutton type="button" value="Refresh" onClick="ClearWindow">&nbsp&nbsp&nbsp&nbsp&nbsp
</td>
</tr>

<tr>
<td align='center'>
<span id = "DataArea"></span>
</td>
</tr>
-->		
<tr>
<td align='left'>
<p></p>
<p></p>
<p></p>
<input type="button" value="Help" name="btn_help"  onClick="showHelp">
</tr>



</table>
</body>
</body>
</html>

Open in new window

Author

Commented:

Author

Commented:
I'm going to close this as I've haven't found a solution and perhaps its too much information to solve. I'll continue to research how to prevent database vbscript dropdown values from repeating when re-running the sub. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial