Insert request parameters using node js and mssql
11,223
You're accessing the req.body.CName directly in the string, this won't work, you'll need to use parameters for your query:
// Change execute query to accept parameters.
var executeQuery = function(res,query,parameters){
sql.connect(dbconfig,function(err){
if(err){
console.log("there is a database connection error -> "+err);
res.send(err);
}
else{
// create request object
var request = new sql.Request();
// Add parameters
parameters.forEach(function(p) {
request.input(p.name, p.sqltype, p.value);
});
// query to the database
request.query(query,function(err,result){
if(err){
console.log("error while querying database -> "+err);
res.send(err);
}
else{
res.send(result);
sql.close();
}
});
}
});
}
//POST API
app.post("/api/Category", function(req , res){
var parameters = [
{ name: 'CName', sqltype: sql.NVarChar, value: req.body.CName},
{ name: 'CSubCategory', sqltype: sql.NVarChar, value: req.body.CSubCategory},
];
var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (@CName, @CSubCategory)";
executeQuery (res, query, parameters);
});
Author by
Mohammed Ehab
Updated on June 11, 2022Comments
-
Mohammed Ehab almost 2 years
I'm having a problem while trying to make an insert statement using the code below and I tried to separate the request body parameters but also not working:
//Initiallising node modules var express = require('express'); var bodyPasrser = require('body-parser'); var sql = require('mssql'); var app = express(); //Body Parser to parse to JSON app.use(bodyPasrser.json()); //CORS Middleware app.use(function(req,res,next){ res.header("Access-Control-Allow-Origin","*"); res.header("Access-Control-Allow-Methods","GET,HEAD,POST,PUT,OPTIONS"); res.header("Access-Control-Allow-Headers","Origin,X-Requested-With,contentType,Content-Type,Accept,Authorization"); next(); }); //Setting up server var server = app.listen(process.env.PORT||8080,function(){ var port = server.address().port; console.log("App now running on port ",port); }); //setup database connection var dbconfig = { user:"sa", password:"...", server : "localhost", database: "supermarket" }; // ConnectionPool //connect to the database var executeQuery = function(res,query){ sql.connect(dbconfig,function(err){ if(err){ console.log("there is a database connection error -> "+err); res.send(err); } else{ // create request object var request = new sql.Request(); // query to the database request.query(query,function(err,result){ if(err){ console.log("error while querying database -> "+err); res.send(err); } else{ res.send(result); sql.close(); } }); } }); } //POST API app.post("/api/Category", function(req , res){ var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (req.body.CName,req.body.CSubCategory)"; executeQuery (res, query); });
The error displayed while testing through postman is like the following:
"The multi-part identifier \"req.body.CSubCategory\" could not be bound."