Auto Checking Field - 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

Auto Checking Field

Author

Topic

bhavoo
Starting Member

5 Posts

Posted - 2007-11-21 : 11:46:03

Hi,

I have a table in one of my databases with a lot of bit(true/false) fields. I have an asp page that selects an individual student then you can tick off attributes (subjects) once they are completed. Basically a big tick list.

I want one of the fields called certificate to be selected when all of the other fields have been checked. How can I do this all on one page? I am using visual web developer express and SQL server 2005.

Here is a shortened down table. Let’s call it options.

pupilid varchar 255,
excel bit,
mindmapping bit,
word bit,
certificate bit.

So the user can check the values using the grid view I have created. Then once they are complete the field certificate automatically fills in.

Thanks,

Bhav

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-21 : 19:13:48

Make the Certificate column a computed column, so the CREATE TABLE will be something like this

CREATE TABLE #Students
(
pupilid varchar(255),
excel bit,
mindmapping bit,
word bit,
certificate AS CASE WHEN excel = 1 AND mindmapping = 1 AND word = 1 THEN 1 ELSE 0 END
)

BTW - Based on what you're trying to do I'd say that your table design is bad, you should have a second table with one row per subject because the way you have it you're going to have to change your table and the computed column every time you add a new subject.

bhavoo
Starting Member

5 Posts

Posted - 2007-11-22 : 10:44:11

Thanks snSQL i have done what you said and it works fine. I will also redesign my table becasue your point is valid. The table doesnt actually update tho. It just displays on the screen. Is there anyway of updating the tables with this value?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-22 : 15:01:25

Not sure what you mean by "updating the tables with this value"? If you mean adding a computed column to an existing table, then use an ALTER TABLE something like this:

ALTER TABLE yourtable
ADD certificate AS CASE WHEN excel = 1 AND mindmapping = 1 AND word = 1 THEN 1 ELSE 0 END

torpkevuk
Starting Member

6 Posts

Posted - 2007-11-27 : 17:30:58

Theres 2 ways of doing this, either add a trigger which checks all the other values and updates the field when they're all set, or through the code, each time a tickbox state has changed, loop through all your tickboxes, if you dont find any that are not ticked, use an Update statement to update the record for that pupilid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources