asked on

Dynamically populating SELECT box (HTML) in ASP, with values from an SQL database

I am trying to make a form, with two select boxes. The first one pulls up all the workgroups from the SQL database. The second one is empty. Then when you select one of the workgroups, everyone in that workgroup appears in the second select box. I would prefer not to use Javascript (and only one asp page). Does anyone know how to do this?

Thanks in advance.

venkateshwarr

here is a rough idea

<form>
<select name=workgpid onchange="document.forms[0].submit();">
<%
'retreive values and insert primary key values for workgroup ... like
workgpid_value = ...
Response.write "<option value='" & workgpid_value & "'>"
%>
</select>
</form>

<select onchange="document.forms[0].submit();">
<%
wid = request.form("workgpid");
query = "select * from table where id=" & wid
workgp = ...
Response.write "<option value='" & workgp & "'>"
%>
</select>

venkat.

filemanager

ASKER

Is it possible to do it without reloading the page? The only reason I don't want to use Javascript is because I'm not very good with it, but if it's easier with javascript then so be it!

venkateshwarr

Here is something without javascript. The second select need not contain onChang()...

<form>
<select name=workgpid>
<%
'retreive values and insert primary key values for workgroup ... like
workgpid_value = ...
Response.write "<option value='" & workgpid_value & "'>"
%>
</select>
<input type=submit>
</form>

<select>
<%
wid = request.form("workgpid");
query = "select * from table where id=" & wid
workgp = ...
Response.write "<option value='" & workgp & "'>"
%>
</select>

If you want to submit the form automatically then you have to use javascript somewhere.....

venkat.

hsaini

<select name=workgpid OnChange='Javascript:form.submit();'>
'Load you select box here from the database. make sure the option value is the unique column in the table where you want to load data for the secod select box.

</Select>

'Now when you select something from the first select box the form will sumbit. then you can grab the data at the top of the page.
Ex:
Dim value1
Value1 = request.form("workgpid ")

Then Do a query to load the second select box

Select idField , FieldDesc from table where workgpid = Value1

So this wasy you will get data that is related to different options that the user selects on the first dropdown.

hope it helps

fritz_the_blank

Here is some code that I wrote to do that (it uses the northwind database for the example). For an explanation, please see my article at:

http://www.fairfieldconsulting.com/asp_multipleSelects.asp

<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
dim strDataPath, strConnectString, objConnection, strCountry, strRegion, strCity, objRS, strSelected

strCountry = Request.Form("Country")
strRegion = Request.Form("Region")
strCity = Request.Form("City")

