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 class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>
<br>
<table class="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>
