Link to home
Start Free TrialLog in
Avatar of Barry Cunney
Barry CunneyFlag for Ireland

asked on

<select> OnChange event

Hi Guys
I am creating an ASP form with 2 dropdown lists - a list of 'Clients' and a list of users.
I want the users dropdown list tobe populated only after a 'client' is selected.
I a trying to specify the 'OnChange' event for the <SELECT> specifying the function 'RetrieveUsers'
When I select a client the ASP page crashes. The message returned is 'RetrieveUsers' is undefined.
Maybe the syntax for the way I have specified OnChange is wrong or something????
I have posted the script below so as you can get a feel for what I am doing

Cheers

B Cunney

<SELECT NAME="CLIENT" OnChange=RetrieveUsers>
<%
Dim cn
Dim adoRSC
Dim stSQLC
Dim strClient
Dim intClientID

Set cn = Server.CreateObject("ADODB.Connection")
Set adoRSC = Server.CreateObject("ADODB.Recordset")

stSQLC = "Select [CUSTOMER_ID],[NAME] from CUSTOMERS WHERE [NAME] IS NOT NULL order by [NAME] asc"

'Open a connection; the string referes to the DSN
cn.ConnectionString = "DRIVER=SQL Server" &_
    ";SERVER=SOCKS" & _
    ";UID=sa" & _
    ";PWD=moses" & _
    ";DATABASE=FINSYS"
cn.Open

' Associate the Recordset with the open connection
adoRSC.ActiveConnection = cn
adoRSC.Open stSQLC,cn

adoRSC.MoveFirst
 
Do While Not adoRSC.EOF
   
  intClientID = Server.HTMLEncode(adoRSC.Fields("CUSTOMER_ID"))
  strClient = Server.HTMLEncode(Trim(adoRSC.Fields("NAME")))%>
 
  <OPTION VALUE ="<%=intClientID%>"><%=strClient%>
 
  <%adoRSC.MoveNext

Loop%>

</SELECT><span style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </span>
<SELECT id=select1 name="USER">

<%
Function RetrieveUsers()
Dim adoRSU
Dim stSQLU
Dim strUser
Dim intUserID

Set adoRSU = Server.CreateObject("ADODB.Recordset")

stSQLU = "Select [USER_ID],[USER_NAME] from USERS WHERE [USER_NAME] IS NOT NULL order by [USER_NAME] asc"

' Associate the Recordset with the open connection
adoRSU.ActiveConnection = cn
adoRSU.Open stSQLU,cn

adoRSU.MoveFirst
 
Do While Not adoRSU.EOF
   
  intUserID = Server.HTMLEncode(adoRSC.Fields("USER_ID"))
  strUser = Server.HTMLEncode(Trim(adoRSC.Fields("USER_NAME")))%>
 
  <OPTION VALUE ="<%=intUserID%>"><%=strUser%>
 
  <%adoRSC.MoveNext

Loop

End Function%>

</SELECT></h2>
Avatar of MCM
MCM

BCUNNEY, you're on the wrong track. RetreiveUsers is server side script, onChange fires on the client. try this instead:
ASKER CERTIFIED SOLUTION
Avatar of MCM
MCM

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Barry Cunney

ASKER

MCM
Is there a way that you can get a client event such as onChange to kick off a server side script
you see how it works? the onChange event submits the form back to the server, including ClientID. The server then realizes that a clientID has been identified, and only at that point does it generate output a list of users. you can't just call a server side script from the client;

this is obvious when you understand how ASP works, but for me as for every other developer who moved to ASP from some other medium, its a hard lesson to learn at first.

oops --

i see I wrote this:

