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

Blank character in SQL table

Hello,
I have a field in database table called Person_name(varchar). The
Problem is that when the user enters something like this: Mr. Sam,
where there is a blank space after the dot, it displays only Mr. It
ignores any letters after white space. How can I change this, when
using update statement.
Regards
Divadivaof_7@.yahoo.com wrote:
> Hello,
> I have a field in database table called Person_name(varchar). The
> Problem is that when the user enters something like this: Mr. Sam,
> where there is a blank space after the dot, it displays only Mr. It
> ignores any letters after white space. How can I change this, when
> using update statement.
> Regards
> Diva
>
I'm not sure this is a SQL problem. You should check the client
application to see if it's doing some sort of parsing that is dropping
anything after the first space. Use SQL Profiler to capture the actual
INSERT statement coming from the app, I'll bet it's inserting "Mr."
instead of "Mr. Sam".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks
Can you briefly explain the idea of using SQL profiler, since I've
never dealt with the same
Tracy McKibben wrote:
> divaof_7@.yahoo.com wrote:
> I'm not sure this is a SQL problem. You should check the client
> application to see if it's doing some sort of parsing that is dropping
> anything after the first space. Use SQL Profiler to capture the actual
> INSERT statement coming from the app, I'll bet it's inserting "Mr."
> instead of "Mr. Sam".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||divaof_7@.yahoo.com wrote:
> Thanks
> Can you briefly explain the idea of using SQL profiler, since I've
> never dealt with the same
Profiler is used to trace the activity that is taking place in your
databases, I would HIGHLY recommend familiarizing yourself with it if
you're going to be working with SQL Server.
Essentially what you want to do is capture every INSERT statement that
is being issued against your table that is having problems. You can
either capture everything and sort through it manually, looking for
INSERT statements to that table, or you can use the filtering
capabilities of Profiler to capture only those statements.
Consult Books Online for further details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Bizare String Concatenation Issue - Explanation Needed

