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.
  1. <%
  2. 'Get the value of parameter from querystring
  3. Product_ID = Request.Querystring("ProductID")
  5. 'Declare a variable to store the connection string
  6. Dim connstr
  7. connstr = "Provide your connection string"
  9. 'Create a new ADODB recordset
  10. set rsSelectedProduct = Server.CreateObject("ADODB.recordset")
  12. 'Create a new ADODB connection
  13. Set conn = Server.CreateObject("ADODB.Connection")
  15. 'Open the connection using the connection string
  16. conn.open connstr
  18. 'Store the query in a variable , in real world scenario we should be using a stored procedure with parameters
  19. QuerySQL = "Select Product_name from tbl_Products where Product_ID = '" & Product_ID & "'"
  21. 'Execute the query
  22. set rsSelectedProduct = conn.Execute(QuerySQL)
  24. 'Check if recordset is empty , if not then store the value
  25. if not rsSelectedProduct.EOF then
  26. product = rsSelectedProduct("Product_name")
  27. else
  28. product = ""
  29. end if
  31. 'Close the Connection and recordset
  33. rsSelectedProduct.Close
  34. conn.Close
  35. Set rsSelectedProduct = Nothing
  36. Set conn = Nothing

  37. 'Declare a function to set the selected text
  38. Function selectProduct(vProduct)
  39. if vProduct = product then
  40. Response.Write("selected=""selected""")
  41. end if
  42. End Function

  44. %>
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.
  1. <%
  2. 'Create Connection object
  3. Set conn2 = Server.CreateObject("ADODB.Connection")
  4. 'Create Recordset object
  5. Set rsProductList = Server.CreateObject("ADODB.recordset")
  7. 'Open the connection using the previous connection string
  8. conn2.open connstr
  10. 'Declare a variable to store the query to be excuted. Here we are using a hardcoded query.We can also use a stored procedure
  11. QueryProduct = "select Product_Name,Product_ID from tbl_Products order by Product_Name asc"
  12. %>
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.
  1. <%
  2. 'Execute the query
  3. set rsProductList = conn2.Execute(QueryProduct)
  4. 'Declare the array
  5. Dim arrProducts
  7. if not rsProductList.EOF then
  8. arrProducts = rsProductList.GetRows() ' Convert recordset to 2D Array
  9. end if
  10. 'Close the Connection and recordset
  12. rsProductList.Close
  13. conn2.Close
  14. Set rsProductList = Nothing
  15. Set conn2 = Nothing
  17. %>
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.
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  2. <html xmlns="http://www.w3.org/1999/xhtml">
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5. <title>Populate Product dropdown</title>
  6. </head>
  7. <body>
  8. <br/>
  9. <label id="lblOne" for="ddlproduct" title="Select Product:" class="Example">Product:</label>
  10. <select name="productSelect" id="productSelect">
  11. <%
  12. 'Check whether it's a proper array or not
  13. if IsArray(arrProducts) then
  15. For i = 0 to ubound(arrProducts, 2) %>
  17. <option value="<%= arrProducts(1,i)%>" <%= selectProduct(arrProducts(0,i)) %>> <%= arrProducts(0,i) %> </option>
  19. <% next %>
  21. <% else %>
  23. <option value=""> Select </option>
  25. <% end if %>
  27. </select>
  28. </body>
  29. </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.
  1. <%
  2. 'Get the value of parameter from querystring
  3. Product_ID = Request.Querystring("ProductID")
  5. 'Declare a variable to store the connection string
  6. Dim connstr
  7. connstr = "Provide your connection string"
  9. 'Create a new ADODB recordset
  10. set rsSelectedProduct = Server.CreateObject("ADODB.recordset")
  12. 'Create a new ADODB connection
  13. Set conn = Server.CreateObject("ADODB.Connection")
  15. 'Open the connection using the connection string
  16. conn.open connstr
  18. 'Store the query in a variable , in real world scenario we should be using a stored procedure with parameters
  19. QuerySQL = "Select Product_name from tbl_Products where Product_ID = '" & Product_ID & "'"
  21. 'Execute the query
  22. set rsSelectedProduct = conn.Execute(QuerySQL)
  24. 'Check if recordset is empty , if not then store the value
  25. if not rsSelectedProduct.EOF then
  26. product = rsSelectedProduct("Product_name")
  27. else
  28. product = ""
  29. end if
  31. 'Close the Connection and recordset
  33. rsSelectedProduct.Close
  34. conn.Close
  35. Set rsSelectedProduct = Nothing
  36. Set conn = Nothing
  38. 'Declare a function to set the selected text
  39. Function selectProduct(vProduct)
  40. if vProduct = product then
  41. Response.Write("selected=""selected""")
  42. end if
  43. End Function
  45. 'Create Connection object
  46. Set conn2 = Server.CreateObject("ADODB.Connection")
  47. 'Create Recordset object
  48. Set rsProductList = Server.CreateObject("ADODB.recordset")
  50. 'Open the connection using the previous connection string
  51. conn2.open connstr
  53. 'Declare a variable to store the query to be excuted. Here we are using a hardcoded query.We can also use a stored procedure
  54. QueryProduct = "select Product_Name,Product_ID from tbl_Products order by Product_Name asc"
  56. 'Execute the query
  57. set rsProductList = conn2.Execute(QueryProduct)
  58. 'Declare the array
  59. Dim arrProducts
  61. if not rsProductList.EOF then
  62. arrProducts = rsProductList.GetRows() ' Convert recordset to 2D Array
  63. end if
  65. 'Close the Connection and recordset
  67. rsProductList.Close
  68. conn2.Close
  69. Set rsProductList = Nothing
  70. Set conn2 = Nothing
  71. %>
  73. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  74. <html xmlns="http://www.w3.org/1999/xhtml">
  75. <head>
  76. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  77. <title>Populate Product dropdown</title>
  78. </head>
  79. <body>
  80. <br/>
  81. <label id="lblOne" for="ddlproduct" title="Select Product:" class="Example">Product:</label>
  82. <select name="productSelect" id="productSelect">
  83. <%
  84. 'Check whether it's a proper array or not
  85. if IsArray(arrProducts) then
  87. For i = 0 to ubound(arrProducts, 2) %>
  89. <option value="<%= arrProducts(1,i)%>" <%= selectProduct(arrProducts(0,i)) %>> <%= arrProducts(0,i) %> </option>
  91. <% next %>
  93. <% else %>
  95. <option value=""> Select </option>
  97. <% end if %>
  99. </select>
  100. </body>
  101. </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.