Handling bit data type in ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Handling bit data type in ASP

Author

Topic

jfewer
Starting Member

1 Post

Posted - 2002-10-02 : 17:53:57

So I have a SQL 2000 server DB which we use to track user information. One column I have created is called "active", Data Type is bit. So if an employee is currently employed we enter "1", if they leave they get "0". In the ASP page I use to modify the records, it displays as "True" for active(bit value 1) and "False" for inactive(bit value 0). If we submit an UPDATE from an ASP form without setting this field to "1" or "0" it tells us:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'True' to a column of data type bit.

How do I get the ASP to display the bit value 1 or 0, or accept the displayed value of True/False?

Any Ideas?

John

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-10-02 : 19:10:08

Well, you could just use a checkbox instead of true and false...but besides this, I assume you've already got some code to handle other values to be stored...eg NULLS, special characters, values with ' etc etc, so just expand it so that it the value is "True" it returns 1 etc...

Maybe I'm missing something? Let me know

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-10-02 : 19:32:12

Hi

I don't like using bit columns with ADO for this reason. There are a few things you can do :
1. use an int or a tinyint. Yes, they will take up some more space but they will get evaluated right.
2. Convert them to an int in your ASP page, i.e. cint(false) and cint(true), except that returns -1 for true. So maybe something like :



function returnIntFromBool(bValue)
     returnIntFromBool = cint(bValue) * -1
end function

Hope that helps

Damian

KrustyDeKlown
Starting Member

1 Post

Posted - 2002-10-04 : 16:02:20

One you can not use a checkbox. Checkboxes always return "true" per say, in other words the value only comes through if checked, so unless you are going to implement a bunch of DHTML to change to the opposite value upon "checking" then I would not implement that strategy.

However, you can use a radio button, one would be True and the other False. The trick to this is to pass 1 or 0 as the value of the button chosen (respectively). This eliminates your dilemma for inserting. However, when pulling bit values from the DB they return boolean values in ASP and not 1 or 0 but rather true and false. So you have to check and see which of the 2 it returns to mark the correct radio button.

Hope that helps!

KrustyDeKlown

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources