PRB: dbwritetext Returns SQL Server Message 156

  - Microsoft SQL Server Programmer's Toolkit, version 4.2

SYMPTOMS

When attempting to call the dbwritetext function to update a text or image value, a DB-Library application can receive the following error message 156:

   Incorrect syntax near the keyword NULL.

CAUSE

This error is caused by passing a NULL text pointer value returned by dbtxptr, and a NULL text timestamp value returned by dbtxtimestamp, to the dbwritetext function.

The dbtxptr and dbtxtimestamp functions return pointers to values from the current DB-Library row, similar to dbdata. The dbtxptr and dbtxtimestamp functions can return NULL values for two reasons:

1. The text or image value in the row and column returned by the SELECT statement is NULL.

2. The dbtxptr and dbtxtimestamp functions are called when DB-Library has no current row. DB-Library has no current row when dbnextrow has returned NO_MORE_ROWS, or dbresults has returned NO_MORE_RESULTS.

WORKAROUND

Ensure that both the dbtxptr and dbtxtimestamp functions return valid, non-NULL values before passing them to dbwritetext. This can be achieved by ensuring that:

1. The text or image value in the row and column returned by the SELECT statement is non-NULL.

2. The dbtxptr and dbtxtimestamp functions are called when DB-Library has a valid current row. DB-Library has a current row when dbnextrow returns REG_ROW.

3. The non-NULL values returned by dbtxptr and dbtxtimestamp are passed to dbwritetext while the same row is still current. In other words, do not call dbnextrow until these values are actually used.