Query Query - 1 month ago 12
Node.js Question

nodejs mssql syntax error

When I use the following query with the node-mssql module it gives an error stating that there is an

Invalid column name 'L'.


For some reason, it thinks the value within the like statement is a column name.

'SELECT TOP 10 * From [Products] WHERE [Code] LIKE "%LO%"


Is there some sort of special syntax I need to use

FULL CODE (from mssql npm example):

var express = require('express');
var app = express();

app.get('/', function (req, res) {

var sql = require("mssql");

// config for your database
var config = {
user: 'username',
password: 'password',
server: '192.168.0.165\\database', // You can use 'localhost\\instance' to connect to named instance
database: 'Products',

options: {
encrypt: false // Use this if you're on Windows Azure
}
}

// connect to your database
sql.connect(config, function (err) {

if (err) console.log(err);

// create Request object
var request = new sql.Request();

// query to the database and get the records
request.query('select TOP 10 * From [Products] WHERE [Code] LIKE "%LO%"', function (err, recordset) {

if (err) console.log(err)
var items = [];
recordset.forEach(function(row){
items.push(row.Code);
})

res.send(items);

});
});
});

var server = app.listen(5000, function () {
console.log('Server is running..');
});

Answer

When using single quotes within a sql statement you must escape the ' character.

For example:

'SELECT TOP 10 * FROM [Products] WHERE [Code] LIKE \'%LO%\''
Comments