CODEWITHSUNDEEP
sqlvbscript
Warhead
Warhead

Reputation: 103

Use sql data to populate dropdown using vbscript

So I have this code to populate a select dropdown using vbscript which is not working any help would be great. I also think the response is not supposed to be used.

 Set myConn = CreateObject("ADODB.Connection")
Set rec = CreateObject("ADODB.RecordSet")
With myConn
   .Provider = "SQLOLEDB"
   .Properties("Data Source") = "**"
   .Properties("Initial Catalog") = "***"
   .Properties("User ID") = "**"
   .Properties("Password") = "**"
   .Open
End With
   queryEmp = "SELECT Name as FN FROM dbo.Employee where Active='True'"
    rec.open(queryEmp,myConn)
    Do Until rec.EOF
    msgBox = rec.Fields("FN")
    strExample = rec.Fields("FN")
         response.write "<OPTION>" & strExample & "</OPTION>"
    End If
    rec.MoveNext
    Loop

Upvotes: 1

Views: 1874

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38765

To avoid security hassles, I used a .HTA for this demo:

<html>
 <head>
  <title>SQL DropDown Demo</title>
  <hta:application
     id="demo"
  ></hta>
  <script type="text/vbscript">

Option Explicit

Sub Window_OnLoad
  Dim oDD : Set oDD = document.GetElementById("ddSQL")
  Dim oDb : Set oDb = CreateObject("ADODB.Connection")
  oDb.Open "valid connection string"
  Dim sQL : sQL     = "valid SQL query"
  Dim oRS : Set oRS = oDb.Execute(sQL)
  Dim oOpt
  Do Until oRS.EOF
      Set oOpt = oDD.document.createElement("option")
      oOpt.Text  = oRS.Fields("valid column name").Value
      oOpt.Value = oOpt.Text
      oDD.Options.Add oOpt
      oRS.MoveNext
  Loop
End Sub

  </script>
 </head>
 <body>
  <form>
   <select id="ddSQL">
   </select>
  </form>
 </body>
</html>

Evidence:

Evidence

Key concepts: empty select element, using the resultset loop to create options, setting their properties, adding them to the select.

Upvotes: 1

Related Questions