p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: fill next combo with SQL filter???


Message #1 by wswyght@t... on Fri, 14 Jul 2000 19:33:45 GMT
I have three drop down boxes that are populated from a database, I want the second

and third boxes to be sorted using SQL according to the previously picked option.

Therefore if combo1.option is Ford then combo2.options[*] will only have ford

models in them. I know how to do the SQL.



I cannot find away to do an onClick event for the combo boxes that is recognized

by ASPScript or VBScript. I can use JavaScript and bring the correct values

into <INPUT="HIDDEN"> tags but can not then automatically fire an VBScript Sub

or an ASP Sub. 



I need to make a ASP Sub fire that I can use the ADO Record Set connection work

that I have on my site. 



Is there anyway to make ASP recognize an onClick so the system does not think

it must be a JavaScript function. I cannot ever call a ASP Sub inside a JavaScript

function. 



Please any help is really needed on this. I have spent two days and am nowhere.





Warren S. Wyght

Message #2 by fredrik.normen@s... on Sun, 16 Jul 2000 11:41:43
You can use use the onChange event on the listbox.



If you need to call a ASP sub you have to reload the page because it's 

server side. But to call a VBScript function or JavaScript it's only to  

call the function name.



Or:



<SCRIPT LANGUAGE="VbScript">

function button1_onCLick()

 <! -- your code here -->

end function

</SCRIPT>



this will automaitcly call this function if you press the button with the 

name button1. Try if you can use something like this.



In ASP+ plus it's more simple to call server side function because you have 

the nye onserver... events.



/Fredrik Normén

Message #3 by "Ken Schaefer" <ken.s@a...> on Sun, 16 Jul 2000 18:19:40 +1000
ASP is all executed PRIOR to the page being sent to the client.



Once the page is on the client you can't called a server-side sub.



If you're developing for IE, you could use disconnected recordsets.



Otherwise you need to put all the info into an array, and have another array

which you can put these elements into in the required order (link arrays I

think they are called - look in your programming text), and display the link

array.



Cheers

Ken





----- Original Message -----

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, July 15, 2000 5:33 AM

Subject: [asp_databases] fill next combo with SQL filter???





> I have three drop down boxes that are populated from a database, I want

the second

> and third boxes to be sorted using SQL according to the previously picked

option.

> Therefore if combo1.option is Ford then combo2.options[*] will only have

ford

> models in them. I know how to do the SQL.

>

> I cannot find away to do an onClick event for the combo boxes that is

recognized

> by ASPScript or VBScript. I can use JavaScript and bring the correct

values

> into <INPUT="HIDDEN"> tags but can not then automatically fire an VBScript

Sub

> or an ASP Sub.

>

> I need to make a ASP Sub fire that I can use the ADO Record Set connection

work

> that I have on my site.

>

> Is there anyway to make ASP recognize an onClick so the system does not

think

> it must be a JavaScript function. I cannot ever call a ASP Sub inside a

JavaScript

> function.

>

> Please any help is really needed on this. I have spent two days and am

nowhere.

>

>

> Warren S. Wyght





Message #4 by "Van Schaijk, Marco" <marco.vanschaijk@e...> on Tue, 18 Jul 2000 08:56:27 +0100
Warren,



I recently had the same problem and I solved it. I only used two selection

lists. A change in the first (LineOfBusiness) results in another list in the

second (Account). And all this totally on the client!

This code displays all Line Of Businesses (LOB) in the first selection list.

All accounts (ACC) from the selected LOB in the second.



<%

'*** Open SQL for all LOB's

strSql = "SELECT DISTINCT LOB_ID, LOB_Name " &_

         "  FROM LOB_Details s

	   " ORDER BY LOB_Name"



	.... Create Recordset objRSLOB from strSql ....

%>

<table>

  <tr>

    <td>Line Of Business:

    <td>

<%

'*** For each LOB create a part of the onChange to call its own ShowLob#

function 

objRSLOB.MoveFirst

DO UNTIL objRSLOB.Eof

    sOnChange=sOnChange &_ 

         " if (document.form1.LineOfBusiness.value ==

"""&objRSLOB("LOB_ID")&""") " &_

         " {ShowLOB"&objRSLOB("LOB_ID")&"();}; " & chr(13)

    objRSLOB.MoveNext

LOOP

%>

      <select name="LineOfBusiness" size="1" id="7"

onChange='<%=sOnChange%>'>

<%

'*** Fill selection list with all LOB's

