Inserting Multiple Checkbox into DB

[bruticus]

Here's the scenario: I have checkboxes that looks like this


<input type="checkbox" name="A" value="blue">
<input type="checkbox" name="A" value="yellow">
<input type="checkbox" name="A" value="red">
<input type="checkbox" name="A" value="green">
<input type="checkbox" name="A" value="orange">

...the list goes on


How do i save it to the Access DB using the "FOR Each" statement?

[buntine]

Do you want to save all the checkbox values in the DB? Or only the ones which have been selected?

Regards.

[bruticus]

only once that are selected... :)

[Ribeyed]

Hi,slight over sight there guys, basic HTML, checkboxes have unique names only groups of radio buttons have the same name. Give all the checkboxes different names, now only the ones that are checked will be submitted. You will need some validation code on the page where you request and send to database to check for checkboxes that haven't been checked.

[bruticus]

the reason i named all the checkboxes the same is because i'm using a validation script to check at least one checkbox is "checked" before submission.

And because of that i'm having difficulty on using the "for each" statement to save into the DB... :(

[Ribeyed]

well if your not wanting to take my advice you will need to post some code for us to work from.

[buntine]

Ok, try something like this:

ASP code:

<%

dim conn
dim sql_query
dim arrCheck
arrCheck = Split(request.form("A"), ",")

conn = server.createObject("ADODB.connection")
conn.open(YourConnectionString)

sql_query = "INSERT INTO tableName (fieldName) "
sql_query = sql_query & "VALUES ('"

for i = 0 to UBound(arrCheck) - 1
sql_query = sql_query & arrCheck(i) & ", "
next

sql_query = sql_query & arrChecks(UBound(arrChecks)) & "')"

conn.execute(sql_query)
%>


This will store the selected checkbox values in a comma delimited order.

[buntine]

No no, he doesnt need to give each control a unique name. ASP will work well with checkboxes.

[bruticus]

it's not the real one. but here's a partial example of it


Multiple Checkboxs With The Same Name:
<input type="checkbox" name="multiple" value="1" />
<input type="checkbox" name="multiple" value="2" />
<input type="checkbox" name="multiple" value="3" />

<%
Dim Item
For Each Item In Request.QueryString("multiple")
'SQL statement here
Next
%>


I'm sorry if this is all i got. I hope it can help you visualize it... :(

[bruticus]

Okay I've changed each checkboxes differently, each has its own name like:



<input type="checkbox" name="H1" value="blue">
<input type="checkbox" name="H2" value="red">
<input type="checkbox" name="H3" value="green">
<input type="checkbox" name="H4" value="yellow">
<input type="checkbox" name="H5" value="pink">


and it goes on up to 52 checkboxes

I want to save it to the database, and each checkboxes would be saved on their corresponding names

ex.
insert into table1 (h1, h2....) VALUES ('"&h1&"', '"&h2&"',....)