if len(Request("ClientID")>0 then

when i should have written this:

if len(Request("Client")>0 then

the "CLIENT" here must correspond to the NAME of the SELECT that the user selected the client from.
there are ways, but they are not good. Remote Scripting is a microsoft technology that uses a java applet to run back to the server and get new information; i don't beleive any one really uses this tech and it is certainly not in fashion -- MS may not even produce/support it any more.

You can write your own ActiveX control or java applet that will retrieve information from the server, but like remote scripting this is not really using ASP. under the ASP engine, what happens is:

-Server process server side scripts
-Server sends html to client, with no server side code included
-Client renders HTML, processes client side scripts.
-User does something that makes the client send an HTTP request back to the server for a whole other page

you don't get to instruct the client to send HTTP requests to your server for anything but a whole page at a time. retrieveUsers() is a function call, and HTTP doesn't know anything about function calls. it knows about page requests. web browsers make HTTP requests for pages, so getting them to get different kinds of data is very tricky, and always is working around the design of what browsers are designed to do. if you make your own activex/java solution, you are making a different client that interacts with you server in a different way than the browser.



hope that was clear, my typing and thinking both seem a little defective today.

an option you can consider is to send all userid/name information to the browser in a javascript script. when the user clicks on "client", you run a client-side script that picks up the correct elements of the array, and dynamically loads them into the select. i could show you such a script, but i have to run; perhaps someone else will oblige if you are interested in such a thing. it won't work on all browsers, but it will work on most. but don't confuse this with the server and client interacting; you are just supplying the client with all possible answers to the user's question; when the user makes a selection, the client already has a list of users ready to supply. pages can get very big this way, if you have a ton of users, or if you want to go more than SELECT deep.
Thanks a million for all the feedback MCM - I am now 80% there using your initial solution - just had to twig a few things(I am new to ASP so still not up to speed with all te concepts)

My dropdown list is working now but when it submits new browser window opens with the Client dropdown list reset to the first client but the users dropdown list is populated correctly showing only users for selected client.

How can I just get current page to refresh/resubmit not in new browser window and maintain the 'client' that was initially selected in client dropdown list.

Am I specifying the form target correctly

My script now looks like this:
*******************************************************
<form target="UserAppEntDev.asp"></o:p></o:p></SPAN></U>
<H1><U><SPAN style="COLOR: #ccffcc"></SPAN></U>&nbsp;</H1>
<H1><U><SPAN style="COLOR: #ccffcc">Specify Client/User Search Criteria<o:p></o:p></SPAN></U></H1>

<p class=MsoNormal><![if !supportEmptyParas]><![endif]>&nbsp;<o:p></o:p></p>

<h2>Client<span style="mso-spacerun: yes">&nbsp;&nbsp; </span><span style="mso-spacerun:
 yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="mso-spacerun:
 yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>User</h2>

<h2><SELECT NAME="CLIENT" OnChange="this.form.submit()">
<%
Dim cn
Dim adoRSC
Dim stSQLC
Dim strClient
Dim intClientID

Set cn = Server.CreateObject("ADODB.Connection")
Set adoRSC = Server.CreateObject("ADODB.Recordset")

stSQLC = "Select [CUSTOMER_ID],[NAME] from CUSTOMERS WHERE [NAME] IS NOT NULL order by [NAME] asc"

'Open a connection; the string referes to the DSN
cn.ConnectionString = "DRIVER=SQL Server" &_
    ";SERVER=SOCKS" & _
    ";UID=sa" & _
    ";PWD=moses" & _
    ";DATABASE=FINSYS"
cn.Open

' Associate the Recordset with the open connection
adoRSC.ActiveConnection = cn
adoRSC.Open stSQLC,cn

adoRSC.MoveFirst
 
Do While Not adoRSC.EOF
   
  intClientID = Server.HTMLEncode(adoRSC.Fields("CUSTOMER_ID"))
  strClient = Server.HTMLEncode(Trim(adoRSC.Fields("NAME")))%>
 
  <OPTION VALUE ="<%=intClientID%>"><%=strClient%>
 
  <%adoRSC.MoveNext

Loop%>

</SELECT><span style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp; </span>

<SELECT id=select1 name="USER">

<% if len(Request("CLIENT"))>0 then
    RetrieveUsers(Request("CLIENT"))%>
    </SELECT></h2>
<%end if%>
</FORM>

<%
Function RetrieveUsers(intClientID)
Dim adoRSU
Dim stSQLU
Dim strUser
Dim intUserID

Set adoRSU = Server.CreateObject("ADODB.Recordset")

stSQLU = "Select [USER_ID],[USER_NAME] from USERS WHERE [USER_NAME] IS NOT NULL AND [CUSTOMER_ID] = " & intClientID & " order by [USER_NAME] asc"

' Associate the Recordset with the open connection
adoRSU.ActiveConnection = cn
adoRSU.Open stSQLU,cn

adoRSU.MoveFirst
 
Do While Not adoRSU.EOF
   
  intUserID = Server.HTMLEncode(adoRSU.Fields("USER_ID"))
  strUser = Server.HTMLEncode(Trim(adoRSU.Fields("USER_NAME")))%>
 
  <OPTION VALUE ="<%=intUserID%>"><%=strUser%>
 
  <%adoRSU.MoveNext

Loop

End Function%>
***********************************************************
Yes my thinking etc. is a bit down today as well MCM - socialising till late last night - not firing an all cylinders today

Yes my thinking etc. is a bit down today as well MCM - socialising till late last night - not firing an all cylinders today

Hi MCM
The reason it was opening in new window was because of the target parameter - I did not fully understand this setting.
<FORM target="UserAppEntDev.asp"

I just removed this target setting and now ASP page works just simply refreshing current page and showing only users for selected client.

The only other thing I have to do is to maintain client list box at the client that was initially selected because when you refresh it resets to the first entry.
If you have any ideas for this it would be appreciated

Cheers


B Cunney


Cheers
MCM
howdy --
i was off duty this afternoon, sorry to abandon you. good job removing target; i know it's counterintuitive, but what you really want is "ACTION="[current page name]" and it is safer to specify it than to omit it.


when you loop through your list, which goes in your original code:

<%
Do While Not adoRSC.EOF
 
intClientID = Server.HTMLEncode(adoRSC.Fields("CUSTOMER_ID"))
strClient = Server.HTMLEncode(Trim(adoRSC.Fields("NAME")))%>

<OPTION VALUE ="<%=intClientID%>"><%=strClient%>

<%adoRSC.MoveNext

Loop
%>

draw the option value thusly:

if intClientID=Request("CLIENT") then
    <OPTION VALUE ="<%=intClientID%>" SELECTED><%=strClient%>
else
    <OPTION VALUE ="<%=intClientID%>" ><%=strClient%>
end if

and you will see the originaly choice pre-selected in your SELECT. good luck.



Thanks a million for this last piece of code MCM has worked a dream - I have change the <FORM ACTION to current page as well.


Cheers


B Cunney