how to store 2 checkbox values in separate lines i

Hello,

I created a checkbox that allows users to select more than one boxes and use Access db to store values. How do I make 2 lines in Access table field if a user select 2 boxes.

Here is my code:
<input type="checkbox" name="a" id="a" value="Asian" />Asian<BR />
<input type="checkbox" name="a" id="b" value="Latino" />Latino<BR />
<input type="checkbox" name="a" id="c" value="White" />White<BR />

If a user check 2 first boxes, I like to have a value in the field to be:
Asian
Latino

Instead of Asian, Latino

How do we do that?

thanks.

Not sure about parsing the values out in asp (I'm a PHP guy), but here's how to create an array for checkbox values:


<input type="checkbox" name="a[]" id="a" value="Asian" />Asian<BR />
<input type="checkbox" name="a[]" id="b" value="Latino" />Latino<BR />
<input type="checkbox" name="a[]" id="c" value="White" />White<BR />


then you will have:

array a(
[0] => Asian
[1] => Latino
[2] => White
)

You can replace the comma with newline.
dim races
races = Request.Form("a")
races = Replace(races, ", ", vbCrLf)
Then if you want to display it back to the page as text, you need to enclose it with <pre></pre> tags to preserve the newlines.
<pre>
<%=rs("fieldname")%>
</pre>

I tried exactly this code but don't work. I gets ONLY one 1st value to store in the db field.
dim races
races = Request.Form("a")
races = Replace(races, ", ", vbCrLf)

I think my question is not so clear.
On my checkbox values, there are:
Yes, Asian.
No, Not Asian
No, Hot Latino

Can you help?
thanks.

I think my question is not so clear.
On my checkbox values, there are:
Yes, Asian.
No, Not Asian
No, Hot Latino

Can you help?
thanks.
Your initial requirement was clear until you said this.
You mean those are exactly the values of the checkboxes? The values have commas in it? Like this?
value="Yes, Asian"
If so, then it would be difficult to distinguish the values of each checkbox when they are submitted. The value could be something like this if all are checked.

Yes, Asian, No, Not Asian, No, Hot Latino

That's right. The values are like that in the table fields. That's why I 'd like to ask if there is a way to to break the each checkbox value in seperate links if multiple checkboxes are selected???

Thanks.

Have you thought about changing the values of your checkboxes to be integers rather than text?


<input type="checkbox" name="a" id="a" value="1" />Yes, Asian<BR />
<input type="checkbox" name="a" id="b" value="2" />No, Latino<BR />
<input type="checkbox" name="a" id="c" value="3" />No, White<BR />


This would mean A) the code above would work for you, and B) the amount of data stored would be significantly lower. You can even make these dynamic if you wanted to by placing the data into a separate table.

It's a great idea. So, I need to create a new table and join with the main table. And, the values which are stored in the main table
will be something like this (below) if multiple boxes are selected, am I right?
ID Race Field
-- --------
1 Yes, Asian
No, White
2 No, Latino
3 No, White
No, Latino

And, the table structure is like this:
ID Race
-- ----
1 Yes, Asian
2 No, Latino
3 No, White

Thanks.

Sort of --

The values that would be stored in the main table will only be integers. Only by doing a SELECT...JOIN... would you be able to see the values.

So the main table example would actually look like this:


ID Race Field
-- --------
1 1, 3
2 2
3 3, 2


If you did the REPLACE() function in your code, then it would look like this:


ID Race Field
-- --------
1 1
3
2 2
3 3
2




Your table structure is correct.

HTH!

Is there any way to see the values directly from the main table?
Thanks.

When doing a simple select -- no. You would have to do a JOIN statement.

SELECT val1,val2,Race.Name FROM table1 t1 JOIN tableRace Race ON t1.RaceID = t2.RaceID

That's the only downside to storing only integers. However, you get really good at writing JOIN statements really quickly, too...

Also, you may run into trouble with the carriage return when pulling values, but that is what Server-Side code, VIEWs, and Queries are for.

HTH!

I tried to create a new table in ms access like this:
ID autonumber
Race TEXT

ID Race
-- ----
1 Yes, Asian
2 No, Latino
3 No, White

And change the Race field on the main table to be Number data type.

When I run the page and got this error:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

Should I use all text for fields?

Thanks.

Your CREATE TABLE is correct, however, because you are going to use commas and/or carriage returns in that field, you need to leave it as TEXT, or set it to a VARCHAR(8).

Also, make sure that you change your ASP page to do the LOOP for building the INPUT boxes --

