asked on

Dependent Drop List - SQL and VBS Part 2

I had this question after viewing Dependent Drop List - SQL and VBS.

I'd like to modify the below code to filter on any database field. So, I have a table with addresses - I want to filter zip codes, and see the cities in that zip code. Thanks!

<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)-10 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 & "'"
  Dim oRS : Set oRS = oDb.Execute(sQL)

  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


</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>

Open in new window

tracyms

ASKER

I also found the below code searching web which could work too if this helps.

I don't know how to code it to use my sql table. So where do I put the database connection strings? And how do I query the specific fields?

Example:

My SQL database = strConnect = "Provider=SQLOLEDB;Server=myServer;Database=myDatabase;User ID=myUserID;Password=myPassword"

My table = Addresses (street, city, state, zip)
My first drop down = state (list of unique states)
My second drop down = city (from state selection in first drop down)

This would be vbscript and sql in an HTA.

Thank you.

<html>
	<head>
		<title>Test</title>
		<HTA:APPLICATION
		  APPLICATIONNAME="Test"
		  ID="Test"
		  VERSION="1.0"/>
	</head>
	
	<script language="VBScript">
	
		Sub Window_OnLoad
			Populatedropdown1
		End Sub
		
		Sub Populatedropdown1
				For i = 0 To 5
			    	Set opt = document.createElement("option")
					opt.Value = i
					opt.Text = "Option " & CStr(i)
					dropdown1.add opt
				Next	
		End Sub
		
		Sub Populatedropdown2
				For Each opt in dropdown2.Options
					opt.RemoveNode
				Next 
				For i = dropdown1.value To 10
			    	Set opt = document.createElement("option")
					opt.Value = i
					opt.Text = "Option " & CStr(i)
					dropdown2.add opt
				Next	
		End Sub
	</script>
	
	<body bgcolor="white">
	
		<p>Select 1: <select name="dropdown1" id="dropdown1" onchange="Populatedropdown2"></select></p>
		<p>Select 2: <select name="dropdown2" id="dropdown2" ></select></p>
		
	</body></html>

Open in new window

Bill Prew

At one level I understand what you are describing. But at a bit more detail I'm not sure how you see it working?

It sounds like you want to be able to filter on any field, and see the subset of values for a related field based on the filter selection. Like STATE filters for CITY, and then CITY filters for ADDRESS. But where / how will those relationships be known, if you want to keep it generic?

»bp

tracyms

ASKER

Hi Bill,

Perhaps I'm making it too complicated when it's really simple (for those familiar with sql/vbs) but I can't convey it properly.

Yes, I only would like to filter a field based on another field in a database table. I'm using the state/city filter as an example so yes it can be generic so I can fill in my own database fields. The very first link I posted back to the similar question is just what I want to do but use different values/fields. If you read this from that previous post it may make a little more sense:

My question

"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..."

And Steve's answer

"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..."

Does this help at all? I thought posting the previous question would make it easier to do/understand...sorry. Let me know if you need further clarification. Thanks!

Bill Prew

So how are you imagining this working? Is there just one table involved? Since you are wanting dynamic filtering, the user would have to select the first column to filter on from a pre-defined list of the columns names in the table? Then how would the second level field be determined? For each level 1 column would there only be one level 2 column that would be filtered after that, and that list would be pre-defined? Or could they mix and match any level 1 with any level 2 column? Do you only want two levels of filtering? Anything else you can describe about how it would work?

»bp

tracyms

ASKER

Two levels is fine. The first dropdown list (populated from the database) will list unique STATE
, which - when a state is selected - will filter the second dropdown list of CITIES.

These STATEs and CITIES will be in a table called "ADRESSES".

Is there just one table involved? Yes

It doesn't have to dynamic per-se, first column would be the driving force for the second column. I'll post a picture if I can one, thanks!

tracyms

ASKER

Example

This is for excel but same concept, two columns and one drop down dependent on another.

tracyms

ASKER

I'm still searching for an answer, if I find something I'll post back. All I'm trying to do is replace this section with the name of a database field, that's really all I need to do. Again, the code is already written here - https://www.experts-exchange.com/questions/29132963/Dependent-Drop-List-SQL-and-VBS.html .

' Populate year field with last 10 years in reverse order
AddOption "Field1","Select Year",""
Dim YYYY
For YYYY=Year(Date) To Year(Date)-10 Step -1
AddOption "Field1",CStr(YYYY),CStr(YYYY)
Next

AddOption "Field2","Select Year first",""

End Sub

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.