Database design - ASP.NET MsAccess

[erichn]

I am stuck to know how to design a database for an asp.net 2.0 project.

I have a table with many fields. In addition to all the other fields (mainly text fields) I would like to keep track of 4 values: alt, sy, LaF, ord.
Any combination of these values is possible. For most records, none of these values would be applicable, meaning nothing should display.

In most queries I would like to display simply these values, seperated by a space, preferably under one heading.
..... MyCodes
..... alt
..... sy ord.
..... LaF ord.
..... alt LaF
..... LaF
..... alt LaF ord. sy
.....
..... ord.

But in my input form, I would like to use checkboxes for these fields.

I never need to make a selection on these fields, they are simply additional information to be displayed.

I fiddled with 4 boolean fields - which allowed me to set the values into the database from the form. However I could not find a way to display them the way I want to (see above).

I also fiddled with 4 different text fields, each one always using the same value in the field. e.g. FieldAlt = "alt". This allowed me to display the values the way I want - but I was unable to update the fields from checkboxes. ("String was not recognized as a valid Boolean." error messages)

Anybody has some suggestions?
Thanks

[alc6379]

if those are the only codes that you'll be using, couldn't you just use string concatenation on it? Ie, if you had one that was "alt LaF" would could just input that in as a string.

From that standpoint, when you updated it, you would have to make sure that you updated ALL of it, ie, if you wanted to change "alt LaF" to "alt sy ord." ,you would want to make sure that each value is explicitly updated when you do it.

Does that make sense? Otherwise, you'd need to set up a normalized database, with a row representing each code that applied to that bit of data, joined together.

[erichn]

I have used this approach and I managed to do everything I wanted, except I was not able to use checkboxes to insert and update the fields.

So if you have a solution to that problem, it would be my preferred design as well.

Thanks

[alc6379]

you could use a set of if statements or something.

basically,

string myValues = "" //initialize the string

if (chkAlt.Checked == true)
{
       myValues = myValues + " Alt";
}

//...and so forth

Then, you'd create an OleDBCommand or SqlCommand that contained the ID. Something like:

"UPDATE tbl_values SET columnName = " + myfield + " Where ID = " + myRow;

You still need to figure out what your ID is. But, that code I just gave you is what the CommandText of your SqlCommand or OleDbCommand might look like. Then, you'd run the ExecuteNonQuery() of your command. That should commit it to the database.

[erichn]

Thanks very much for your input. I have opted for 4 boolean fields in the database and use something similar to your code
string myValues = "" //initialize the string

if (chkAlt.Checked == true)
{
myValues = myValues + " Alt";
}
to display the values in my grid control.

Works like a dream!