0

I am very new to Node, just trying to write a simple application that has 2 drop downs being populated from a mssql query and when I hit submit, the corresponding data should be displayed on the next page.

In further details...

I have two .ejs pages, (index.ejs, selection.ejs). The drop downs belong to index.ejs and I want to be able to select items from both drop downs Name, Date and hit the Submit button. Which then will route me to the selection.ejs page and display a table of 7 columns based on the selected criteria.

router.js

router.get('/', async (req, res) => {
  try {
    var name = await conn.query("SELECT DISTINCT pr.Name FROM WFS.PRTABLE pr WHERE pr.Functional_Group = 'Test'");
    var dates = await conn.query('SELECT r.Date FROM WFS.Dates r');
    res.render('index', {name : name , dates: dates});
      } catch (err) {
        res.status(500)
        res.send(err.message)
      }
  });


router.get('/selection', async (req, res) =>{
  try {
    var name = await conn.query("SELECT DISTINCT pr.Name FROM WFS.PRTABLE pr WHERE pr.Group = 'Test'");
    var dates = await conn.query('SELECT r.Date FROM WFS.Dates r');

    var dateID = req.body.Dates;
    var nameID = req.body.Names;

    var tables = await conn.query("SELECT * FROM WFS.Views v WHERE v.Name = ? AND v.Date = ?", [ nameID , dateID ], function(err){
    if(err) throw err;
       res.render('selection', {tables: tables, name : name , dates: dates});
       });
    } 
      catch (err) {
        res.status(500)
        res.send(err.message)
    }
 });

index.ejs 

 <select class="DateDD" id="selection" name="Dates">
      <% for(var n=0; n < dates.recordset.length; n++) { %>
           <option><%= dates.recordset[n].Date%></option>
      <% } %>
  </select>
  <select class="NameDD" id="selection" name="Names">
      <% for(var n=0; n < name.recordset.length; n++) { %>
           <option><%= name.recordset[n].Name%></option>
      <% } %>
   </select>
<input type="submit" name="Submit" id="submitData" class="btn btn-primary" value="View Report" />

selection.ejs

CONTAINS THE SAME THING AS INDEX.EJS AND ... 

<table class="table table-bordered table-condensed table-striped">
    <% for(var n=0; n < tables.recordset.length; n++) { %>
        <tr>
            <td><%=tables.recordset[n].Name%></td>
            <td><%=tables.recordset[n].Date%></td>
             ....
             ....
             ....
             ....
        </tr>
    <% } %>
    </table>

I keep receiving this error Incorrect syntax near '?'. Don't know whats causing it. All I want to do is pass in the selected name and date from the drop down into the query as my WHERE clause and render the data on selection.ejs accordingly.

caitlinp
  • 183
  • 13
  • Can you verify that `DateID` and `NameID` have values when passing to the `SQL Select` statement? – SS_DBA Apr 18 '19 at 13:31
  • They actually don't... they're both undefined.. How can I fix that? – caitlinp Apr 18 '19 at 13:53
  • It's been a while since I did `javascript`, but shouldn't you be passing the `selectedvalue` of the `dropdown` list? – SS_DBA Apr 18 '19 at 14:02
  • see if this helps... https://stackoverflow.com/questions/1085801/get-selected-value-in-dropdown-list-using-javascript – SS_DBA Apr 18 '19 at 14:03
  • I added this : `$(document).ready(function() {` `$('#submitData').click(function(event) {` `var relVal = $('#selections option:selected').val();` `alert(relVal);` `});` However, when I pass `relVal` its still undefined. – caitlinp Apr 18 '19 at 19:59

0 Answers0