How can I use MSSQL query result as my select box options

Question

I have a simple HTML web form that contains a select box. I would like to set the options to use the results of my MSSQL query. (It's a list of product numbers)

Here is what I currently have:

var {TYPES, Request, Connection} = require('tedious');

// Create connection to database
var config = {
  userName: 'user',
  password: '123123',
  server: 'someserver',
  options: {
    instanceName: 'test'
  }
};
var connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on('connect', (err) => {
  if(err){
    console.log(err);
  }
    run();
});
function run(request, result){
  request = new Request("USE Database; SELECT PartNumber FROM dbo.Master ORDER BY PartNumber;", (err) =>{
    if(err){
      console.log(err);
      connection.close();
    }
    connection.close();
  });
  var result = "";
  request.on('row', (columns) =>{
    columns.forEach((column) =>{
      result+= column.value + " ";
    });
    console.log(result);
    result = "";
  });
  connection.execSql(request);
}

<div class="row">
  <div class="col span-1-of-3">
    <label for="partnumber">PartNumber</label>
  </div>
    <div class="col span-2-of-3">
      <select>
        <option  required></option>
      </select>
    </div>
</div>

I am having troubles finding any example of this on the Web. I want to click the Select Box and my list from the JavaScript code appear. I can run the console and see the results, but can not populate the Options.

Possible duplicate: https://stackoverflow.com/questions/6601028/how-to-populate-the-options-of-a-select-element-in-javascript — Nicholas Hirras, Mar 01 '18 at 16:21
I looked at the Possible duplicate reference, but am not sure if it is a duplicate. I am running a sql query, and I do not know how to return the results into a select box. I can get the results when I console.log(results), but am unsure where to go from there. — Mike Arney, Mar 01 '18 at 16:39
You have the results, it's just a matter of populating the options from javascript. That has nothing to do with it being a SQL result or just plain text. Will supply an example below as answer. — Nicholas Hirras, Mar 01 '18 at 16:45

score 1 · Accepted Answer · answered Mar 01 '18 at 16:49

You can try something like this. You'll need to loop over the results and create options from each item.

var {TYPES, Request, Connection} = require('tedious');

// Create connection to database
var config = {
  userName: 'user',
  password: '123123',
  server: 'someserver',
  options: {
    instanceName: 'test'
  }
};
var connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on('connect', (err) => {
  if(err){
    console.log(err);
  }
    run();
});
function run(request, result){
  request = new Request("USE Database; SELECT PartNumber FROM dbo.Master ORDER BY PartNumber;", (err) =>{
    if(err){
      console.log(err);
      connection.close();
    }
    connection.close();
  });
  var result = "";
  request.on('row', (columns) =>{
    columns.forEach((column) =>{

     var opt = document.createElement("option");
     opt.value = column.value;
     opt.innerHTML = column.value;
     document.getElementById('results').appendChild(opt);


      result+= column.value + " ";
    });
    console.log(result);
    result = "";
  });
  connection.execSql(request);
}

<div class="row">
  <div class="col span-1-of-3">
    <label for="partnumber">PartNumber</label>
  </div>
    <div class="col span-2-of-3">
      <select id="results">
        <option  required></option>
      </select>
    </div>
</div>

How can I use MSSQL query result as my select box options

1 Answers1