objRSLOB.MoveFirst

DO UNTIL objRSLOB.Eof

%>

        <OPTION

value="<%=objRSLOB("LOB_ID")%>"><%=objRSLOB("LOB_Name")%></OPTION>

<%

    objRSLOB.MoveNext

LOOP

%>

      </select>

  <tr>

    <td>Organisation:

    <td>



<!-- *** Create empty selection list for ACC. 

     *** Use style="width:???" to avoid width-adjustments in selection-list

-->



      <select name="Account" size="1" id="Account" style="width:350" >

        <OPTION value=""></OPTION>

      </select>

<%

' *** Make functions ShowLob# for each LOB to display its own ACC's

objRSLOB.MoveFirst

DO UNTIL objRSLOB.Eof

	strSql = "SELECT l.LOB_Name+' - '+a.ACC_Name, a.ACC_ID AS TheName "

&_

		     "  FROM LOB_Details l, ACC_Details a " &_

			 " WHERE a.LOB_ID = l.LOB_ID " &_

			 "   AND l.LOB_ID = " & objRSLOB("LOB_ID") &_

			 " ORDER BY TheName"



	.... Create Recordset objRSACC from strSql ....



	sFunctions = sFunctions & chr(13) & "function

ShowLOB"&objRSLOB("LOB_ID")&" () {" &_

	                                    " document.form1.Account.length

= 0; " 

	i = 0

	Do Until objRSACC.Eof

		sFunctions = sFunctions & " document.form1.Account.length="

& i + 1 &";" &_

			       " document.form1.Account.options[" & i &

"].text  = '" & 

			       objRSACC("TheName") & "';" & chr(13) &_

  			       " document.form1.Account.options[" & i &

"].value = '" &_ 

			       objRSACC("ACC_ID") & "';" & chr(13) 	

		If i = 0 Then sSelectFirst = "document.form1.Account.value 

'" &_ 

			                       objRSACC("TheName") & "';"

		i = i + 1

		objRSACC.MoveNext

	Loop

	sFunctions = sFunctions & sSelectFirst & " } " & chr(13)



	objRSACC.Close

	set objRSACC = nothing

	objRSLOB.MoveNext

LOOP

%>



</table>

<%

' *** show accounts or first LOB

  objRSLOB.MoveFirst

  sFunctions = sFunctions & "ShowLOB"&objRSLOB("LOB_ID")&"();"

%>



<!-- Write all ShowLOB functions as Client Script -->

<SCRIPT>

	<%=sFunctions%>

</SCRIPT>	





I hope this helps. Please let me know. (Also when you have questions).



Marco van Schaijk

Systems Engineer

EDS

Netherlands Solution Centre (NLSC)

GUI Competence Centre

E-mail:	Marco.vanSchaijk@e...





----- Original Message -----

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, July 15, 2000 5:33 AM

Subject: [asp_databases] fill next combo with SQL filter???





> I have three drop down boxes that are populated from a database, I want

the second

> and third boxes to be sorted using SQL according to the previously picked

option.

> Therefore if combo1.option is Ford then combo2.options[*] will only have

ford

> models in them. I know how to do the SQL.

>

> I cannot find away to do an onClick event for the combo boxes that is

recognized

> by ASPScript or VBScript. I can use JavaScript and bring the correct

values

> into <INPUT="HIDDEN"> tags but can not then automatically fire an VBScript

Sub

> or an ASP Sub.

>

> I need to make a ASP Sub fire that I can use the ADO Record Set connection

work

> that I have on my site.

>

> Is there anyway to make ASP recognize an onClick so the system does not

think

> it must be a JavaScript function. I cannot ever call a ASP Sub inside a

JavaScript

> function.

>

> Please any help is really needed on this. I have spent two days and am

nowhere.

>

>

> Warren S. Wyght





Message #5 by "Warren S. Wyght" <wswyght@t...> on Mon, 17 Jul 2000 19:32:37 -0600
Ken;



Holy Cow Ken that is a really good idea, what the heck do you do for a living

think!!!!

I know what a parallel array is and I should have thought of it but I did not

even come close to this thought. Thanks for your help it is a life safer.



Warren



Ken Schaefer wrote:



> ASP is all executed PRIOR to the page being sent to the client.

>

> Once the page is on the client you can't called a server-side sub.

>

> If you're developing for IE, you could use disconnected recordsets.

>

> Otherwise you need to put all the info into an array, and have another array

> which you can put these elements into in the required order (link arrays I

> think they are called - look in your programming text), and display the link

> array.

>

> Cheers

> Ken

>

> ----- Original Message -----

> To: "ASP Databases" <asp_databases@p...>

> Sent: Saturday, July 15, 2000 5:33 AM

> Subject: [asp_databases] fill next combo with SQL filter???

>

> > I have three drop down boxes that are populated from a database, I want

> the second

> > and third boxes to be sorted using SQL according to the previously picked

> option.

> > Therefore if combo1.option is Ford then combo2.options[*] will only have

> ford

> > models in them. I know how to do the SQL.

> >

> > I cannot find away to do an onClick event for the combo boxes that is

> recognized

> > by ASPScript or VBScript. I can use JavaScript and bring the correct

> values

> > into <INPUT="HIDDEN"> tags but can not then automatically fire an VBScript

> Sub

> > or an ASP Sub.

> >

> > I need to make a ASP Sub fire that I can use the ADO Record Set connection

> work

> > that I have on my site.

> >

> > Is there anyway to make ASP recognize an onClick so the system does not

> think

> > it must be a JavaScript function. I cannot ever call a ASP Sub inside a

> JavaScript

> > function.

> >

> > Please any help is really needed on this. I have spent two days and am

> nowhere.

> >

> >

> > Warren S. Wyght

>

> ---

> ---

> You are currently subscribed to asp_databases




--

Those who make peaceful revolution impossible will make violent revolution

inevitable

        JFK Speech at white House 13 March 62

        vital Speeches 1 April 1962 Page 356



http://www.lethbridgec.ab.ca/~wswyght/index.html









Message #6 by "Warren S. Wyght" <wswyght@t...> on Mon, 17 Jul 2000 19:34:32 -0600
I will have to look into ASP+ but I am still trying to learn ASP.



Thanks for you help

Warren



fredrik.normen@s... wrote:



> You can use use the onChange event on the listbox.

>

> If you need to call a ASP sub you have to reload the page because it's

> server side. But to call a VBScript function or JavaScript it's only to

> call the function name.

>

> Or:

>

> <SCRIPT LANGUAGE="VbScript">

> function button1_onCLick()

>  <! -- your code here -->

> end function

> </SCRIPT>

>

> this will automaitcly call this function if you press the button with the

> name button1. Try if you can use something like this.

>

> In ASP+ plus it's more simple to call server side function because you have

> the nye onserver... events.

>

> /Fredrik Normén

>

> ---

> ---

> You are currently subscribed to asp_databases




--

Those who make peaceful revolution impossible will make violent revolution

inevitable

        JFK Speech at white House 13 March 62

        vital Speeches 1 April 1962 Page 356



http://www.lethbridgec.ab.ca/~wswyght/index.html









Message #7 by "Warren S. Wyght" <wswyght@t...> on Wed, 19 Jul 2000 18:50:57 -0600
Howdy Marco;



Thank you very much for this bit of code I will be trying it out tomorrow when I

am back at the office.



Thanks again, this looks very promising.



Warren



"Van Schaijk, Marco" wrote:



> Warren,

>

> I recently had the same problem and I solved it. I only used two selection

> lists. A change in the first (LineOfBusiness) results in another list in the

> second (Account). And all this totally on the client!

> This code displays all Line Of Businesses (LOB) in the first selection list.

> All accounts (ACC) from the selected LOB in the second.

>

> <%

> '*** Open SQL for all LOB's

> strSql = "SELECT DISTINCT LOB_ID, LOB_Name " &_

>          "  FROM LOB_Details s

>            " ORDER BY LOB_Name"

>

>         .... Create Recordset objRSLOB from strSql ....

> %>

> <table>

>   <tr>

>     <td>Line Of Business:

>     <td>

> <%

> '*** For each LOB create a part of the onChange to call its own ShowLob#

> function

> objRSLOB.MoveFirst

> DO UNTIL objRSLOB.Eof

>     sOnChange=sOnChange &_

>          " if (document.form1.LineOfBusiness.value ==

> """&objRSLOB("LOB_ID")&""") " &_

