pass value from db to dropdown menu

Question

i am trying to pass values from my database for a drop down in my view : this is a js file in my project :

newproject.js :

var express = require('express');
var router = express.Router();
var sql = require ('mssql');


router.get('/', function(req, res, next) {
    res.render('newProject');
});


const config = {
    user: 'sa',
    password: 'password',
    server: 'localhost\\SQLEXPRESS', // You can use 'localhost\\instance' to connect to named instance
    database: 'pcgdb',

    options: {
        encrypt: false // Use this if you're on Windows Azure
    }
};


sql.connect(config).then(() => {
    return sql.query`select Project_Type_Desc from Project_Type`
}).then(result => {
    console.log(result)
}).catch(err => {
    console.log(err)
})



module.exports = router;

this is the corresponding view :

index.ejs

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>PCG WEB APP</title>
</head>

<body>
<h1>Please input a new project</h1>
    <form class="form-horizontal" role="form" style="width: 50%;">
            <label for="sel1">Region : </label>
            <select class="form-control" id="sel1">
                <option>EMEA</option>
                <option>APAC</option>
                <option>AMER</option>
            </select>
    Country :
    <input type="text" name="coutry"><br>
    City:
    <input type="text" name="city"><br>
    Requested By :
    <input type="text" name="request_by"><br>
    Project Responsibility:
    <input type="text" name="project_responsibility"><br>
    Facility Classification:
    <input type="radio" name="facilityclassification" value="new" checked> New
    <input type="radio" name="facilityclassification" value="existing"> Existing<br>
            <label for="sel1">Project Type : </label>
            <select class="form-control" id="sel1">
                <option>Densification</option>
                <option>Renovation</option>
                <option>Expansion/Contraction of Office</option>
                <option>Infrastructure Upgrades</option>
                <option>Existing Office Relocation</option>
                <option>New Location</option>
            </select>
    Expected Start Date :
    <input type="date" name="start_date"><br>
    Expected End Date :
    <input type="date" name="end_date"><br>
    Brief description of scope of work:
    <input type="text" name="description"><br>
    Project manager :
    <input type="text" name="manager"><br>
    Project Owner :
    <input type="text" name="owner"><br>
    Project Sponser :
    <input type="text" name="sponser"><br>
    Functional Currency :
    <input type="text" name="currency"><br>
    <input type="submit" value="Submit" class = "btn btn-primary">
</form>
</div>
</body>
</html>

i want to pass the value i have queried to to view to use as a drop drown for the project type selection, please let me know if you guys know a solution . I am totally new to es5 or es6 and am having a hard time with the syntax . i would also like to query additional table to populate more drop downs .

the db is ms sql server and i am using mssql module to connect with the DB

Thanks in advance.

Have a look at this answer for how to pass variables to the ejs template: https://stackoverflow.com/a/16098699/2027146 — Milk, Aug 02 '17 at 20:11
And then this answer for how to use a loop in ejs to output your `select` `options`: https://stackoverflow.com/a/22952940/2027146 — Milk, Aug 02 '17 at 20:12

score 6 · Accepted Answer · answered Aug 02 '17 at 20:27

6

Pass the DB result to the template:

router.get('/', function(req, res, next) {
    sql.connect(config).then(() => {
        return sql.query`select Project_Type_Desc from Project_Type`
    }).then(result => {
        console.log(result)
        // Pass the DB result to the template
        res.render('newProject', {dropdownVals: result})
    }).catch(err => {
        console.log(err)
    })
});

Then in your template, use the values passed through:

<select class="form-control" id="sel1">
<% for(var i=0; i < dropdownVals.length; i++) { %>
   <option><%= dropdownVals[i] %></option>
<% } %>
</select>

answered Aug 02 '17 at 20:27

Milk

2,469
5
31
54

do you know how to query 2 more tables and pass those values as well ? have been struggling with this for 2 days – SSS Aug 04 '17 at 11:58
@SSS Please accept the answer if it worked for you. If you want to pass through multiple lists, you can make multiple database calls and pass the results through as different fields in the object passed through to `res.render` – Milk Aug 06 '17 at 19:06

pass value from db to dropdown menu

1 Answers1