i am using access 2000 db, asp form

[sbishops]

i am using access 2000 db, asp forms and vbscript.

here is my question:

i want to get some bits of information (name, email) from a user via a form. I want to then insert this info into an access 2000 database via an asp processing page. this part i have accomplished using one table, one unique user per row, as such:

table 1:
personID (auto-generated) | name | email |
1234 jon j@r.com

now to the problem: in my form, the user will come across a multi-select listbox of colors and can choose all, none, or any number of colors that are available. i want to capture whatever he's chosen, and store those choices somewhere. it didn't make sense to store a list of colors in the first table in one field so i reasoned that i would need a second table dealing with just the colors.

i want to be able to look at this second table, see all the color choices and know that this user (whose personID has been captured from the first table) has affirmatives in some columns and negatives in the rest, like an excel spreadsheet. see? like such:

table 2:
personID | all | none | red | yellow | green |
1234 null null yes yes null


now, just to let you know, my current setup may NOT necessarily be what I need to accomplish my goal, as i know just enough to be dangerous and annoying so if you can understand my ramblings and can see what im trying to do and if you have a better way to set this thing up, I wouuld soooo appreciate it. i'm NOT so deep in that my databse cannot be changed.

Is there a way to make the default value of the <option> tag &quot;False&quot; and then make the &quot;False&quot; a &quot;True&quot; once the user has selected it?

This could work, but how do i do this? do i make the default value False in the <select> or <option> tag or do i do this in access? then, do I really have to loop thru every option or isn't there some wildcard code i could use that looks for true and then backs into the value (ie: red, green, etc.) associated with it? for instance (and you'll see my c++ background here):

Dim colorArray(50)
For i = 1 to Request.Form(&quot;color&quot.Count
if colorArray(i).Selected = &quot;True&quot;
{
INSERT INTO Table 2 &quot;TRUE&quot;
WHERE colorArray(i).Value == COLUMN_NAME;
}
Next

Does this make sense?? I am sooo sorry for my newbie-ness

Isn't there a way to test the value of the color chosen against the column header names in access? This would avoid 50 or so case statements or if loops.

For instance, if a user chooses red, green and yellow, and there are column names of red, green and yellow in my access table, isn't there a way to say: if chosen_value == column_name, then insert &quot;True&quot; ???

Please tell me theres a way to test against column names?? thank you soooo much in advance))

 

[ahmun]

Your Table design is on the right track, as far as seperating the color choices table from the main table

What you can do is set up your HTML form so that the first selection in your listbox is not among the choices for the user to pick. Then, if the user submits the form, your ASP code can check to see if the &quot;unselect&quot; option has been chosen.

<select name=&quot;optFavoriteColor&quot;>
<option selected value=&quot;FALSE&quot;>Choose a favorite Color</option>
<option value=&quot;all&quot;>All</option>
<option value=&quot;none&quot;>none</option>
etc.
.
.

Another way... perhaps use checkboxes, so the user can choose multiple colors? What if I just like Orange and Blue, but hate all other colors? Then your ASP page will process each individual checkbox and decide to set your table value to &quot;null&quot; or &quot;yes&quot; as you described.

Earnie Eng

 

[sbishops]

thank you so much-it seems like you understand what i am trying to do. now with what you said about checkboxes... i like that idea, but can i get the same result with a listbox. in reality, the user will have 50+ choices and aside from space being used, it will take time for the user to check each box. highlighting each choice in a listbox seems easier on the user, if you get my meaning...

so can i just grab, somehow, the highlighted choices and insert true or yes into those fields in the database table, and have the unselected choices go to null? if so, could you help me out with syntax, i am newbie, if not already apparent

thanks so much.

 

[ahmun]

Are you talking about a list box that allows multiple select?

Earnie Eng

 

[sbishops]

yes and thank you for reading my post

 

[ahmun]

Here is a quick and crude solution to that idea, but you would have to play around with it... The idea is to build an SQL UPDATE query from the user's selection and update the database. Of course, you would need to make sure there is an entry for that specific user first, before running the update query. If you don't know how to execute SQL queries in ASP, try searhing this forums site for some examples. I learned a lot just browsing through the FAQs and forums myself. Also,

http://www.devguru.com

is my #1 reference on the web for syntax questions of all the programming languages I use.

Set up your HTML list box somthing like this:

<select name=&quot;optFavoriteColor&quot;>
<option value=&quot;blnAll&quot;>All</option>
<option value=&quot;blnNone&quot;>none</option>
<option value=&quot;blnRed&quot;>Red</option>
<option value=&quot;blnGreen&quot;>Green</option>
<option value=&quot;blnBlue&quot;>Blue</option>
etc.
.
.

Use the actual field names of the color preferences table for the values of each option. That way, when we process the selections, we don't have to do any translation of what the user selected to determine which fields in the database need updating.

Once the user clicks submit, the action page should have some code like thus:

<%
  dim strSelections, arySelections 
  dim strItem, sSQL

  'Gather the user's input into a variable
  strSelections = request.form(&quot;optFavoriteColor&quot;)

  'Split up the variables into an array with the 
  'split() function
  arySelections = split(strSelections)

  'Now that you have the names of the colors/selections
  'from the user, you can use that to create the SQL
  'statement:

  'loop through the array and construct the SET portion 
  'of the UPDATE query.
  strSET = &quot;&quot;
  for each strItem in arySelections
    strSET = strSET & strItem & &quot; = 'yes',&quot;
  next strItem

  'remove the trailing comma inserted in the loop
  'and add a space
  strSET = Left(strSET, (Len(strSET) - 1) & &quot; &quot;
  
  'assemble the full SQL statement
  sSQL = &quot;UPDATE tblColorPreference SET &quot; & 
  sSQL = sSQL & strSET
  sSQL = sSQL & &quot;WHERE PersonID = &quot; & intPersonID & &quot;;&quot;

  'here you would have a database Connection set up
  '(this example does not show how to do that)
  myConnection.execute sSQL

%>

Earnie Eng

 

[sbishops]

thank you sooo much, you have pointed me in the right direction and i'm going to try it. wish me luck and please keep an eye out for my posts as they will undoubtedly related to this. i've almost got it now and i couldn't do it without the help and patience of people like you thank you sooo much again