Booleans?

Hello.
I just changed the databases of a website from Access to SQL Server.
Converting the bases went smothly, but now I recieve error message to my SQL-quereries:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'.

The query is: "Select pieces,date from orders where active = True AND company_id <> 19 AND authorize_wait = 0 ORDER BY send_date desc"

When I change int the query to: "active = 1" the page works like before (with Access)

I supose SQL Server is able to understand commands with True/False-quereries, so what is wrong here?
Thank you for your help
/CalleMSSQL does not define a "True" or "False" variable. You need to either use a one as you have in your example OR define a local variable

declare @.True bit
set @.True = 1
Select pieces,date
from orders
where active = @.True
AND company_id <> 19
AND authorize_wait = 0
ORDER BY send_date desc|||Thank you for your response!
/Calle

Booleans, SqlServer, and VB

Hi,

The three above seem to form an evil combination. ;-)

For now, I use varchar(5) in my SqlServer 2000 db and fill it with "True" or "False", which VB understands as boolean values in my .aspx/.aspx.vb pages.

Is there no way of using Bit instead in SqlServer, or even 0/1, and easily bind it in VB? How do others do it? (I've only seen other people struggling with this, and not any solution).

I can't understand why a language such as VB cannot be allowed to understand both True/False, 1/0, and Y/N!

Muchos gracias in advance,

Pettrer

I do not know what about Y/N but 0/1 in VB is a little opposite because is 0/-1 so -1 is true in VB and 1 is true in SQL Bit field.

Thanks

|||

Thanks, but the problem is that VB only writes "True" and "False" to the database. Of course i might be able to change it in the INSERT/UPDATE statements, but then I read somewhere that booleans are not indexed in SqlServer, so I guess that it's bo point using it anyway. Am i wrong in this assumption? Please help.

Pettrer, very puzzled ;-)

|||

If your field is bit field is stored on SQL as 0 or 1 but in gridView in VB is be displayed as True/False by default (or as check box if you ask for this )check this code :

select

cast(-10asbit) true,cast(0asbit) false,cast(1asbit) true,cast(10asbit) true

As you could see from this example I was a little wrong in SQL looks like false is 0 and everything else is true

Thanks

|||

Yes, you're right - everything that is not 0 is true; absint - not absinth! ;-) - comes to mind...

However, my questions are (forgive me if I misunderstood your reply):

1. How do I get from the checkbox to boolean in the db (the other way becomes correct automatically )?

2. Is this a behaviour I want (ie having booleans in the db), as I read somewhere that booleans are not indexed - which sounds really strange to me, as that would affect server performance)? I could of course convert everything to 0/1 as integers, but it seems rather stupid to do that. What do people do? Is no one using VB with SqlServer?

Pettrer, puzzled! :-)

|||Use bit data type which is Ture/False in the database and you can set this column to required and with a default if you want.|||

Limno,

Thanks for replying. You're right of course, but the problem is not the SqlServer db per se, but how badly VB works together with it. I took a look in the db and realised that if I do this the boolean way, it's still one byte per entry, as they can't be divided into the bits. If I use the strings True and False, I get two bytes instead. It's really not a big deal, then (my advice to other VB developers must be to use the textual representation in the db). But it's really, really strange!

Pettrer

Boolean - Expanded/Collapsed

I have created a matrix where I have booleans on the rows and columns. They are initially in the collapsed state. For reporting purposes, I would like the initial state to to be expanded or at list a quick fix to quickly convert the collapsed booleans to expanded booleans.

I have navigated to the Layout tab and right-clicked the fields that are tied to the boolean and then clicked on the properties. This bring sup the Textbox Properties. From here, I click on the Visibility tab. At the bottom of the textbox is a section "Initial appearance of the toggle image for this report item:". The default choice is marked as collapsed (+). One would logically think that you you would have to do then is select the expanded (-). choice. Well, when I do this, all it simply does is chance the icon from a '+' to a '-' and the fields are still shown as collapsed.

Any ideas on what I need to do?

ttt|||

If I am understand it right, what you will have to do is

1. Select the Entire row and go to the properties

2. Go to "Visibility " --> "Hidden" set this to False if you want this to be expanded or to true.

|||

Expand/Collapse

1) Create a parameter and name it something like "Expand" and name the Prompt "Expand All" or whatever you choose

2) Set the parameter to Boolean

3) Set the default value Non-queried to "True"

4) Click on OK

5) Select properties for the control box that is supporting the +- like you said below

a)"I have navigated to the Layout tab and right-clicked the fields that are tied to the boolean"

6) Click on Visibility

7)Select Expression: and insert this "=Iif(Parameters!Expand.Value = True, True, False)"

8)Click OK

9)Select properties for the matrix and select "Groups"

10) Select the group where you have the "Visibility can be toggled by another report item"

11) Select Expression: and insert this "=Iif(Parameters!Expand.Value = True, False, True)"

12)Click on OK

13)Click on OK

Give it a shot. . .

When you change the parameter, you will need to reselect the view report button.

|||

This post is in response to Techquest ...

If I go to the Layout View, select the row, then right-click and select Properties, I do not see an option for "Visibility". However, if I select just one field, then right-click and select Properties, I do see an option for "Visibility". I have tried to change every single field by doing this (and if I remember correctly -- because it was last week) and it still does not do what I am looking for.

|||

Hi JCU1343,

Your right, you do not want to select the entire row, but instead you want to select just one field in the table. That's where you will find the Visibility control. It should be the field where the "+" and "-" sign will appear. When you insert the code: "=Iif(Parameters!Expand.Value = True, True, False)" in the Expression at the bottom of the "Visibility" tab, this gives control at the parameter level to change the "+" to "-" and back again. Then having selected the properties of the group in your table and selecting the "Visibility" tab for the group you should insert "=Iif(Parameters!Expand.Value = True, False, True)" which will expand/collapse the entire group depending on which choice you selected and you should also see the "checked" field here indicating the "Visibilty can be toggled by another report item". Every time you select the parameter you have to re-run the report.

Hope this helps. . .

Don't forget this feature depends on a parameter, and I know it works because I've used it on at least 8 reports for my company.

|||

FLHTCUI - Arizona Harley Rider:

Thank you so much for your posts (especially the first post). That is exactly what I was looking for/wanted. I truly appreciate it. You are a big help!

Can I direct you over to another post that I created that no one has been able to provide an answer to? It can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1852697&SiteID=1