Display data from db on HTML using Node.js-CodePudding

I am trying to get a webpage to display data from my database and display it on a html page in a table and be able to scroll down on the table by rendering it in ejs. I based my code from this site https://www.tutsmake.com/node-js-fetch-and-display-data-from-mysql-database-in-html-list/

i'm not really sure what I'm doing wrong, the db is connecting just fine, and I have the ejs file in my views folder. I'm guessing the issue is coming from the ejs file but I'm not sure. I've just started coding again so this is basically all new to me.

when I go to localhost:3000/test I'm getting a 404 error and when I look at the terminal it says GET/TEST 404 20.377ms -1153, I think I might be pulling to much data at once since the there are 75 rows for this, I've tried to limit the amount of rows but it still wont load the page and for some reason this code can't even find my ejs file.

From what I've read online there are a million different ways to do this and I don't know where to start to get this working. I'm using mssql as my database and using visual studio to build everything.

this is my server.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var expressValidator = require('express-validator');
var flash = require('express-flash');
var session = require('express-session');
var bodyParser = require('body-parser');

var sql = require("mssql/msnodesqlv8");
var connection = require('./lib/db');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use(session({
   secret: '123456cat',
   resave: false,
   saveUninitialized: true,
   cookie: { maxAge: 60000 }
}))

app.use(flash());
app.use(expressValidator());

app.use('/', indexRouter);
app.use('/index', usersRouter);

// catch 404 and forward to error handler
app.use(function (req, res, next) {
   next(createError(404));
});

// error handler
app.use(function (err, req, res, next) {
   // set locals, only providing error in development
   res.locals.message = err.message;
   res.locals.error = req.app.get('env') === 'development' ? err : {};
   // render the error page
   res.status(err.status || 500);
   res.render('error');
});
module.exports = app;

this is my dbaCon.js

const sql = require("mssql/msnodesqlv8");

var conn = new sql.ConnectionPool({
    server: 'DESKTOP-BA7Q2HM',
    port: 1433,
    driver: 'msnodesqlv8',
    database: "tempdb",
    domain: 'DNAME',
    requestTimeout: 3600000,
    options: {
        trustedConnection: true,
        enableArtithAbort: true
    },
    debug: true,
    parseJSON: true
});

conn.connect(function (err) {
    if (err) throw err;
    console.log('Database is connected successfully!');
});
module.exports = conn;

this is my users.js

var express = require('express');
var router = express.Router();
var connection = require('../dbaCon.js');


/* GET home page. */
router.get('/', function (req, res, next) {

    connection.query('SELECT id, status, type, Date, Review * FROM [dba].[table] ', function (err, rows) {

        if (err) {
            req.flash('error', err);
            res.render('index', { page_title: "test - Node.js", data: '' });
        } else {

            res.render('index', { page_title: "test - Node.js", data: rows });
        }

    });
});

module.exports = router;

this is my index.ejs file

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Display Active Loan info to Client</title>
</head>
<body>
    <% if (messages.error) { %>
    <p style="color:red"><%- messages.error %></p>
    <% } %>
    <% if (messages.success) { %>
    <p ><%- messages.success %></p>
    <% } %>
    <br>
    <table >
        <thead>
            <tr>
                <th scope="col">id</th>
                <th scope="col">status</th>
                <th scope="col">type</th>
                <th scope="col">date</th>
                <th scope="col">review</th>

            </tr>
        </thead>
        <tbody>
            <% if(data.length){
            for(var i = 0; i< data.length; i  ) {%>
            <tr>
                <th scope="row"><%= (i 1) %></th>
                <td><%= data[i].id%></td>
                <td><%= data[i].status%></td>
                <td><%= data[i].type%></td>
                <td><%= data[i].date%></td>
                <td><%= data[i].review%></td>
            </tr>
            <% }
            }else{ %>
            <tr>
                <td colspan="3">No Data Availed</td>
            </tr>
            <% } %>
        </tbody>
    </table>
</body>
</html>

CodePudding user response：

you do not have a route for "/test".

you have these routes that points to '/WHAT_YOU_HAVE_ON_indexRouter' and '/index/WHAT_YOU_HAVE_ON_userRouter'

app.use('/', indexRouter); app.use('/index', usersRouter);

on your userRouter you dont have a '/test' route, you need to add it.

you reach the 404 because the app cant find any route related to '/test'

CodePudding user response：

when I go to localhost:3000/test I'm getting a 404 error

You didn't setup any route with the endpoint /test, at least not on the code you posted, that's why you get an 404 not found error.

I think I might be pulling to much data at once since the there are 75 rows for this

No, 75 rows are no close to much data. You should edit your question to clarify each problem.