Populating an HTML list box from Access table

[furtivevole]

(With apolgies for duplication, I've also posted this to one of the Access forums, but re-post it here because of the vbScript content)

I have taken over maintenance of someone else's .asp pages. These use vbScript (new to me), and there are quite a few other unknowns which makes it trickier/riskier to try things out than it would usually be.

One page is a web-based form, from which (among other things) the user has to select a Customer via a list box. Currently this is from a hard-coded list (i.e. with a series of <option>cust_name</option> tags). The user cannot create a new customer on the fly, so a combo box is not required.

The requirement is instead to read the customer list dynamically from an Access table.

I'm stuck on how best to do this. For instance, using vbScript, if I read the table into an array (declared as public), could I then use this to populate the HTML list box - or is there a more elegant method? Depends presumably on the scope of the array variable outside the vbScript code.

Any ideas please?

Linnet

 

[link9]

Sounds like an ASP solution is what's needed:

here's a quick and dirty example of how you might create a list box filled with values from a table called 'myTable', and the two columns in this table would be 'text', and 'value'.

<%
dim con, rs
set con = server.createObject (&quot;ADODB.Connection&quot
set rs = server.createObject (&quot;ADODB.Recordset&quot

con.open (&quot;DSN=myDSN&quot
rs.open &quot;SELECT text, value FROM myTable&quot;, con

with response
.write(&quot;<select name=mySelect>&quot; & vbcr)
.write(&quot;<option value=none>SELECT ONE</option>&quot; & vbcr)
while not rs.eof
.write(&quot;<option value=&quot; & rs(&quot;value&quot & &quot;>&quot
.write(rs(&quot;text&quot)
.write(&quot;</option>&quot; & vbcr)
rs.movenext
wend
.write(&quot;</select>&quot; & vbcr)
end with

set rs = nothing
set con = nothing
%>

the vbcr stuff will just put a line break in the resulting HTML so that it will be easier for you to read if there's a mistake somewhere, but that's the basic gist of what has to be done.

hope that helps!
Paul Prewett