| Author |
Topic |
|
pippett2
Starting Member
6 Posts |
 Posted - 2009-09-18 : 12:56:01
|
| Hello,I am new to using ms SQL Server and --I think-- have a rather elementary question. I recently created a bit field in my table to store whether a Client (pertaining to my database) is active or not: true or false. On my webpage I have created two check boxes which allows a user to choose if he/she would like to see active or unactive clients, or all clients by not selecting a checkbox. I am able to return the list of clients fine, my problem is trying to run a report from this information. I am trying to run a report showing what clients are active or unactive or simply show all clients. I am running into the problem trying to return only the clients that aren't active (false). It seems I can reurn all clients and active clients easily using a case statement, but I can't return only the false values (unactive). Can anyone help me with a solution? Is it evening possible doing what I am trying to do using a bit value? Thanks in advance for any help. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-18 : 13:02:13
|
| I don't understand the problem. You have a non nullable [isActive] column in your client table? If so and you want to return all clients that: are only inactive: where [isActive] = 0are only active: where [isActive] = 1all clients: (set no criteria for the [isActive] column)Be One with the OptimizerTG |
 |
|
|
pippett2
Starting Member
6 Posts |
Posted - 2009-09-18 : 13:05:53
|
| Sorry for not explaining that, but every client gets set a value whether it is active or unactive, therefore there are no null values allowed in the column [IsActive]. |
|
|
|
pippett2
Starting Member
6 Posts |
Posted - 2009-09-18 : 13:33:01
|
| the problem I think I'm running into is the two checkboxs return a value that determine the outcome of the report. If the one check box is checked it returns a true value where the other check box returns a false, and vice versa, unless they both are not checked, thus returning two false values. I am not sure this is possible because in the SQL of the report they both are returning a value that is checking against the same field (sorry I am trying the best way to explain it). So everytime I run a report I only get, either, all clients or only clients who are active. I cannot get the report to show clients who are not active. |
|
|
|
pippett2
Starting Member
6 Posts |
Posted - 2009-09-18 : 13:47:42
|
| Here's what I am doing. I know it doesn't make sense/work but I am trying to work it out.and case @IsActiveCheckBox when 0 then 0 else @IsActiveCheckBox end = case IsActiveCheckBox when 0 then 0 else c.IsActive endand case IsNotActiveCheckBox when 0 then 0 else IsNotActiveCheckBox end = case IsNotActiveCheckBox when 0 then 0 else c.IsActive end |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-18 : 14:13:09
|
quote:
Originally posted by pippett2
Here's what I am doing. I know it doesn't make sense/work but I am trying to work it out.and case @IsActiveCheckBox when 0 then 0 else @IsActiveCheckBox end = case IsActiveCheckBox when 0 then 0 else c.IsActive endand case IsNotActiveCheckBox when 0 then 0 else IsNotActiveCheckBox end = case IsNotActiveCheckBox when 0 then 0 else c.IsActive end
Are you building an in-line sql statement and executing it from within your application or are you calling a stored procedure with parameters?In either case, post the sql code. Based on the code you just posted it looks like you have one variable (@isActiveCheckBox) and 1 sql columns IsActive. so what objects are [IsNotActiveCheckBox] and [IsActiveCheckBox]? They can't represent the actual checkbox controls in your application, right because Sql Server has no knowlege of them?Be One with the OptimizerTG |
|
|
|
pippett2
Starting Member
6 Posts |
Posted - 2009-09-21 : 11:03:29
|
quote:
Originally posted by TGAre you building an in-line sql statement and executing it from within your application or are you calling a stored procedure with parameters?In either case, post the sql code. Based on the code you just posted it looks like you have one variable (@isActiveCheckBox) and 1 sql columns IsActive. so what objects are [IsNotActiveCheckBox] and [IsActiveCheckBox]? They can't represent the actual checkbox controls in your application, right because Sql Server has no knowlege of them?Be One with the OptimizerTG
I'm executing the statement within my application. The two check boxes returns a value using the ReportService that adds a new proxy parameter. Thats how I set IsActiveCheckBox and IsNotActiveCheckBox return a value that the sql server can acknowledge them. |
|
|
|
pippett2
Starting Member
6 Posts |
Posted - 2009-09-21 : 12:02:10
|
| Okay, I think I figured out what I am going to do. I think I'm going to do a CASE statment in the where clause to figure it out. I think I got what you were saying, I just wasn't sure and the more I read the more I understood. Instead of returning the two checkbox values I am going to have one value returned to my parameter. I'm trying to figure out this case statement thought. I wonder if someone can help me. Here's what I am trying to perform. CASE @IsActive WHEN 0 THEN @IsActive = c.IsActive WHEN 1 THEN @IsActive= c.IsActiveELSE 0 = 0 END |
|
|
|
|
Returning bit values