SQL cmd.command include an OR statement

Question

Below is my SQL cmd.CommandText query:

cmd.CommandText = "SELECT CONVERT(date, [DateTime]) 'Date of Download', ActionBy AS 'User Full Name', COUNT(DISTINCT CAST(Identifier AS NVARCHAR(10)) + Remarks + Link) 'Number of Document Downloads' " + 
    " FROM [dbo].[AuditLog] "+
    " WHERE ActionTaken = 'Clicked' "+
    " and Type = 'Attachment Link'" +
    " and CONVERT(date, [DateTime]) BETWEEN CONVERT(date,'" + scanStartDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') and CONVERT(date,'" + scanEndDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') "+
    " GROUP BY CONVERT(date, [DateTime]), ActionBy "+
    " HAVING COUNT(DISTINCT Identifier) > " + limit + 
    " ORDER BY COUNT(DISTINCT Identifier) DESC";

I would like to include an extra parameter OR for Type, like as follows:

" and Type = 'Attachment Link' OR 'Attachment Link - Search'" +

May I know how can it be done?

Thank you.

Edit: Apologies, did not add in the error message, as follow.

An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

enclose your OR in parentheses (Type = 'Attachment Link' or Type = 'Attachment Link - Search') — Ed Bangga, Aug 22 '19 at 01:25

score 2 · Answer 1 · answered Aug 22 '19 at 01:31

specify the column after your OR operator

cmd.CommandText = "SELECT CONVERT(date, [DateTime]) 'Date of Download', ActionBy AS 'User Full Name', COUNT(DISTINCT CAST(Identifier AS NVARCHAR(10)) + Remarks + Link) 'Number of Document Downloads' " + 
        " FROM [dbo].[AuditLog] "+
        " WHERE ActionTaken = 'Clicked' "+
        " and (Type = 'Attachment Link' OR Type = 'Attachment Link - Search') " +
        " and CONVERT(date, [DateTime]) BETWEEN CONVERT(date,'" + scanStartDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') and CONVERT(date,'" + scanEndDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') "+
        " GROUP BY CONVERT(date, [DateTime]), ActionBy "+
        " HAVING COUNT(DISTINCT Identifier) > " + limit + 
        " ORDER BY COUNT(DISTINCT Identifier) DESC";

kjmerf · Accepted Answer · 2019-08-22T13:08:57.827

1

I assume I am over-simplifying but do you mean:

cmd.CommandText = "SELECT CONVERT(date, [DateTime]) 'Date of Download', ActionBy AS 'User Full Name', COUNT(DISTINCT CAST(Identifier AS NVARCHAR(10)) + Remarks + Link) 'Number of Document Downloads' " + 
    " FROM [dbo].[AuditLog] "+
    " WHERE ActionTaken = 'Clicked' "+
    " and (Type = 'Attachment Link' OR Type = 'Attachment Link - Search') " +
    " and CONVERT(date, [DateTime]) BETWEEN CONVERT(date,'" + scanStartDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') and CONVERT(date,'" + scanEndDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') "+
    " GROUP BY CONVERT(date, [DateTime]), ActionBy "+
    " HAVING COUNT(DISTINCT Identifier) > " + limit + 
    " ORDER BY COUNT(DISTINCT Identifier) DESC";

You might need ( ) for order of operations and I think you mean "=" instead of "-".

edited Aug 22 '19 at 13:08

answered Aug 22 '19 at 01:24

kjmerf

4,275
3
21
29

tried this but getting "An expression of non-boolean type specified in a context where a condition is expected, near 'Attachment Link = Search'" – Ed Bangga Aug 22 '19 at 02:36
Oh right... well hold on: what is the name of the column? I assume Type? And it can equal either AttachmentLink OR Search, right? Update above. – kjmerf Aug 22 '19 at 02:42
1

@gymcode is looking for (Type = 'Attachment Link' or Type = 'Attachment Link - Search') – Ed Bangga Aug 22 '19 at 03:17

SQL cmd.command include an OR statement

2 Answers2