🚀 go-pugleaf

It looks to me like the problem is that you have single quotes around the
second %s of the format string.  They are correct around the first, since
you need to quote the part number in [VEND_PART_NO] = 'abc949',
but around the second, I think you end up quoting part of your WHERE
clause:

[VEND_PART_NO] = 'abc949' AND
'[VEND_PART_NO] NOT LIKE 'WP-%' AND [VEND_PART_NO] NOT
LIKE 'KS-%''

It's always an excellent idea to actually look at the query you
construct when you do anything like this - if the error is what I think,
you will catch it if you look at the query (and even perhaps try to run it
outside your program).

Steve Kass
Drew University

moniqueh wrote:

> Hi all,
>
> I am trying to query a database using SQL in my Visual C++ program
> code.
> In the WHERE clause, I want to exclude all the VEND_PART_NO starting
> with 'WP-' and 'KS-'. So, I used "%" character and "NOT LIKE" in my
> SQL statement. The following is my SQL codes and the error message I
> am getting:
>
> Error message:
>
> SQL0007N The Character "%" following "RT_NO] NOT LIKE 'WP-" IS NOT
> VALID.  SQLSTATE=42601
>
> Visual C++ and SQL Codes:
>
> CString lpszSQLPN, templpszSQL;
> templpszSQL = "[VEND_PART_NO] NOT LIKE 'WP-%' AND [VEND_PART_NO] NOT
> LIKE 'KS-%'";
>
> lpszSQLPN.Format("SELECT DISTINCT [PART_NO] \
>                   FROM [CCL].[APPRVD_VEND_PART] \
>                   WHERE \
>                   (\
>                    [VEND_ID] = %d AND \
>                    [VEND_ID] != 722 AND \
>                    [VEND_ID] != 999 AND \
>                    [VEND_PART_NO] = '%s'AND \
>                    '%s' \
>                   ) \
>                   ORDER BY [PART_NO]",m_pSet->m_VEND_ID,m_pSet->m_VEND_PART_NO,
> templpszSQL);
>
> if( ( m_pSetPN->Open(CRecordset::forwardOnly,lpszSQLPN,CRecordset::readOnly)
> ) == 0)
>         AfxMessageBox("Error opening Recordset!");
>
> I didn't include the templpszSQL statement directly in lpszSQLPN
> because the VC++ compiler seems to confuse the "%" character for SQL
> with the "%s" and "%d" which parameters are being passed into in a
> CString::Format statement.
>
> Please help....
>
> Thanks for any ideas in advance.
>
> Regards,
> Monique