storing multiple values within one field

[joeyramirez]

Hi,

How to insert multiple checkbox values(from asp classic) into one column of the database(mysql)?.

Suppose if a checkbox contain 4 values,if i choose 3 fvalues,then how to insert that 3 values into a single column.

[r937]

don't do it

multiple values in a single column violates first normal form

you will find that searching for a specific value inside the column is very, very painful

[joeyramirez]

Thanks sir r937. . I'll think of another way to solve my problem.

[awasson]

Yes, you are way better off having a related table to store your checkbox values in.

So Table A is the main table with an ID for whatever record is in it and then Table B has a couple of columns, one for the ID (of the record in Table A), another for Checkbox_Value (1, 2, 3, 4).

[joeyramirez]

Can I ask you how to insert multiple row values in one SUBMIT?for example I have cbprob=1,cbprob=2,cbprob=3 and my query is
INSERT INTO tbltest (pid) VALUES ('"&Request.Form("cbprob")&"'). How am i going to insert the three values?

[joeyramirez]

Can someone teach me how to insert multiple values from a checkbox in one submit?My checkboxes are dynamic.

[awasson]

It's pretty hard to teach you how to do something when we don't really know anything about what you are trying to achieve. What database are you using, and what is the structure of the table you're inserting into? Also what does the form field html look like and are you posting (POST) or getting (GET) the results?

In the big picture, you'll need to collect all of the checkbox results from your form and then put them into some sort of array or series of variables. Then, if you're following my suggestion of using a related table for the checkbox results, you'll need to create a loop and insert each result so that there is a separate row for each checkbox.

[r937]

... you'll need to create a loop and insert each result so that there is a separate row for each checkbox.

make sure you generate only one INSERT statement, and just loop over the values, rather than generating a new INSERT statement for each loop iteration

i.e. instead of this --

INSERT INTO ... VALUES ( ... );
INSERT INTO ... VALUES ( ... );
INSERT INTO ... VALUES ( ... );
INSERT INTO ... VALUES ( ... );

do this instead --

INSERT INTO ... VALUES
( ... ),( ... ),( ... ),( ... );

[awasson]

@r937: Does that statement work with MS Access? I'm not sure what joeyramirez is using by my hunch is it might be Access.

[r937]

@r937: Does that statement work with MS Access?

nope, i don't think so

but he's using mysql (see post #1)

[Doug G]

Works in Access too

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[r937]

Works in Access too

are you sure?

i don't have msaccess to actually test this, but that documentation page you linked to says...

Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.

what i suggested above inserts multiple rows with one statement

INSERT INTO ... VALUES
( ... ),( ... ),( ... ),( ... ); -- each of these parentheses creates a row and contains multiple column values

[joeyramirez]

my page looks like this.this is test2.asp where the checkboxes are located.

<form action="test3.asp" method="post">

<%
Dim sql,rs
sql="Select * from tblproblem ORDER by pID"
Set rs=Server.CreateObject("ADODB.RECORDSET")
rs.Open sql,Conn,1,3
%>

<%While Not rs.EOF%>
<input name="cboprob" type="checkbox" value="<%=rs("pID")%>" /><%=rs("pname")%><br />
<%
rs.Movenext
Wend
Set rs=nothing
%>
<input type=submit name=submit />
</form>

and this is test3.asp where the insert query is located


<%
Dim sql,exec
sql="INSERT INTO tbltest (pid) VALUES ('"&Request.Form("cbprob")&"')"
Set exec=Conn.Execute(sql)
If err.number=0 then
Response.Write "Save Completed"
End If
Conn.Close
Set exec=nothing
%>

[awasson]

@joeyramirez: Can you show a sample of what the checkboxes look like (in HTML) as they render from that code? It looks to me like they will all have the same name (cboprob) and we need to make sure that they are unique so they can be requested and inserted into a SQL insert query.

Thanks,
Andrrew

[joeyramirez]

sitepoint.jpg



This is what it looks like.

[awasson]

Thanks, can you look at the html and find out what the name attributes are for the checkboxes? My concern is that they're all the same value in which case we need to make them unique somehow. Maybe doing something like the following:

<%
i = 0
While Not rs.EOF    
%>
<input name="cboprob-<%=i%>" type="checkbox" value="<%=rs("pID")%>" /><%=rs("pname")%><br />
<%
    rs.Movenext
    i = i+1
Wend
Set rs=nothing
%>

[joeyramirez]

<input name="cboprob" type="checkbox" value="1" />Monitor No Output<br />



<input name="cboprob" type="checkbox" value="2" />Monitor No Power<br />



<input name="cboprob" type="checkbox" value="3" />Monitor Blurred<br />



<input name="cboprob" type="checkbox" value="4" />CPU No Power<br />



<input name="cboprob" type="checkbox" value="5" />Blue Screen<br />

[awasson]

Ok, that's going to be a problem... You need to name each checkbox with a unique name otherwise when you try to request the form variable, you won't be able to distinguish between them.

In the little code snippet I posted I was just appending the variable with a number so the html would look something like this:

<input name="cboprob-0" type="checkbox" value="1" />Monitor No Output<br />

<input name="cboprob-1" type="checkbox" value="2" />Monitor No Power<br />

<input name="cboprob-2" type="checkbox" value="3" />Monitor Blurred<br />

<input name="cboprob-3" type="checkbox" value="4" />CPU No Power<br />

<input name="cboprob-4" type="checkbox" value="5" />Blue Screen<br />

Then you could get the variables on the receiving asp script as follows

<%
checkbox-0 = request("cboprob-0")
checkbox-1 = request("cboprob-1")
checkbox-2 = request("cboprob-2")
checkbox-3 = request("cboprob-3")
checkbox-4 = request("cboprob-4")

%>

Then you could create a query to insert the results sort of like this:

<%

sql="INSERT INTO tbltest (pid) VALUES ('&checkbox-0&'),('&checkbox-1&'),('&checkbox-2&'),('&checkbox-3&'),('&checkbox-4&')"

%>

This is roughly how I would do it so it will require some tweaking, particularly (checking for syntax errors I may have introduced) and adding some sort of ID to accompany the inserted cboprob values so you can relate that to a particular customer or trouble ticket.

I have to head out for a while but I will check back later.

Andrew

[joeyramirez]

Thanks Sir Andrew.I will try your code

[joeyramirez]

By the way Sir Andrew, all cboprob are accompanied by a ticket number. I'm just trying to add the checkbox values to the DB.

[siteguru]

Can't remember, but ... if multiple checkboxes have the same name, does Request.Form("cboprob") return a comma-separated string of values?

[awasson]

Can't remember, but ... if multiple checkboxes have the same name, does Request.Form("cboprob") return a comma-separated string of values?

Wow, I haven't tried that but it sure would be a cool trick if it does.

[joeyramirez]

It does say Data truncated for column 'pid' at row 1 when i try to insert multiple values for cbprob.

[joeyramirez]

How do I split this values 1, 3, 4, 5 to be inserted like INSERT INTO tbltest (pid) VALUES ('1),('3'),('4'),('5');

[awasson]

Debugging ASP can be a bit of a pain.

Comment out the the actual ASP statement that inserts the data so that you avoid the error. It's the one that starts with "Set" and then says something about the Object Recordset.

Next we want to look at what the SQL string looks like so somewhere the commented out insert statement put in:

 

Response.Write sql

Where "sql" is the SQL string that you are inserting.