What's the proper approach for this?
I really would appreciate it if you guys could help. :(

Thanks for the time...

[buntine]

Bruticus, i already gave you an example of how to achieve this.. Giving each checkbox an individual name is not a good idea..

ASP will only submit the ones which are checkad anyway. Scroll up and see my post for the correct code.

I have not tested it so you may have to add to it.

[bruticus]

Thanks buntine and I hope you'll be patient on me for this...as i tried the code, I had a problem on the "(fieldname)" part because in my database the fields that they would be saved looked like this:

e1, e2, e3, e4....something like that

If you'd like you could check out this link to give a preview of what I mean:

www.batanesinteractive.net/temp/survey/questions.asp (http://www.batanesinteractive.net/temp/survey/questions.asp)

Thank you very much :)

Originally posted by buntine
Ok, try something like this:

ASP code:

<%

dim conn
dim sql_query
dim arrCheck
arrCheck = Split(request.form("A"), ",")

conn = server.createObject("ADODB.connection")
conn.open(YourConnectionString)

sql_query = "INSERT INTO tableName (fieldName) "
sql_query = sql_query & "VALUES ('"

for i = 0 to UBound(arrCheck) - 1
sql_query = sql_query & arrCheck(i) & ", "
next

sql_query = sql_query & arrChecks(UBound(arrChecks)) & "')"

conn.execute(sql_query)
%>


This will store the selected checkbox values in a comma delimited order.

[retesh_gondal]

give name "c1" to all checkboxes. on next page paste the following code and modify accordingly.

<%
dim ar 'declare array to store names of chkechboxes checked
chk=Request.Form("c1") 'store names of checked boxes in a variable
ar=split(chk,",") ' put values in array
k=ubound(ar) 'detemine upper bound of array

for i=0 to k
if chk="" then
exit for
else
con.Execute "delete from table_name where id=" & ar(i) & ""
end if
next%>

[Ribeyed]

hi,
try something like this:


<%
checkcount = request.form("checkcount") ' This should be 52
for x = 1 to checkcount 'going to loop 52 times
theelement = "c" & x ' generating the dynamic names for the 52 checkboxes
thefield = request.form("theelement") ' requesting the elements
' because the variable "theelement" will be written over with a new value after every loop you
'need to insert each one seperatly. As they all belong to the same record the first one will be
'an insert and the rest will be an update of the same record.
if x = 1 then
DBConn.Execute "INSERT INTO Table1 (h1) VALUES ('"theelement"')" 'The will create the record.
'need to retrieve the primary key. You can use count or max but if no records exsist then the code will fail.
'so you could do a select and order desc the retrieve the first record which will be the one you just inserted
sql = "SELECT PrmaryKey FROM table1 ORDER BY PrimaryKey DESC "
set RS = DBConn.Execute (sql)
if not RS.EOF then
PrimaryKey = RS("PrimaryKey") ' if this doesn't have a value then the rest of the code will fail. It should have
'as long as the insert statement works.
end if
else
' using an update query you can add the other values to the same record
thefield = "H" & x
DBConn.Execute = "UPDATE table1 set "&thefield&" = '"&theelement&"' WHERE primarykey = "&primarykey&""
end if
next
%>

[buntine]

Thanks buntine and I hope you'll be patient on me for this...as i tried the code, I had a problem on the "(fieldname)" part.


Ok, so you have set up a field for every checkbox? If so, we can use the same code i wrote before but we will have to alter it a tad..


dim conn
dim sql_query
dim arrCheck
arrCheck = Split(request.form("A"), ",")

conn = server.createObject("ADODB.connection")
conn.open(YourConnectionString)


sql_query = "INSERT INTO tableName (e1, e2, e3, etc, etc) "
sql_query = sql_query & "VALUES ('"

for i = 0 to UBound(arrCheck) - 1
sql_query = sql_query & arrCheck(i) & "','"
next

sql_query = sql_query & arrCheck(UBound(arrCheck)) & "')"

conn.execute(sql_query)


This will place each selected value in a seperate field. hopefully.

Regards,
Andrew Buntine.

[bruticus]

buntine, i got an error

Type mismatch: 'UBound'

Im not familiar on how to use this syntax. Please help :(

[buntine]

Hey, i made an error in the code. I have edited the code i posted before so you should be able to go back to it and try again with the new code.

Error was: I dimensioned the arrey as 'arrCheck' and then i tryed to refer to it as 'arrChecks'.

[bruticus]

thanks for that now im having a "Subscript out of range: 'UBound(...)'" error

here's what my checkboxes looks like

<input type="checkbox" name="E" value="Encoder">
<input type="checkbox" name="E" value="System Analyst">
<input type="checkbox" name="E" value="Web Developer">
<input type="checkbox" name="E" value="Data Librarian">
<input type="checkbox" name="E" value="Systems Analyst">
....


here's my ASP code



dim sql_query
dim arrCheck
arrCheck = Split(request.form("E"), ",")

sql_query = "INSERT INTO empE (e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11) "
sql_query = sql_query & "VALUES ('"

for i = 0 to UBound(arrCheck) - 1
sql_query = sql_query & arrCheck(i) & "','"
next

sql_query = sql_query & arrCheck(UBound(arrCheck)) & "')"

Objconn.execute(sql_query)


on my database here are my fields
table name : empE
Fields Type
id primary <autonumber>
e1 text
e2 text
e3 text
....
e11 text

I hope this help...

[buntine]

Ok, it does help..

SQL qill spit out an error if the amount of records defined in the SQL query to not match the amount of values given in the 'VALUES' part of the SQL query..

This is fixable, but it may be more tedious than i first thought..

I will have a go at fixing this problem for you if you would like.

Regards,
Andrew Buntine.

[bruticus]

yes please do...if it's not asking too much...


Thanks! :)

[buntine]

Ok, i sat down and hacked away for a while and i finally got it working after a few minutes of debugging.

First, i set up a super simple HTML page and added the following code.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>The form</title>
</head>
<body>
Check boxes...<br /><br />
<form action="process.asp" method="post" name="form_one">
check 1: <input type="checkbox" name="e" value="1" /><br />
check 2: <input type="checkbox" name="e" value="2" /><br />
check 3: <input type="checkbox" name="e" value="3" /><br />
check 4: <input type="checkbox" name="e" value="4" /><br />
check 5: <input type="checkbox" name="e" value="5" /><br />
check 6: <input type="checkbox" name="e" value="6" /><br />
check 7: <input type="checkbox" name="e" value="7" /><br />
check 8: <input type="checkbox" name="e" value="8" /><br />
check 9: <input type="checkbox" name="e" value="9" /><br />
check 10: <input type="checkbox" name="e" value="10" /><br />
check 11: <input type="checkbox" name="e" value="11" /><br />
<br /><input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>


Then, i created a page named process.asp. The file contains the following code.


<%
'|Initialise our variables.
dim arrCheck
dim i, x, intFields

dim arrSqlCheck()

dim conn, conn_string
dim sql_query, rsTemp

'|Create array of all the checked checkboxes.
arrCheck = Split(request.form("e"), ",")

'|Set connection string. You may need to edit this.
set conn = server.createObject("ADODB.connection")
conn_string = "DBQ=" & server.mapPath("dtabse.mdb") & ";"
conn_string = conn_string & "Driver={Microsoft Access Driver (*.mdb)}"
conn.open(conn_string)

'|Set ADO object and execute SQL query.
set rsTemp = server.createObject("ADODB.recordSet")
sql_query = "SELECT * FROM empE"
rsTemp.open(sql_query), conn, 3

'|Get the number of fields in the table.
intFields = rsTemp.fields.count

'|Free resources by closing dormant ADO object.
rsTemp.close
set rs = nothing

'|Re-dimension the array so we can fill it up.
reDim preserve arrSqlCheck(intFields-1)

'|Determine which checkboxes have been checked and then
'|fill the array, respectively.
for i = 0 to UBound(arrCheck)
for x = 1 to UBound(arrSqlCheck)
if x = CInt(arrCheck(i)) then
arrSqlCheck(x) = "yes"
else
if CStr(arrSqlCheck(x)) <> "yes" then
arrSqlCheck(x) = "no"
end if
end if
next
next

'|Start the SQL query.
sql_query = "INSERT INTO empE (e1,e2,e3,e4,e5,e6,e7,e8,e9,e10,e11)"
sql_query = sql_query & " VALUES ("

'|Construct the main body of the SQL query.
for i = 1 to UBound(arrSqlCheck) -1
sql_query = sql_query & "'" & arrSqlCheck(i) & "',"
next

'|Add the finishng touches.
sql_query = sql_query & "'" & arrSqlCheck(UBound(arrSqlCheck)) & "')"

'|Execute the query.
conn.execute(sql_query)

'|Close ADO object to free resources.
conn.close
set conn = nothing

'|Display success message.
with response
.write("<div align=""center"" style=""font-size: 12px; font-family: arial,tahoma; width: 700px; height: 100px; margin-top: 20px;"">The database has been successfully updated!</div>")
.write(vbCrLf)
end with
%>


The database i used had one table named 'empE'. The table had 12 fields, as follows.

id autoNumber (primary key)
e1 text
e2 text
e3 text
...
e11 text


I tested this and it worked fine. If you want me to email you the databse and 2 files, just ask.

Regards,
Andrew Buntine.

[bruticus]

Yay its works! thanks very much Buntine. The code is more than enough....


Thanks again! :D

[buntine]

Thats ok;) im glad to help you out.