Populate Dropdown List From Database In Classic ASP (Vbscript)

In this article we will be discussing a simple way to populate a dropdown list control in a Classic ASP web page. This tutorial is for beginners who have just started going through the legacy applications written in VBScript/Jscript for Classic ASP. This is also useful for people interested in COM Interop.

We will be using VBscript to code the Classic ASP page. The same objective can be achieved using JScript but with a different syntax.

Let's start with the steps.

Step 1

When the page is loaded for the first time we will be checking the already saved value in the database so that it can be displayed as the selected text in the dropdown.

Please refer to the code snippet below. I have used Product_id as a querystring parameter to search the database. Also I have declared a function to set the selected text of dropdown.

<%
'Get the value of parameter from querystring
Product_ID = Request.Querystring("ProductID")
'Declare a variable to store the connection string
Dim connstr
connstr = "Provide your connection string"
'Create a new ADODB recordset
set rsSelectedProduct = Server.CreateObject("ADODB.recordset")
'Create a new ADODB connection
Set conn = Server.CreateObject("ADODB.Connection")
'Open the connection using the connection string
conn.open connstr
'Store the query in a variable , in real world scenario we should be using a stored procedure with parameters
QuerySQL = "Select Product_name from tbl_Products where Product_ID = '" & Product_ID & "'"
'Execute the query
set rsSelectedProduct = conn.Execute(QuerySQL)
'Check if recordset is empty , if not then store the value
if not rsSelectedProduct.EOF then
product = rsSelectedProduct("Product_name")
else
product = ""
end if
'Close the Connection and recordset
rsSelectedProduct.Close
conn.Close
Set rsSelectedProduct = Nothing
Set conn = Nothing
'Declare a function to set the selected text
Function selectProduct(vProduct)
if vProduct = product then
Response.Write("selected=""selected""")
end if
End Function
%>

Step 2

Inside the server side vbscript code Create a new ADODB connection, ADODB Recordset and open the connection using the required connection string.

Please refer to the code snippet below.

<%
'Create Connection object
Set conn2 = Server.CreateObject("ADODB.Connection")
'Create Recordset object
Set rsProductList = Server.CreateObject("ADODB.recordset")
'Open the connection using the previous connection string
conn2.open connstr
'Declare a variable to store the query to be excuted. Here we are using a hardcoded query.We can also use a stored procedure
QueryProduct = "select Product_Name,Product_ID from tbl_Products order by Product_Name asc"
%>

Step 3

Execute the query and populate the Recordset from the result. Then convert the recordset to a 2 dimensional array in Vbscript.

Refer to the code below.

<%
'Execute the query
set rsProductList = conn2.Execute(QueryProduct)
'Declare the array
Dim arrProducts
if not rsProductList.EOF then
arrProducts = rsProductList.GetRows() ' Convert recordset to 2D Array
end if
'Close the Connection and recordset
rsProductList.Close
conn2.Close
Set rsProductList = Nothing
Set conn2 = Nothing
%>

Step 4

Create the dropdown list tag inside the HTML <body> section of the page. Here I have used the 2D Array generated in step 3 above.

<!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=utf-8" />
<title>Populate Product dropdown</title>
</head>
<body>
<br/>
<label id="lblOne" for="ddlproduct" title="Select Product:" class="Example">Product:</label>
<select name="productSelect" id="productSelect">
<%
'Check whether it's a proper array or not
if IsArray(arrProducts) then
For i = 0 to ubound(arrProducts, 2) %>
<option value="<%= arrProducts(1,i)%>" <%= selectProduct(arrProducts(0,i)) %>> <%= arrProducts(0,i) %> </option>
<% next %>
<% else %>
<option value=""> Select </option>
<% end if %>
</select>
</body>
</html>

Here is the Complete code for the page test.asp. If we host this page in IIS then we will be able to see the dropdown generated.

<%
'Get the value of parameter from querystring
Product_ID = Request.Querystring("ProductID")
'Declare a variable to store the connection string
Dim connstr
connstr = "Provide your connection string"
'Create a new ADODB recordset
set rsSelectedProduct = Server.CreateObject("ADODB.recordset")
'Create a new ADODB connection
Set conn = Server.CreateObject("ADODB.Connection")
'Open the connection using the connection string
conn.open connstr
'Store the query in a variable , in real world scenario we should be using a stored procedure with parameters
QuerySQL = "Select Product_name from tbl_Products where Product_ID = '" & Product_ID & "'"
'Execute the query
set rsSelectedProduct = conn.Execute(QuerySQL)
'Check if recordset is empty , if not then store the value
if not rsSelectedProduct.EOF then
product = rsSelectedProduct("Product_name")
else
product = ""
end if
'Close the Connection and recordset
rsSelectedProduct.Close
conn.Close
Set rsSelectedProduct = Nothing
Set conn = Nothing
'Declare a function to set the selected text
Function selectProduct(vProduct)
if vProduct = product then
Response.Write("selected=""selected""")
end if
End Function
'Create Connection object
Set conn2 = Server.CreateObject("ADODB.Connection")
'Create Recordset object
Set rsProductList = Server.CreateObject("ADODB.recordset")
'Open the connection using the previous connection string
conn2.open connstr
'Declare a variable to store the query to be excuted. Here we are using a hardcoded query.We can also use a stored procedure
QueryProduct = "select Product_Name,Product_ID from tbl_Products order by Product_Name asc"
'Execute the query
set rsProductList = conn2.Execute(QueryProduct)
'Declare the array
Dim arrProducts
if not rsProductList.EOF then
arrProducts = rsProductList.GetRows() ' Convert recordset to 2D Array
end if
'Close the Connection and recordset
rsProductList.Close
conn2.Close
Set rsProductList = Nothing
Set conn2 = Nothing
%>
<!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=utf-8" />
<title>Populate Product dropdown</title>
</head>
<body>
<br/>
<label id="lblOne" for="ddlproduct" title="Select Product:" class="Example">Product:</label>
<select name="productSelect" id="productSelect">
<%
'Check whether it's a proper array or not
if IsArray(arrProducts) then
For i = 0 to ubound(arrProducts, 2) %>
<option value="<%= arrProducts(1,i)%>" <%= selectProduct(arrProducts(0,i)) %>> <%= arrProducts(0,i) %> </option>
<% next %>
<% else %>
<option value=""> Select </option>
<% end if %>
</select>
</body>
</html>

I hope this will be useful for beginners who have just started going through legacy ASP code. Comments and feedback are always welcome.Thank you.