tracyms
asked on
Dependent Drop List - SQL and VBS
I had this question after viewing Populate Text Box From Dropdown List.
The code below (as gotten from question I posted above) works to populate drop down lists independently. I'd like to modify the code to be dependent drop down lists.
So let's say I have a table called CODES, which contains the following:
Year | Code_Number
1999 | 1001-10
1999 | 1002-10
2000 | 2002-05
I’d like two drop downs. One drop down has the year, which when selected will filter the second drop down of the Code_Number. So in the example above, if I select 1999 from the first drop down, 1001-10 and 1002-10 will fill in the second drop down.
Additionally, I’d like both selections in text box/drop down to be stored in a text file. While I do have a sub routine for that, I keep getting undefined errors.
The table has hundreds of thousands of entries and takes a few seconds to load when I run the query so I'm wondering if the table can be loaded with perhaps just the years (or maybe a text box to enter the year) and then fetch the Code_Number only for that year so it'll load faster?
Let me know if further clarification is needed. Thanks!
Tracy
The code below (as gotten from question I posted above) works to populate drop down lists independently. I'd like to modify the code to be dependent drop down lists.
So let's say I have a table called CODES, which contains the following:
Year | Code_Number
1999 | 1001-10
1999 | 1002-10
2000 | 2002-05
I’d like two drop downs. One drop down has the year, which when selected will filter the second drop down of the Code_Number. So in the example above, if I select 1999 from the first drop down, 1001-10 and 1002-10 will fill in the second drop down.
Additionally, I’d like both selections in text box/drop down to be stored in a text file. While I do have a sub routine for that, I keep getting undefined errors.
The table has hundreds of thousands of entries and takes a few seconds to load when I run the query so I'm wondering if the table can be loaded with perhaps just the years (or maybe a text box to enter the year) and then fetch the Code_Number only for that year so it'll load faster?
<html>
<head>
<title>SQL DropDown Demo</title>
<hta:application
id="demo"
icon="http://www.iconarchive.com/download/i62658/ampeross/qetto-2/search.ico" >
</hta>
<script type="text/vbscript">
Option Explicit
Sub Window_OnLoad
ReadSQLData
' Also add some extra values in addition to the SQL data:
AddOption "Field1","Extra Option 1","Value for Option 1"
AddOption "Field1","Extra Option 2","Value for Option 2"
AddOption "Field2","Extra Option 1","Value for Option 1"
AddOption "Field2","Extra Option 2","Value for Option 2"
End Sub
Sub ReadSQLData
' Read information from SQL server and put into Field1 and Field2
Dim oDb : Set oDb = CreateObject("ADODB.Connection")
oDb.Open "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=myServer;Database=myDatabase;"
Dim sQL : sQL = "SELECT * FROM [CODE]"
Dim oRS : Set oRS = oDb.Execute(sQL)
Do Until oRS.EOF
AddOption "Field1",oRS.Fields("Year").Value,oRS.Fields("Year").Value
AddOption "Field2",oRS.Fields("Code_Number").Value,oRS.Fields("Code_Number").Value
oRS.MoveNext
Loop
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
**********************Send Contents of Text Boxes to File when click button**********************
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
' Declare variable to write to file
getOutput0 = Text1
getOutput1 = Text2
' write database values to file
Set objTS = objFSO.OpenTextFile(strFileName, ForWriting)
objTS.WriteLine(getOutput0)
objTS.WriteLine(getOutput1)
objTS.Close()
End Sub
**********************END**********************
</script>
</head>
<body>
<form>
Field1: <select id="Field1" onchange="HandleChange me.id,me.value">
</select>
<br>
Field2: <select id="Field2" onchange="HandleChange me.id,me.value">
</select>
<br>
// Input box to show value selected from dropdown
Text1: <input type="text" maxlength="30" size="40" id="Text1">
<br>
Text2: <input type="text" maxlength="30" size="40" id="Text2">
**********************Button to Send to File (call RunScript sub)**********************
<input type="button" value="Send to File" name="btn_runscript" onClick="vbs:RunScript"><br><br>
**********************END**********************
</form>
</body>
</html>Let me know if further clarification is needed. Thanks!
Tracy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The text file takes the values from the text box and writes them to the user's temp directory on their pc and names the file "TextFile.txt". So, if 1999 is in the first text box and 1001-10 is in the second text box, when the user clicks the button to invoke the RunScript subroutine creates the TextFile.txt file and it's contents looks like this:
1999
1001-10
I use the text file in an array so I can pull it in another application.
'Here is the code to get the contents of that file into an array and pull it in to the fields of my application:
Dim oFSO : Set oFSO = CreateObject("Scripting.Fi
If oFSO.FileExists(createobje
If oFSO.GetFile(createobject(
arrLines = Split(oFSO.OpenTextFile(cr
'Now you have all the lines in the file in an array, so if you want src to be the first line and src1 to be the second line (not a fan of those names by the way), simply do this:
Field2 = arrLines(0)
Field3 = arrLines(1)
Else
Field2 = ""
Field3 = ""
End If
End If
Does that answer your question?
1999
1001-10
I use the text file in an array so I can pull it in another application.
'Here is the code to get the contents of that file into an array and pull it in to the fields of my application:
Dim oFSO : Set oFSO = CreateObject("Scripting.Fi
If oFSO.FileExists(createobje
If oFSO.GetFile(createobject(
arrLines = Split(oFSO.OpenTextFile(cr
'Now you have all the lines in the file in an array, so if you want src to be the first line and src1 to be the second line (not a fan of those names by the way), simply do this:
Field2 = arrLines(0)
Field3 = arrLines(1)
Else
Field2 = ""
Field3 = ""
End If
End If
Does that answer your question?
ASKER
Steve,
I tried your code and got it to work. I finally figured out why the years only went back 10, just had to read - LOL!
For YYYY=Year(Date) To Year(Date)-10 Step -1
The years in my database go back 60 so I updated the above to For YYYY=Year(Date) To Year(Date)-60 Step -1
How would this work if there was a different field in the database to filter? Meaning, if I wanted to filter a zip code for example in the first drop down and second populates users who live in that zip code. I guess I was looking to use only the database fields/SQL statements to do the work, the addition of your code threw me off a bit.
Also, I got the text values to write to the TextFile.txt file by add ".value" to it:
getOutput0 = Text1.value
getOutput1 = Text2.value
One more thing, I wanted to clear the values after selections so I added code to clear the text boxes after clicking the "Send to File" button, added this to the end of my RunScript subsroutine:
Document.getElementById("T
Document.getElementById("T
However, I couldn't figure out how to set your drop downs back to default options (Select Year/Select Year First).
See updated code below:
I tried your code and got it to work. I finally figured out why the years only went back 10, just had to read - LOL!
For YYYY=Year(Date) To Year(Date)-10 Step -1
The years in my database go back 60 so I updated the above to For YYYY=Year(Date) To Year(Date)-60 Step -1
How would this work if there was a different field in the database to filter? Meaning, if I wanted to filter a zip code for example in the first drop down and second populates users who live in that zip code. I guess I was looking to use only the database fields/SQL statements to do the work, the addition of your code threw me off a bit.
Also, I got the text values to write to the TextFile.txt file by add ".value" to it:
getOutput0 = Text1.value
getOutput1 = Text2.value
One more thing, I wanted to clear the values after selections so I added code to clear the text boxes after clicking the "Send to File" button, added this to the end of my RunScript subsroutine:
Document.getElementById("T
Document.getElementById("T
However, I couldn't figure out how to set your drop downs back to default options (Select Year/Select Year First).
See updated code below:
<html>
<head>
<title>My HTML application</title>
<HTA:APPLICATION
APPLICATIONNAME="My HTML application"
ID="MyHTMLapplication"
VERSION="1.0"/>
</head>
<script language="VBScript">
Option Explicit
Sub Window_OnLoad
' Populate year field with last 10 years in reverse order
AddOption "Field1","Select Year",""
Dim YYYY
For YYYY=Year(Date) To Year(Date)-60 Step -1
AddOption "Field1",CStr(YYYY),CStr(YYYY)
Next
AddOption "Field2","Select Year first",""
End Sub
Sub ReadSQLData(WhichYear)
' Read information from SQL server and put into Field1 and Field2
Dim oDb : Set oDb = CreateObject("ADODB.Connection")
oDb.Open "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=myServer;Database=myDatabase;"
Dim sQL : sQL = "SELECT Code_Number FROM [CODE] WHERE Year='" & Whichyear & "'"
Do Until oRS.EOF
AddOption "Field2",oRS.Fields("Code_Number").Value,oRS.Fields("Code_Number").Value
oRS.MoveNext
Loop
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 ClearField2
Dim objOption
For Each objOption in Document.getElementById("Field2").options
objOption.RemoveNode
Next
AddOption "Field2","Now please choose code",""
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"
If SetValue="" Then
MsgBox "Please choose a value for the year"
Document.getElementById("Field2").disabled=True
Document.getElementById("Field2").value=""
Else
' A value has been chosen for year. Enable the code field, clear it's choices and get them from SQL
Document.getElementById("Field2").disabled=False
Document.getElementById("Text1").value=SetValue
ClearField2
ReadSQLData SetValue
End If
Case "Field2"
If SetValue="" Then
' Nothing selected
Document.getElementById("Text2").value=""
Document.getElementById("btn_runscript").disabled=True
Else
Document.getElementById("Text2").value=SetValue
Document.getElementById("btn_runscript").disabled=False
' Trigger doing something with your code....
End If
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
Dim strFileName
Dim oTxtFile
Dim inContent
Dim fieldname
Dim getOutput0
Dim getOutput1
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
' Declare variable to write to file
getOutput0 = Text1.value
getOutput1 = Text2.value
' write database values to file
Set objTS = objFSO.OpenTextFile(strFileName, ForWriting)
objTS.WriteLine(getOutput0)
objTS.WriteLine(getOutput1)
objTS.Close()
Document.getElementById("Text1").value=""
Document.getElementById("Text2").value=""
End Sub
</script>
<body bgcolor="white">
<DIV STYLE="font-family: Arial; font-size: 9pt; color: black">
Field1: <select id="Field1" onchange="HandleChange me.id,me.value"></select>
<br>
Field2: <select id="Field2" onchange="HandleChange me.id,me.value" disabled=true></select>
<br><br>
Text1: <input type="text" maxlength="30" size="40" id="Text1">
<br>
Text2: <input type="text" maxlength="30" size="40" id="Text2">
<br>
</table>
<input type="button" value="Send to File" name="btn_runscript" onClick="vbs:RunScript" disabled=true><br><br>
</DIV>
</body>
</html>
You'd want to have a SQL query to produce data for the first drop down as one off to populate it then in the handlechange sub for that field do second lookup using SQL of filtered data.
I suggest if you can explain full ideas you are trying to do can more easily make coffee work for the different cases.
Steve
I suggest if you can explain full ideas you are trying to do can more easily make coffee work for the different cases.
Steve
ASKER
Ok, thanks! It does work for my purposes currently. Can you tell me how to re-set the dropdowns to default option values? I tried calling it after my RunScript subroutine but it didn't work:
Document.getElementById("T
Document.getElementById("T
Run Window_OnLoad ()
Thanks again Steve!
Document.getElementById("T
Document.getElementById("T
Run Window_OnLoad ()
Thanks again Steve!
They are Field1 and Field2... Just setting Field1 to blank should work as it will cause Field2 to blank and options to disappear and grey out.
If you want help with other but re post code etc then say.
Steve
If you want help with other but re post code etc then say.
Steve
ASKER
Ok, got it thanks!
Thanks for the answer, can help with text file bits if wanted still.
Steve
Steve
ASKER
Hi Steve,
I opened another question I thought you could answer based on this question. I already asked it and this was your answer but you had already solved my initial question and we didn't work on that part:
"You'd want to have a SQL query to produce data for the first drop down as one off to populate it then in the handlechange sub for that field do second lookup using SQL of filtered data."
Link here - https://www.experts-exchange.com/questions/29135962/Dependent-Drop-List-SQL-and-VBS-Part-2.html#questionAdd
I opened another question I thought you could answer based on this question. I already asked it and this was your answer but you had already solved my initial question and we didn't work on that part:
"You'd want to have a SQL query to produce data for the first drop down as one off to populate it then in the handlechange sub for that field do second lookup using SQL of filtered data."
Link here - https://www.experts-exchange.com/questions/29135962/Dependent-Drop-List-SQL-and-VBS-Part-2.html#questionAdd
Is this just to store the last values for the fields as default for the user?
Can the values in the SQL tables disappear, i.e. you set a value from default but the value is no longer a choice from SQL table?