So now we need to call the function. There is a wealth of information on database connectivity techniques dotted all over the Web, but for the sake of simplicity we’ll assume we’ve got an MS Access database called "shop" stored in the same folder as the script. This is sufficient for initial learning but it is worth taking look at the alternatives available to you for your live site and the security implications of the options.
Here is the complete script. Simply paste this into an ASP file – and to test you’ll need that copy of the database as described above.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<form>
<%
' define the database connection settings
strconnect="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("shop.mdb")
' open a connection object and actually connect to the database
set dbconn=server.createobject("adodb.connection")
dbconn.open strconnect
call db_dropdown(dbconn, "tbl_fruit", "fruit_id", "fruit_name")
' close the db connection
dbconn.close
set dbconn=Nothing
%>
</form>
</body>
</html>
<%
function db_dropdown(dbconnection, tablename, bound_field, display_field)
' output the HTML code to initialise a drop-down box
response.write "<select name='"&bound_field&"' id='"&bound_field&"'>"
' open recordset connection
strSQL="SELECT " & bound_field & "," & display_field & " FROM " & tablename
set xrs=server.createobject("adodb.recordset")
xrs.open strSQL, dbconnection
do while not xrs.eof
' for each line record, write an option to the drop-down box
response.write "<option value='"&xrs(0)&"'>"& xrs(1)&"</option>"
xrs.movenext
loop
'clear up the connections
xrs.close
set xrs=Nothing
' output the HTML code to close the drop-down box
response.write "</select><br />"
end function
%>
Now every time that you need a drop-down box you simply have to call this function "db_dropdown". You may choose to place the Database Connection object inside this function or even another function. There are many ways this function can be developed further and that is the great benefit of the concept. The code may do exactly what you need it to do now, but in six months time you may decide that the results need to be sorted in alphabetical order, or that you want to add a default value to the function. With a bit of thinking you’ll be able to achieve this with an absolute minimum of work.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 257 
