0

Tired brain - perhaps you can help.
My table has two bit fields:
1) TestedByPCL and
2) TestedBySPC.
Both may = 1.
The user interface has two corresponding check boxes. In the code I convert the checks to int.

int TestedBySPC = SearchSPC ? 1 : 0;
int TestedByPCL = SearchPCL ? 1 : 0;

My WHERE clause looks something like this: 

WHERE TestedByPCL = {TestedByPCL.ToString()} AND TestedBySPC = {TestedBySPC.ToString()}

The problem is when only one checkbox is selected I want to return rows having the corresponding field set to 1 or both fields set to 1.
Now when both fields are set to 1 my WHERE clause requires both check boxes to be checked instead of only one.
So, if one checkbox is ticked return records with with that field = 1 , regardless of whether the other field = 1.

DeveloperDan
  • 4,626
  • 9
  • 40
  • 65
  • Show us some sample table data and the expected result. (As formatted text, no images.) – jarlh Sep 18 '19 at 20:17
  • This: *when only one checkbox is selected I want to return rows having both fields set to true* and this: *if one checkbox is ticked return records with with that field true, regardless of whether the other field is true* are 2 different requirements. Will you clarify? – forpas Sep 18 '19 at 20:31
  • I clarified the question (I hope) but it looks like I have the answer from Zohar below. Thanks. – DeveloperDan Sep 18 '19 at 20:40

1 Answers1

1

Second attempt (I think I've got it now):

WHERE ((TestedByPCL = {chkTestedByPCL.IsChecked} AND TestedBySPC = {chkTestedBySPC.IsChecked}) 
OR 
(TestedByPCL = 1 AND TestedBySPC = 1 AND 1 IN ({chkTestedByPCL.IsChecked}, {chkTestedBySPC.IsChecked})))

Misunderstood the question. Change the AND to an OR:

WHERE TestedByPCL = {chkTestedByPCL.IsChecked} OR TestedBySPC = {chkTestedBySPC.IsChecked}

Also:

  1. SQL Server does not have a Boolean data type, it's closest option is a bit data type.

  2. The usage of curly brackets suggests using string concatenations to build your where clause. This might not be a big deal when you're handling checkboxes but it's a security risk when handling free text input as it's an open door for SQL injection attacks. Better use parameters whenever you can.

DeveloperDan
  • 4,626
  • 9
  • 40
  • 65
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Yes, the data type is bit. I've modified the question to match my code. With this answer though when I uncheck both boxes results are returned. I get results where either fields is zero. – DeveloperDan Sep 18 '19 at 20:30
  • Excellent! Your revision seems perfect. Sorry I wasn't clear initially. – DeveloperDan Sep 18 '19 at 20:36
  • [Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) – Zohar Peled Sep 18 '19 at 20:50
  • There is a [boolean](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql?view=sql-server-2017#boolean-data-type) data type (with values TRUE, FALSE and UNKNOWN), but you cannot get a firm grip on one: "Unlike other SQL Server data types, a **Boolean** data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set." Using `where`, `case`, `iif`, ... would be challenging without boolean values. – HABO Sep 18 '19 at 20:52
  • @HABO Well, we are developers. If we won't nitpick, who will? Of course you are correct. – Zohar Peled Sep 18 '19 at 21:00
  • @developerDan this is now pretty far from the version I wrote. You are welcome to post and accept your own answer, but please stop changing the code in mine. Thanks. – Zohar Peled Sep 19 '19 at 19:06