Incorrect syntax near 'null' on a database query

Question

I have the following code and am getting an "Incorrect syntax near 'null'" error when it runs.

var queryResult = qie.doQuery(
  "myDatabase",
  "select patmname, patdob, patsex, pataddr2, pataddr1, patcity, patstate, patzip\n" +
  "   from analyzer_tci.dbo.patdemo_t\n" +
  "   where acctno = ('" + messageCache.getValue('Acct Number') + "'");

Here is the error I receive

Why is it not working?

Answer 1

The approach I would take to this problem is to check if the account number is null before using it in the sql statement.  If the accountNumber is null, you should skip the query, and possibly error the message.  So something like this...

var accountNumber = messageCache.getValue('Acct Number');

if (accountNumber !== null) {

   var queryResult = qie.doQuery(

     "GPMS Analyzer_TCI",

     "select patmname, patdob, patsex, pataddr2, pataddr1, patcity, patstate, patzip\n" +

     "   from analyzer_tci.dbo.patdemo_t\n" +

     "   where acctno = '" + accountNumber + "'");

 

  // ... use the queryResult.getNode() to get the fields

} else {

  throw "AccountNumber is null.  Cannot perform lookup.";

}