>          " {ShowLOB"&objRSLOB("LOB_ID")&"();}; " & chr(13)

>     objRSLOB.MoveNext

> LOOP

> %>

>       <select name="LineOfBusiness" size="1" id="7"

> onChange='<%=sOnChange%>'>

> <%

> '*** Fill selection list with all LOB's

> objRSLOB.MoveFirst

> DO UNTIL objRSLOB.Eof

> %>

>         <OPTION

> value="<%=objRSLOB("LOB_ID")%>"><%=objRSLOB("LOB_Name")%></OPTION>

> <%

>     objRSLOB.MoveNext

> LOOP

> %>

>       </select>

>   <tr>

>     <td>Organisation:

>     <td>

>

> <!-- *** Create empty selection list for ACC.

>      *** Use style="width:???" to avoid width-adjustments in selection-list

> -->

>

>       <select name="Account" size="1" id="Account" style="width:350" >

>         <OPTION value=""></OPTION>

>       </select>

> <%

> ' *** Make functions ShowLob# for each LOB to display its own ACC's

> objRSLOB.MoveFirst

> DO UNTIL objRSLOB.Eof

>         strSql = "SELECT l.LOB_Name+' - '+a.ACC_Name, a.ACC_ID AS TheName "

> &_

>                      "  FROM LOB_Details l, ACC_Details a " &_

