ASP/database problem

I hope I can make this clear:


I have an Access 2000 database that drives an ASP web site to track
sales leads.
There is a combo box , "units", that lists the inventory of models we
sell.  Here's a code snippet

Fills the combo box:

<select name="units" id="units">


<%
While (NOT leads1.EOF)
%>
<option><%=(leads1.Fields.Item("units").Value)%></option>
<%
leads1.MoveNext()
Wend
If (leads1.CursorType > 0) Then
leads1.MoveFirst
Else
leads1.Requery
End If

%>

This code works perfectly to fill the combobox with the model names
from the units table of the units db.  Leads1 is the recordset that is
connected to the db

The 'units' table has three fields:

ID (autonumber - PK)
units - Text field
AnnValue - currency field

The form also has a readonly textbox, "AnnVal" to display the Annual
cost of the service contract for that unit.

Displays textbox:

<td valign="top"><input name="AnnVal" type="text" id="AnnVal"
size="15" readonly>
</td>


I want to populate that textbox with the data in the AnnValue field of
the units db.  Based on the choice the user makes in the combobox, I
want the corresponding price to populate the read-only textbox.

I have seen several solutions that employ some Javascript with ASP to
get this to work but I have no idea how to implement these
suggestions.  Being a real newbie, can someone offer some nicely
commented code to help me make this work the way I have described?

Thank you so much.  You folks have always been so helpful!

Doo-Dah Man wrote:
> I hope I can make this clear:
>
>
> I have an Access 2000 database that drives an ASP web site to track
> sales leads.
> There is a combo box , "units", that lists the inventory of models we
> sell.  Here's a code snippet
>
> Fills the combo box:
>
> <select name="units" id="units">
>
>
> <%
> While (NOT leads1.EOF)
> %>
>          <option><%=(leads1.Fields.Item("units").Value)%></option>
>          <%
>  leads1.MoveNext()
> Wend
> If (leads1.CursorType > 0) Then
>  leads1.MoveFirst
> Else
>  leads1.Requery

<gasp> Ughhhhh!


>
> This code works perfectly to fill the combobox with the model names
> from the units table of the units db.

Maybe so. But .... ughhhh!

>  Leads1 is the recordset that is
> connected to the db
>
> The 'units' table has three fields:
>
> ID (autonumber - PK)
> units - Text field
> AnnValue - currency field
>
> The form also has a readonly textbox, "AnnVal" to display the Annual
> cost of the service contract for that unit.
>
> Displays textbox:
>
> <td valign="top"><input name="AnnVal" type="text" id="AnnVal"
> size="15" readonly>
>      </td>
>
>
> I want to populate that textbox with the data in the AnnValue field of
> the units db.  Based on the choice the user makes in the combobox, I
> want the corresponding price to populate the read-only textbox.
>
> I have seen several solutions that employ some Javascript with ASP to
> get this to work but I have no idea how to implement these
> suggestions.  Being a real newbie, can someone offer some nicely
> commented code to help me make this work the way I have described?
>
> Thank you so much.  You folks have always been so helpful!

You really need to follow up in a client-side scripting newsgroup (such as
.scripting.jscript). This is very much off-topic in this newsgroup, once yo
u
get part the issue of getting the lookup data into your clientside document.
The answer you need depends on how much cross-browser compatability you
need.

I would use an xml document to accomplish this. Instead of your slow
recordset loop and <gasp> requery, use GetRows to quickly pull your
recordset data into an array. Then close your recordset and connection, and
use the array to handle your data. (it's a simple for loop to loop through
the array to create your options for your dropdown box ... there is no such
thing as a "combo" in html).

You can also create an xml document by looping through the array. I have a
demo for doing this located here (since it uses an xml data island, this is
an IE-only demo - you can modify it with help from the client-side newsgroup
to make it more cross-browser compatible):

http://www.davidpenton.com/testsite...ata.islands.asp


Once you have the data in a client-side xml document, you are in the realm
of client-side scripting (DHTML). Basically, you need to use the select
element's onchange event, creating a function that uses the select's value
to search through the cleint-side xml document (the data island can be
treated as an xml document) using the selectSingleNode method to find the
node containing the data you wat to put into the textbox.

I have another IE-only demo which can be found here - it's much more complex
than what you will need since it handles keystrokes instead of a selection
of a select element's option, but the basic ideas can be found in it:

