0

I am trying to run the delete query from app.js file to postgresql database but every time I am getting error: invalid input syntax for integer: "undefined" Below is my app.js file code:

var express = require('express'),
    path = require('path'),
    bodyParser = require('body-parser'),
    cons = require('consolidate'),
    dust = require('dustjs-helpers'),
    pg = require('pg-promise'),
    pgdb = require('pg'),
    app = express();


//DB Connection
const config = {
    user: 'Nasreen',
    database: 'Inventory1',
    password: 'test',
    port: 5432                  //Default port, change it if needed
};

const pool = new pgdb.Pool(config);


//assign dust engine 
app.engine('dust', cons.dust);

//set default ext
app.set('view engine', 'dust');
app.set('views', __dirname + '/views');

//set public folder
app.use(express.static(path.join(__dirname, 'public')));

//Body parser middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

app.get('/', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }

        client.query('SELECT * FROM "Products"', function (err, result) {
            if (err) {
                return console.error('error running query', err);
            }
            res.render('index', { Products: result.rows });
            done();
        });
    });
});

app.post('/add', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }

        client.query('INSERT INTO "Products" ("Name", "Purchase_Qty", "Purchase_Rate") VALUES($1, $2, $3)',
            [req.body.Name, req.body.Purchase_Qty, req.body.Purchase_Rate]);

        done();
        res.redirect('/');
    });
});

app.delete('/delete/:ID', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }
        client.query('DELETE FROM "Products" WHERE "ID" = $1',
            [req.params.ID]);

        done();
        res.sendStatus(200);
    });
});


app.post('/edit', function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            return console.error('error', err)
        }

        client.query('UPDATE "Products" SET "Name"= $1, "Purchase_Qty"= $2, "Purchase_Rate"= $3 WHERE "ID"= $4',
            [req.body.Name, req.body.Purchase_Qty, req.body.Purchase_Rate, req.body.ID]);

        done();
        res.redirect('/');
    });
})

INSERT is working fine with double quotes for column names and table name but delete and edit wont't work. Can someone plz help!!

1 Answer 1

1

You don't need to quote the tables' names and the columns' names.
Change the delete statement to:

"DELETE FROM Products WHERE ID = '$1'";

Change the update statement to:

"UPDATE Products SET Name= '$1', Purchase_Qty= '$2', Purchase_Rate= '$3' WHERE ID= '$4'";

EDIT: Try this:

client.query(`DELETE FROM "Products" WHERE "ID" = ${req.params.ID}`);

It's a function overloading for query. Using template string should solve the issue.

Instead of:

client.query('DELETE FROM "Products" WHERE "ID" = $1',[req.params.ID]);
Sign up to request clarification or add additional context in comments.

3 Comments

It doesn't recognize the table name and column name without quotes
So specify the table's name with the schema. I.E SELECT <schema_name>.<table_name>...
I am very thankful to u for your replies but the edit syntax is also not working for me :( I am not sure whats the issue :'(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.