Boolean Data Type not available (workaround for checkbox.checked storing?)

I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not:

<asp:CheckBoxID="CheckBox2"runat="server"Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>'Enabled="False"/>

This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display.

I checked the SQL statement, and sure enough, it has the

UpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @.Shipping... WHERE [PartID] = @.original_PartID

After fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.

Any insight as to how to get this to work is much appreciated. Thanks in advance.

I don't know if this will work declaratively but your problem is ANSI SQL Boolean is three valued True/False/Null the reason ANSI SQL is called three valued logic. The links below will show how to use Nullable types of FCL(framework class library) 2.0 to solve your problem. Hope this helps.

http://www.codeproject.com/csharp/c__20_nullable_types.asp

http://www.c-sharpcorner.com/UploadFile/PashuSX/NullableTypes04282006114548AM/NullableTypes.aspx?ArticleID=b28a5114-a92a-4ced-a23c-06d8a29de6e4

|||

I found this thread:http://forums.asp.net/thread/1092916.aspx and after reading it, I've changed the data type in the database to Bit. This has significantly helped my problem .

BUT I have the checkbox in normal mode displaying perfectly fine. However, when I click edit, there is a textbox that displays either True or False (True is 1 and False is 0 in the database). If I change True to 0 or False to 1, it works. HOWEVER I want it to display a textbox. When I delete the textbox in the edit template, replace it with a checkbox and then set the databinding to the shipping field, I can click edit and the box appears just fine, but when I change it and click update, I get this Error:Syntax error converting the nvarchar value 'False' to a column of data type bit.Which to me means that it is trying to post the checkbox.checked ='s value, not just checkbox.checked which is stored as a bit in the database.

How/Where can I change this?

|||Figured it out! Turns out the auto generated update statement set the paramenter to

<asp:ParameterName="Shipping"Type="String"/>

So I took out the Type="String" and it fixed my problem.

Thanks.

|||I am glad to see your problem is resolved.

blobs?

I have searched high and low for this answer, but still to no avail...

Does anyone have a reliable way of storing serialized data into a table which does not use direct SQL on the client side? I am using Stored Procedures and simply need to be able to read out of the table the object written in.

Inserting appears fine, but reading only seems to come back with 16 bytes of data and I just can't figure out why. Whats the recommended way of doing this?

Matt.You're not reading the byte array properly. See if this article helps:

http://www.dotnetbips.com/displayarticle.aspx?id=60|||just thought I would let everyone know that I solved my serialization problem...

...I was creating a SqlParameter object with a SqlDbType.Image and giving it a size of 16, which meant every time I performed a DB op with that entry I got no more than 16 bytes of data back. very annoying.

anyway, Iv'e removed that from the new SqlParameter() and it works.

I had even read in various places that it's not possible to do serialization to the db using things like SQL, but I may have been just very paranoid by then...

cheers folks,
m.