<%
Set rs = Server.CreateObject("ADODB.Recordset")
Set Conn = Server.CreateObject("ADODB.Connection")
'
rs.open "SELECT ID,Name FROM table_Race ORDER BY NAME DESC", conn
rs.movefirst
while not rs.eof
response.write("<input type=""checkbox"" value=""" & rs("ID") & """>" & rs("Name"))
rs.movenext
wend
rs.close
Conn.close
'
Set rs = nothing
Set conn = nothing
%>

Hello,
I am talking about inserting data in the db table.
I worked to insert the data. But ONLY the first value was inserted which is "1", even I check 2 boxes.

Here is my code:

dim races
races = Request.Form("a")
races = Replace(races, ", ", vbCrLf)
.....

<input type="checkbox" name="a" id="a" value="1" />Yes, Asian<BR />
<input type="checkbox" name="a" id="b" value="2" />No, Latino<BR />
<input type="checkbox" name="a" id="c" value="3" />No, White<BR />

....


Thanks.

Did you set the field back to a TEXT (or VARCHAR) field?

If you did and you are still getting errors, try:

Response.Write("Form Value: " & request.form("a") & " -- " & races)
Response.flush

Let me know what your page replies with and the exact error code, line, etc.

Hello,
When I tried the code below and It worked to insert the data. But ONLY the first value was inserted which is "1", even I check 2 boxes.

Here is my code:

dim races
races = Request.Form("a")
races = Replace(races, ", ", vbCrLf)
.....

<input type="checkbox" name="a" id="a" value="1" />Yes, Asian<BR />
<input type="checkbox" name="a" id="b" value="2" />No, Latino<BR />
<input type="checkbox" name="a" id="c" value="3" />No, White<BR />

....


Thanks.

Please read my last post and let me know what you receive.

Finally, it worked.
I have values like 1, 3 or 2, 3 on the fields.
I use Access to create a query to display values that break 1 and 3, instead of comma.

1 Yes, Asian
No, Latino
2 No, White

Can you please show me how to do that? I mean, on the query, I dont' need to view on web page.

thansk.

Since you are using Access, I am not sure that we can do this easily, as with Commas and Carriage Returns, you need to use the SPLIT function to get the data out.

Question (trying to gather data, not to question your intentions): Why do you want to view it straight from the database? Why not write a quick Admin page or Access report that does this for you?

I think it is too hard to store data this way because there is no way to manipulate the data later.
For example: One field have multiple values like 2, 3, 5 and another row has 1, 2, how can I run a query for these values?
I am thinking about storing each value from the checkbox in different field?
Race1 Race2 Race3
1 3
2 3

What do you think I should do?

Thanks.

Although I thank you for your compliment of asking my opinion, I think that it depends on your application, and more importantly, what you are comfortable with doing.

I store data in comma delimited format for checkboxes all the time and have never had an issue in retrieving or editing data. I use either FIND_IN_SET() or InStr() to search for data within strings. FIND_IN_SET is a standard SQL function, but I am not sure if Access has it.


QUERY: SELECT * FROM table1 WHERE InStr(Race,"1") <> 0

OR

QUERY: SELECT * FROM table1 WHERE FIND_IN_SET(1,Race) <> 0


Since you don't have more than 3 at the moment, you are OK with these simple queries. If you get more than 10 choices however, you will need to think about how to parse out 1, 10, 11, etc. from the InStr() function.

FIND_IN_SET will work perfectly no matter what since it is a binary comparison.

To get back on topic now:

If you feel more comfortable with setting your RACE fields to separate columns, by all means do so. Just remember, though, that the data manipulation would have to be done using Arrays and Request.Form()s, rather than just 1 statement and 1 field.

For example, using an array:

<%
if request.form("a") <> "" then
arrayCheckboxes = split(request.form(a),",")
'
rs.Race1 = arrayCheckboxes(0)
rs.Race2 = arrayCheckboxes(1)
rs.Race3 = arrayCheckboxes(2)
'
rs.update
%>
...
..
.
<input type="checkbox" name="a" id="a" value="1" />Yes, Asian<BR />
<input type="checkbox" name="a" id="b" value="2" />No, Latino<BR />
<input type="checkbox" name="a" id="c" value="3" />No, White<BR />
.
..
...


Issue with doing the above?? If you ever change the number of Race Fields in your application, you will have to edit this again.

Another way of doing it (storing data as comma-delimited):


<%
if request.form("a") <> "" then
rs.Race = request.form("a")
rs.update
end if
%>
...
...


To edit the data:


<%
'
'Retreive Values from Database and store in Array
rs.open "SELECT Race FROM table1", conn
arrayCheckboxes = Split(rs2("RaceID"),",")
rs.close
'
'
'Get Checkbox values
rs.open "SELECT RaceID,Race FROM table2", conn
'Write out data
response.write("<input type=""checkbox"" value=""" & rs("RaceID") & """ name=""a""")
'
'Compare values from database
'
for x=0 to ubound(arrayCheckboxes)
if rs2("RaceID") = arrayCheckboxes(x)
response.write(" checked")
Exit For
end if
next
'
'finish out the checkbox
'
response.write(">" & rs("Race") & vbCrLf)
rs.close
%>


This way, everything is dynamic and you never have to worry about it. You can make as many edits as you want and you never have to update this.

I hope you find this useful, and don't hesitate to ask if you have any other questions.

Thanks. I think I go with the way to store data from checkboxes to the same field name. I don't need to display data on the web page. I only need to view data from these multiple checkboxes in separate lines, instead of commas (like now I currently have).
thanks.

I am not very good at this, so I think will use ONLY one table to store data.
I have questions:
1. If 2 checkboxes are checked, then, can these checked boxes be inserted in 2 fields? if so, how do I modify the form name, currently there is only ONE checkbox name?

Thanks again.
Jenny.

Yes, they can, but it will take a little bit or array manipulation to get it to do so, OR you can name each checkbox something different.


<input type="checkbox" name="Asian" id="a" value="1" />Yes, Asian<BR />
<input type="checkbox" name="Latino" id="b" value="1" />No, Latino<BR />
<input type="checkbox" name="White" id="c" value="1" />No, White<BR />


With the code above, you can enter in their Race information by a simple YES or NO (1 or 0).

I would go with that way above array manipulation.

It worked.
Thanks.