SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 46
-
Jan 9, 2012, 21:55 #1
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
storing multiple values within one field
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.
-
Jan 10, 2012, 02:04 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 10, 2012, 02:21 #3
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks sir r937.
. I'll think of another way to solve my problem.
-
Jan 10, 2012, 13:57 #4
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
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).Andrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 10, 2012, 18:36 #5
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jan 11, 2012, 02:21 #6
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
how to insert multiple checkbox values in database
Can someone teach me how to insert multiple values from a checkbox in one submit?My checkboxes are dynamic.
-
Jan 11, 2012, 11:42 #7
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
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.Andrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 11, 2012, 11:58 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
( ... ),( ... ),( ... ),( ... );
-
Jan 11, 2012, 12:54 #9
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
@r937: Does that statement work with MS Access? I'm not sure what joeyramirez is using by my hunch is it might be Access.
Andrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 11, 2012, 15:19 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jan 11, 2012, 17:00 #11
- Join Date
- Apr 2009
- Posts
- 359
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Works in Access too
http://msdn.microsoft.com/en-us/libr...ffice.12).aspxDoug G
=====
"If you ain't the lead dog, the view is always the same - Anon
-
Jan 11, 2012, 17:18 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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.
INSERT INTO ... VALUES
( ... ),( ... ),( ... ),( ... ); -- each of these parentheses creates a row and contains multiple column values
-
Jan 11, 2012, 18:17 #13
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
%>
-
Jan 11, 2012, 18:37 #14
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
@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,
AndrrewAndrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 11, 2012, 18:57 #15
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
sitepoint.jpg
This is what it looks like.
-
Jan 11, 2012, 19:36 #16
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
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:
Code:<% 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 %>
Andrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 11, 2012, 19:47 #17
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
<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 />
-
Jan 11, 2012, 19:58 #18
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
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:
HTML Code:<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
Code:<% 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") %>
Code:<% sql="INSERT INTO tbltest (pid) VALUES ('&checkbox-0&'),('&checkbox-1&'),('&checkbox-2&'),('&checkbox-3&'),('&checkbox-4&')" %>
I have to head out for a while but I will check back later.
AndrewAndrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 11, 2012, 20:47 #19
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Sir Andrew.I will try your code
-
Jan 11, 2012, 22:10 #20
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 14, 2012, 10:32 #21
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Can't remember, but ... if multiple checkboxes have the same name, does Request.Form("cboprob") return a comma-separated string of values?
-
Jan 14, 2012, 14:01 #22
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
Andrew Wasson | www.lunadesign.org
Principal / Internet Development
-
Jan 15, 2012, 19:14 #23
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
It does say Data truncated for column 'pid' at row 1 when i try to insert multiple values for cbprob.
-
Jan 16, 2012, 01:09 #24
- Join Date
- Nov 2011
- Location
- Cabanatuan City, Philippines
- Posts
- 56
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How do I split this values 1, 3, 4, 5 to be inserted like INSERT INTO tbltest (pid) VALUES ('1),('3'),('4'),('5');
-
Jan 16, 2012, 01:54 #25
- Join Date
- Oct 2001
- Location
- Vancouver BC Canada
- Posts
- 2,037
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
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:
Code:Response.Write sql
Andrew Wasson | www.lunadesign.org
Principal / Internet Development
Bookmarks