Blobs in SQL Server

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...Connie (csawyer@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

I have a very quick sketch for this on
http://www.sommarskog.se/blobload.txt.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 15, 4:26 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Connie (csaw...@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

>
I have a very quick sketch for this onhttp://www.sommarskog.se/blobload.txt.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Here is some good information that I found in my search to complete
the above task, I am posting as this may help others working with
blobs:
In this article I want to show, how you can copy a single text or
image value into or out of SQL Server with textcopy.exe utility. You
can find this utility in the directory containing the standard SQL
Server EXE files (C:\Mssql\Binn by default for SQL Server 6.5 and C:
\Mssql7\Binn by default for SQL Server 7.0).

The Textcopy utility is not described in SQL Server Books Online, but
you can get its description by typing textcopy /? from the command
prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The
value
is a specified text or image 'column' of a single row (specified by
the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If
the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is
not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not
specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the
text or
image data. If 'database' is not specified, the
default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE
keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from
'file'.
/O Copy text or image value out of SQL Server into
'file'.
/K chunksize Size of the data transfer buffer in bytes.
Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of
textcopy utility:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (30),
@.whereclause varchar (40),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'textcopy /S ' + @.srvname +
' /U ' + @.login +
' /P ' + @.password +
' /D ' + @.dbname +
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause +
'" /F ' + @.filename +
' /' + @.direction
EXEC master..xp_cmdshell @.exec_str

This is the example to copy image into SQL Server database pubs, table
pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @.srvname = 'ServerName',
@.login = 'Login',
@.password = 'Password',
@.dbname = 'pubs',
@.tbname = 'pub_info',
@.colname = 'logo',
@.filename = 'c:\picture.bmp',
@.whereclause = " WHERE pub_id='0736' ",
@.direction = 'I'

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.
This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:

> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>
|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/...ID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...[vbcol=seagreen]
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:

> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the imag
es
> as blobs or to store them as file system objects with a referral pointer i
n
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:
> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
> > Can anyone tell me what the pros/cons are of using blobs in sql?
> >
> > We are looking at scanning certain paperwork and storing it with the
> > appropriate records in our database. The two options are to store the images
> > as blobs or to store them as file system objects with a referral pointer in
> > the db.
> >
> > I have heard a number of different people say that storing blobs is
> > inherently evil but they haven't explained why that is.
> >
> > Any help will be appreciated.
> >
> >|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/wrinfo/wrinfo.php?search_type=SNPID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:
>> This is one of those topics that has been debated countless times, and
>> there
>> is no universal truth.
>> Storing in the database
>> -transactional integrity
>> -maintenance simplicity
>> Storing in the filesystem
>> -performance
>> -efficiency
>> -accessibility
>> "Jawallaby" wrote:
>> > Can anyone tell me what the pros/cons are of using blobs in sql?
>> >
>> > We are looking at scanning certain paperwork and storing it with the
>> > appropriate records in our database. The two options are to store the
>> > images
>> > as blobs or to store them as file system objects with a referral
>> > pointer in
>> > the db.
>> >
>> > I have heard a number of different people say that storing blobs is
>> > inherently evil but they haven't explained why that is.
>> >
>> > Any help will be appreciated.
>> >
>> >

BLOB

Hello,
I am looking into storing images into SQL and want to use BLOB which I
understand to be the best method. Can someone supply me with a simple
example of how to import a file into the database?
Thanks in advance,
John
Well it is amazing what you find after you post. After much searching I
found it on my own. For anyone else who may have wanted to know how to
populate a image type, there is an executable that will do it called
TextCopy.exe. A sample command line is below.
C:\MyDocs\My
Pictures>textcopy -I -Usa -PMyPassword -SMyServer -DMyDatabase -TMyTable -CI
mageColumn -W"where ID = 3" -Fsample.jpg /Z
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: MyServer
debug: Login: sa
debug: Password: MyPassword
debug: Database: MyDatabase
debug: Table: MyTable
debug: Column: ImageColumn
debug: Where clause: where ID = 3
debug: File: sample.jpg
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'MyServer' Message 5701: Changed database context to 'master'.
SQL Server 'MyServer' Message 5701: Changed database context to
'MyDatabase'. (Concerning line 1)
debug: Query: select ImageColumn from MyTable where ID = 3
debug: File 'sample.jpg' opened for read
debug: File is 9894 bytes long
debug: Read 4096 bytes from file
debug: Read 4096 bytes from file
debug: Read 1702 bytes from file
debug: File closed
Data copied into SQL Server image column from file 'sample.jpg'.
The other key I found is that the existing column must not be null. I
resolved this by running the following.
INSERT INTO [dbo].[MyTable] VALUES ('3', 0x0 )
If you don't do this you will get the following error when you run in debug
(-Z)
ERROR: Text or image pointer and timestamp retrieval failed.
There is also a stored procedure if you would like it let me know and I can
post that as well.
-John
"John A. Curry" <JohnCurry@.Captaris.com> wrote in message
news:uzYznuiXEHA.2408@.tk2msftngp13.phx.gbl...
> Hello,
> I am looking into storing images into SQL and want to use BLOB which I
> understand to be the best method. Can someone supply me with a simple
> example of how to import a file into the database?
> Thanks in advance,
> John
>

BLOB

Hello,
I am looking into storing images into SQL and want to use BLOB which I
understand to be the best method. Can someone supply me with a simple
example of how to import a file into the database?
Thanks in advance,
John
Well it is amazing what you find after you post. After much searching I
found it on my own. For anyone else who may have wanted to know how to
populate a image type, there is an executable that will do it called
TextCopy.exe. A sample command line is below.
C:\MyDocs\My
Pictures>textcopy -I -Usa -PMyPassword -SMyServer -DMyDatabase -TMyTable -CI
mageColumn -W"where ID = 3" -Fsample.jpg /Z
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: MyServer
debug: Login: sa
debug: Password: MyPassword
debug: Database: MyDatabase
debug: Table: MyTable
debug: Column: ImageColumn
debug: Where clause: where ID = 3
debug: File: sample.jpg
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'MyServer' Message 5701: Changed database context to 'master'.
SQL Server 'MyServer' Message 5701: Changed database context to
'MyDatabase'. (Concerning line 1)
debug: Query: select ImageColumn from MyTable where ID = 3
debug: File 'sample.jpg' opened for read
debug: File is 9894 bytes long
debug: Read 4096 bytes from file
debug: Read 4096 bytes from file
debug: Read 1702 bytes from file
debug: File closed
Data copied into SQL Server image column from file 'sample.jpg'.
The other key I found is that the existing column must not be null. I
resolved this by running the following.
INSERT INTO [dbo].[MyTable] VALUES ('3', 0x0 )
If you don't do this you will get the following error when you run in debug
(-Z)
ERROR: Text or image pointer and timestamp retrieval failed.
There is also a stored procedure if you would like it let me know and I can
post that as well.
-John
"John A. Curry" <JohnCurry@.Captaris.com> wrote in message
news:uzYznuiXEHA.2408@.tk2msftngp13.phx.gbl...
> Hello,
> I am looking into storing images into SQL and want to use BLOB which I
> understand to be the best method. Can someone supply me with a simple
> example of how to import a file into the database?
> Thanks in advance,
> John
>
|||Hi John:
Can you please tel me how I can store documents like word and Excel to the SQL Server Database? If you can email me at doctor27_in@.yahoo.com, I would highly appriciate it.
Thanks,
Nikunj

Quote:

Originally posted by John A. Curry
Well it is amazing what you find after you post. After much searching I
found it on my own. For anyone else who may have wanted to know how to
populate a image type, there is an executable that will do it called
TextCopy.exe. A sample command line is below.
C:\MyDocs\My
Pictures>textcopy -I -Usa -PMyPassword -SMyServer -DMyDatabase -TMyTable -CI
mageColumn -W"where ID = 3" -Fsample.jpg /Z
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: MyServer
debug: Login: sa
debug: Password: MyPassword
debug: Database: MyDatabase
debug: Table: MyTable
debug: Column: ImageColumn
debug: Where clause: where ID = 3
debug: File: sample.jpg
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'MyServer' Message 5701: Changed database context to 'master'.
SQL Server 'MyServer' Message 5701: Changed database context to
'MyDatabase'. (Concerning line 1)
debug: Query: select ImageColumn from MyTable where ID = 3
debug: File 'sample.jpg' opened for read
debug: File is 9894 bytes long
debug: Read 4096 bytes from file
debug: Read 4096 bytes from file
debug: Read 1702 bytes from file
debug: File closed
Data copied into SQL Server image column from file 'sample.jpg'.
The other key I found is that the existing column must not be null. I
resolved this by running the following.
INSERT INTO [dbo].[MyTable] VALUES ('3', 0x0 )
If you don't do this you will get the following error when you run in debug
(-Z)
ERROR: Text or image pointer and timestamp retrieval failed.
There is also a stored procedure if you would like it let me know and I can
post that as well.
-John
"John A. Curry" <JohnCurry@.Captaris.com> wrote in message
news:uzYznuiXEHA.2408@.tk2msftngp13.phx.gbl...
> Hello,
> I am looking into storing images into SQL and want to use BLOB which I
> understand to be the best method. Can someone supply me with a simple
> example of how to import a file into the database?
> Thanks in advance,
> John
>

BitArray to binary

I want to store a binary string in SqlServer from VB .NET through a
stored procedure. I have been storing the binary data in a BitArray
which I am not set on by any means. When I pass the BitArray into a
stored procedure, which is looking for a variable of type binary, it
throws the following: "Object must implement IConvertible." BitArray
obviously does not implement IConvertable.

If anyone has a good way of passing binary data into a stored
procedure through a parameter array I'd appreciate it."Ramzi Abboud" <ramziabb@.gmail.com> wrote in message
news:a5617e65.0410141217.21e48d0@.posting.google.co m...
>I want to store a binary string in SqlServer from VB .NET through a
> stored procedure. I have been storing the binary data in a BitArray
> which I am not set on by any means. When I pass the BitArray into a
> stored procedure, which is looking for a variable of type binary, it
> throws the following: "Object must implement IConvertible." BitArray
> obviously does not implement IConvertable.
> If anyone has a good way of passing binary data into a stored
> procedure through a parameter array I'd appreciate it.

You might get a better answer by posting in a .NET or VB group. I don't know
what a BitArray is, but you can use the ADO Command object to execute stored
procedures with correctly typed parameters, which might be another
possiblity for you.

Simon

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...
>