http://www.thrasherwebdesign.com/do...s1/listdemo.zip
(read the "Dynamic Listbox" description on this page:
http://www.thrasherwebdesign.com/in....asp&c=&a=clear


The DHTML documentation can be found here:
http://msdn.microsoft.com/workshop/...try.asp

and here:
http://msdn.microsoft.com/workshop/...try.as
p


The XML documentation can be found here:
http://msdn.microsoft.com/library/e...XMLOverview.asp


There are other solutions to be found at www.aspfaq.com.

This is truly a FAQ - you could have avoided this post by using Google.


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I spent a lot of time searching Google and never found what I was
looking for.  Thanks for responding.  Not sure if I completely
understand what you're saying here, but I will read through all the
links.




On Tue, 29 Mar 2005 06:56:56 -0500, "Bob Barrows [MVP]"
<reb01501@NOyahoo.SPAMcom> stood up, looked around, realized where he
was, and wrote:

>Doo-Dah Man wrote: 
>
><gasp> Ughhhhh!
>
> 
>
>Maybe so. But .... ughhhh!
> 
>
>You really need to follow up in a client-side scripting newsgroup (such as
>.scripting.jscript). This is very much off-topic in this newsgroup, once yo
u
>get part the issue of getting the lookup data into your clientside document
.
>The answer you need depends on how much cross-browser compatability you
>need.
>
>I would use an xml document to accomplish this. Instead of your slow
>recordset loop and <gasp> requery, use GetRows to quickly pull your
>recordset data into an array. Then close your recordset and connection, and
>use the array to handle your data. (it's a simple for loop to loop through
>the array to create your options for your dropdown box ... there is no such
>thing as a "combo" in html).
>
>You can also create an xml document by looping through the array. I have a
>demo for doing this located here (since it uses an xml data island, this is
>an IE-only demo - you can modify it with help from the client-side newsgrou
p
>to make it more cross-browser compatible):
>
>http://www.davidpenton.com/testsite...ata.islands.asp
>
>
>Once you have the data in a client-side xml document, you are in the realm
>of client-side scripting (DHTML). Basically, you need to use the select
>element's onchange event, creating a function that uses the select's value
>to search through the cleint-side xml document (the data island can be
>treated as an xml document) using the selectSingleNode method to find the
>node containing the data you wat to put into the textbox.
>
>I have another IE-only demo which can be found here - it's much more comple
x
>than what you will need since it handles keystrokes instead of a selection
>of a select element's option, but the basic ideas can be found in it:
>
>http://www.thrasherwebdesign.com/do...s1/listdemo.zip
>(read the "Dynamic Listbox" description on this page:
>http://www.thrasherwebdesign.com/in....asp&c=&a=clear
>
>
>The DHTML documentation can be found here:
>http://msdn.microsoft.com/workshop/...try.as
p
>and here:
>http://msdn.microsoft.com/workshop/...try.a
sp
>
>
>The XML documentation can be found here:
>http://msdn.microsoft.com/library/e...XMLOverview.asp
>
>
>There are other solutions to be found at www.aspfaq.com.
>
>This is truly a FAQ - you could have avoided this post by using Google.
>
>
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my spam trap so I
>don't check it very often. If you must reply off-line, then remove the
>"NO SPAM"
>

"Doo-Dah Man" <yeah@right.com> wrote in message
 news:0uqh419e85v3vqbj7qb8rhkqud5ss51rb7@
4ax.com...
> I hope I can make this clear:
>
>
> I have an Access 2000 database that drives an ASP web site to track
> sales leads.
> There is a combo box , "units", that lists the inventory of models we
> sell.  Here's a code snippet
>
> Fills the combo box:
>
> <select name="units" id="units">
>
>
> <%
> While (NOT leads1.EOF)
> %>
>           <option><%=(leads1.Fields.Item("units").Value)%></option>
>           <%
>   leads1.MoveNext()
> Wend
> If (leads1.CursorType > 0) Then
>   leads1.MoveFirst
> Else
>   leads1.Requery
> End If
>
> %>
>
> This code works perfectly to fill the combobox with the model names
> from the units table of the units db.  Leads1 is the recordset that is
> connected to the db
>
> The 'units' table has three fields:
>
> ID (autonumber - PK)
> units - Text field
> AnnValue - currency field
>
> The form also has a readonly textbox, "AnnVal" to display the Annual
> cost of the service contract for that unit.
>
> Displays textbox:
>
> <td valign="top"><input name="AnnVal" type="text" id="AnnVal"
> size="15" readonly>
>       </td>
>
>
> I want to populate that textbox with the data in the AnnValue field of
> the units db.  Based on the choice the user makes in the combobox, I
> want the corresponding price to populate the read-only textbox.
>
> I have seen several solutions that employ some Javascript with ASP to
> get this to work but I have no idea how to implement these
> suggestions.  Being a real newbie, can someone offer some nicely
> commented code to help me make this work the way I have described?
>
> Thank you so much.  You folks have always been so helpful!

Perhaps this is what you want:


<input name="AnnVal" type="text" id="AnnVal" size="15" readonly>

<select name="units" id="units"

onchange="document.forms[0].AnnVal.value=document.forms[0].options[document.
forms[0].selectedIndex].value">
<%  While Not leads1.EOF %>
<option
value="<%=leads1("AnnValue").Value%>"><%=leads1("units").Value%></option>
<%      leads1.MoveNext()
Wend
If (leads1.CursorType > 0) Then
leads1.MoveFirst
Else
leads1.Requery
End If
%>