'set connection strings for entire application
strDataPath = server.MapPath("NorthWind.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                  + " Data Source= " & strDataPath & ";"_
                  + " Mode=Share Deny None;User Id=admin;PASSWORD=;"

if not IsObject("ojbConnection") then
      set objConnection=Server.CreateObject("ADODB.Connection")
      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout = 10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if
end if

sub makeCountry()
      if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
      end if
      if objRS.state <> 0 then
            objRS.close
      end if
      objRS.Open "SELECT DISTINCT Country FROM Customers ORDER BY Country",objConnection,3,3
      Response.Write("<option></option>" & VBCRLF )
      do while not objRS.EOF
            if objRS("Country") = strCountry then
                  strSelected = " Selected "
            else
                  strSelected = ""
            end if
            Response.Write("<option" & strSelected & ">" & objRS("Country") & "</option>" & VBCRLF )
            objRS.MoveNext
      loop
      objRS.Close
      set objRS=Nothing
end sub

sub makeRegion()
      if strCountry <> "" then
            if not isObject("objRS") then
                  set objRS=Server.CreateObject("ADODB.RecordSet")
            end if
            if objRS.state <> 0 then
                  objRS.close
            end if
            objRS.Open "SELECT DISTINCT Region FROM Customers WHERE Country = '" & strCountry & "' ORDER BY Region",objConnection,3,3
            if objRS.eof then
                  Response.Write("<option>No Regions Found</option>")
            else
                  Response.Write("<option>Select Region Now</option>" & VBCRLF )
                  do while not objRS.EOF
                        if objRS("Region") = strRegion then
                              strSelected = " Selected "
                        else
                              strSelected = ""
                        end if
                        Response.Write("<option" & strSelected & ">" & objRS("Region") & "</option>" & VBCRLF )
                        objRS.MoveNext
                  loop
            end if
            objRS.Close
            set objRS=Nothing
      else
            Response.Write("<option>Select a Country First</option>")
      end if
end sub

sub makeCity()
      if strRegion <> "Select a Country First" AND strRegion <> "Select Region Now" AND strRegion <>"" then
            if not isObject("objRS") then
                  set objRS=Server.CreateObject("ADODB.RecordSet")
            end if
            if objRS.state <> 0 then
                  objRS.close
            end if
            objRS.Open "SELECT DISTINCT City FROM Customers WHERE Region = '" & strRegion & "' ORDER BY City",objConnection,3,3
            if objRS.eof then
                  Response.Write("<option>No Cities Found</option>")
            else
                  Response.Write("<option>Select City Now</option>" & VBCRLF )
                  do while not objRS.EOF
                        if objRS("City") = strCity then
                              strSelected = " Selected "
                        else
                              strSelected = ""
                        end if
                        Response.Write("<option" & strSelected & ">" & objRS("City") & "</option>" & VBCRLF )
                        objRS.MoveNext
                  loop
            end if
            objRS.Close
            set objRS=Nothing
      else
            Response.Write("<option>Select a Region First</option>")
      end if
end sub
%>

<SCRIPT LANGUAGE=javascript>

</SCRIPT>

</HEAD>
<BODY>
<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT name="Country" onChange="submitCountry()">
      <%call makeCountry%>
</SELECT><br>
<SELECT name="Region" onChange="submitRegion()">
      <%call makeRegion%>
</SELECT><br>
<SELECT name="City">
      <%call makeCity%>
</SELECT><br>

<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>
</FORM>
</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>

</HTML>

Fritz the Blank

filemanager

ASKER

Fritz the Blank: I am trying to modify the code on the site you posted to match the requirements of my assignment, but I'm using SQL Server, not Access, and the connection isn't working in VBScript as it was originally written in ASP. Is there a way to put an ASP exerpt into the VBScript code?

filemanager

ASKER

Oh, this is the code:

      set objRS=Server.CreateObject("ADODB.RecordSet")
     sProject = "SELECT * FROM Workgroup W, WorkgroupMember WM ORDER BY Name"
      objRS.open sProject,Session("ReportsDSN"),3,3

and this is the error message I'm getting:
Data source name not found and no default driver specified

But the old code:
     sProject = "SELECT * FROM Workgroup W, WorkgroupMember WM ORDER BY Name"
      objRS.open sProject,Session("ReportsDSN"),3,3

     dTimeout = LetTimeout(300, sProject)
      set objRS = oUtility.Execute(Session("ReportsDSN"), sProject, Array("Resourceid", adGuid, adParamInput, 0, sResourceid), Array("Resourceid", adGuid, adParamInput, 0, sResourceid))

worked fine!

fritz_the_blank

I don't follow what isgoing on here:

oUtility.Execute(Session("ReportsDSN"), sProject, Array("Resourceid", adGuid, adParamInput, 0, sResourceid), Array("Resourceid", adGuid, adParamInput, 0, sResourceid))

Fritz the Blank

filemanager

ASKER

Honestly, I don't really either, another programmer wrote it. It uses a DSN defined in the Global.asa, but alas it used to work!

Do you know of any other ways of executing an SQL statement?

ASKER CERTIFIED SOLUTION

fritz_the_blank

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fritz_the_blank

The code that I provided does do what is asked....

FtB