>                          " WHERE a.LOB_ID = l.LOB_ID " &_

>                          "   AND l.LOB_ID = " & objRSLOB("LOB_ID") &_

>                          " ORDER BY TheName"

>

>         .... Create Recordset objRSACC from strSql ....

>

>         sFunctions = sFunctions & chr(13) & "function

> ShowLOB"&objRSLOB("LOB_ID")&" () {" &_

>                                             " document.form1.Account.length

> = 0; "

>         i = 0

>         Do Until objRSACC.Eof

>                 sFunctions = sFunctions & " document.form1.Account.length="

> & i + 1 &";" &_

>                                " document.form1.Account.options[" & i &

> "].text  = '" &

>                                objRSACC("TheName") & "';" & chr(13) &_

>                                " document.form1.Account.options[" & i &

> "].value = '" &_

>                                objRSACC("ACC_ID") & "';" & chr(13)

>                 If i = 0 Then sSelectFirst = "document.form1.Account.value 

> '" &_

>                                                objRSACC("TheName") & "';"

>                 i = i + 1

>                 objRSACC.MoveNext

>         Loop

>         sFunctions = sFunctions & sSelectFirst & " } " & chr(13)

>

>         objRSACC.Close

>         set objRSACC = nothing

>         objRSLOB.MoveNext

> LOOP

> %>

>

> </table>

> <%

> ' *** show accounts or first LOB

>   objRSLOB.MoveFirst

>   sFunctions = sFunctions & "ShowLOB"&objRSLOB("LOB_ID")&"();"

> %>

>

> <!-- Write all ShowLOB functions as Client Script -->

> <SCRIPT>

>         <%=sFunctions%>

> </SCRIPT>

>

> I hope this helps. Please let me know. (Also when you have questions).

>

> Marco van Schaijk

> Systems Engineer

> EDS

> Netherlands Solution Centre (NLSC)

> GUI Competence Centre

> E-mail: Marco.vanSchaijk@e...

>

> ----- Original Message -----

> To: "ASP Databases" <asp_databases@p...>

> Sent: Saturday, July 15, 2000 5:33 AM

> Subject: [asp_databases] fill next combo with SQL filter???

>

> > I have three drop down boxes that are populated from a database, I want

> the second

> > and third boxes to be sorted using SQL according to the previously picked

> option.

> > Therefore if combo1.option is Ford then combo2.options[*] will only have

> ford

> > models in them. I know how to do the SQL.

> >

> > I cannot find away to do an onClick event for the combo boxes that is

> recognized

> > by ASPScript or VBScript. I can use JavaScript and bring the correct

> values

> > into <INPUT="HIDDEN"> tags but can not then automatically fire an VBScript

> Sub

> > or an ASP Sub.

> >

> > I need to make a ASP Sub fire that I can use the ADO Record Set connection

> work

> > that I have on my site.

> >

> > Is there anyway to make ASP recognize an onClick so the system does not

> think

> > it must be a JavaScript function. I cannot ever call a ASP Sub inside a

> JavaScript

> > function.

> >

> > Please any help is really needed on this. I have spent two days and am

> nowhere.

> >

> >

> > Warren S. Wyght

>

> ---

> ---

> You are currently subscribed to asp_databases




--

Those who make peaceful revolution impossible will make violent revolution

inevitable

        JFK Speech at white House 13 March 62

        vital Speeches 1 April 1962 Page 356



http://www.lethbridgec.ab.ca/~wswyght/index.html










  Return to Index