ASP/database problem

[Doo-Dah Man]

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!

 

[Bob Barrows [MVP]]

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


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 you
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/tips/xml.data.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/downloads1/listdemo.zip
(read the "Dynamic Listbox" description on this page:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear


The DHTML documentation can be found here:
http://msdn.microsoft.com/workshop/author/dhtml/overview/dhtml_overviews_entry.asp
and here:
http://msdn.microsoft.com/workshop/author/dhtml/reference/dhtml_reference_entry.asp


The XML documentation can be found here:
http://msdn.microsoft.com/library/en-us/xmlsdk/html/xmmscXMLOverview.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

 

[Doo-Dah Man]

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]"
<[email protected]> stood up, looked around, realized where he

 

[McKirahan]

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