Hi all, can someone please explain why this is happening:
I execute the following:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM MyTable T
SELECT @.temp
Say [MyTable] looks like this:
|NAME|
--
A
B
C
D
Here are the result from running the above code on two different SQL Server
2000 installations:
SQL Server 2000 #1
[D]
SQL Server 2000 #2
[A][B][C][D]
I want #2. Is there some database option that I'm missing or something? Any
help would be appreciated. Thanks.SELECT @.@.version from each server? Might be different builds.
For me:
DECLARE @.temp VARCHAR(500)
SET @.temp = ''
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM master.dbo.sysdatabases T
SELECT @.temp
select @.@.version
SQL 2000, build 878 (Sp3a plus hotfixes):
[master][tempdb][model][msdb][pubs][Nort
hwind]
SQL 2005, IDW 13:
[master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
> Hi all, can someone please explain why this is happening:
> I execute the following:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM MyTable T
> SELECT @.temp
>
> Say [MyTable] looks like this:
> |NAME|
> --
> A
> B
> C
> D
> Here are the result from running the above code on two different SQL
> Server
> 2000 installations:
> SQL Server 2000 #1
> [D]
> SQL Server 2000 #2
> [A][B][C][D]
> I want #2. Is there some database option that I'm missing or something?
> Any
> help would be appreciated. Thanks.|||Thanks Mike. I should have posted an update, but I found this:
http://support.microsoft.com/defaul...b;EN-US;q287515
What I posted here was a simple example of my issue, my actual query
contained a little more SQL and it was on a view instead of a table. Doing
the following resolved my issue:
SELECT @.temp = @.temp + '[' + T.NAME + ']'
FROM (SELECT * FROM MyTable) T
This caused the query optimizer to sort the results of the inner query
first, then apply the concatenations.
Thanks again for your response Mike.
"Mike Epprecht (SQL MVP)" wrote:

> SELECT @.@.version from each server? Might be different builds.
> For me:
> DECLARE @.temp VARCHAR(500)
> SET @.temp = ''
> SELECT @.temp = @.temp + '[' + T.NAME + ']'
> FROM master.dbo.sysdatabases T
> SELECT @.temp
> select @.@.version
> SQL 2000, build 878 (Sp3a plus hotfixes):
> [master][tempdb][model][msdb][pubs][Nort
hwind]
> SQL 2005, IDW 13:
> [master][tempdb][model][msdb][AdventureW
orksDW][AdventureWorks]
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Treadway" <MikeTreadway@.discussions.microsoft.com> wrote in message
> news:39B330F1-41D5-4A7F-BFE0-25998D07C492@.microsoft.com...
>
>

Bit vs. Varchar?

Saw a friend of mine using a varchar field like a bit field (only storing 1
character). I told him that it would be better to change that to a bit
field, and he said, Why? What is the difference between Varchar(1) and Bit?
Thanks,
DrewDrew wrote:
> Saw a friend of mine using a varchar field like a bit field (only
> storing 1 character). I told him that it would be better to change
> that to a bit field, and he said, Why? What is the difference
> between Varchar(1) and Bit?
> Thanks,
> Drew
There is no ANSI standard for a bit column. It's a SQL Server invention.
Bit columns only take up 1 bit of storage (giving you the ability to
store as many as 8 bit columns in a single byte). I personally don't
mind them, but there are arguments for using a char(1) with a check
contraints for Y/N, 0/1, etc.
I would not use a varchar because of the added overhead. Use a char(1)
or a bit.
David Gugick
Imceda Software
www.imceda.com|||A bit can store only 0 or 1.
If storing just a single character and it will never be null best to
use char(1) instead of varchar(1). The reason is less data space is
used. Same reason applies to bit.
Tim S|||Correction bit can also store NULL.|||I prefer to use a TINYINT for this. It only takes up one byte just like a
bit or char(1) but an hold up to 255 values even if you only want to use 0
or 1. It's cleaner than a bit and more versatile.
Andrew J. Kelly SQL MVP
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
> Saw a friend of mine using a varchar field like a bit field (only storing
> 1 character). I told him that it would be better to change that to a bit
> field, and he said, Why? What is the difference between Varchar(1) and
> Bit?
> Thanks,
> Drew
>|||Thanks for everyone's reply.
Thanks,
Drew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OKaeVLGPFHA.2348@.tk2msftngp13.phx.gbl...
>I prefer to use a TINYINT for this. It only takes up one byte just like a
>bit or char(1) but an hold up to 255 values even if you only want to use 0
>or 1. It's cleaner than a bit and more versatile.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
>|||>> Saw a friend of mine using a varchar field [sic] like a bit field
[sic] (only storing 1
character). I told him that it would be better to change that to a bit
field [sic] , and he said, Why? What is the difference between
Varchar(1) and Bit? <<
Rows are not records; columns are not fields and BIT is not a Standard
SQL datatype. in fact, it makes no sense in a high level language.
That is what assembly language programmers use in the hardware and not
what SQL programmers have in an abstract darta model. VARCHAR(1) also
makes no sense; use CHAR(1) instead.
What a good SQL porgrammers does is design encoding schemes. We do not
write code which depends on flags. And it is hard to design encoding
schemes.|||>> Correction bit can also store NULL. <<
No, bit can be only one or zero; look it up in any computer science
book. On the other hand BIT, the proprietary datatype in SQL Server,
can store 1, 0 or NULL. Kinda screws up portability, don't it?|||>> I prefer to use a TINYINT for this. <<
Another proprietary datatype, but at least it has room for more codes
and can port to INTEGER.|||I think I have my answer. The spec simply calls for smallint
to be smaller than int. I guess I still don't see why they
didn't add a further designation that stated that tinyint
was smaller than smallint. It's already the case that
without knowing the given implementation's bounds on int and
smallint, you can't logically decide when to use one over
the other.
Thomas
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:utGU6aHPFHA.2132@.TK2MSFTNGP14.phx.gbl...
> That's curious. What is the logic behind having a 2-byte
> integer (smallint) and a 4-byte integer (integer) but not
> a 1-byte integer (SQL's tinyint)?
> Thomas
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1112987344.909087.50090@.g14g2000cwa.googlegroups.com...
>