dchid
asked on
Dynamically populate checkbox from database with appropriate boxes checked
Not sure how to explain this properly, basically I am working on an update page which updates records already in a database, I pull the info from the database to populate check boxes and drop down fields and have the option currently listed in database shown, the following code will hopefully help you understand what I am on about better than my explanation.
<%
Dim myRS, prodID, i, mySQL, prodcat, prodsubcat, Cat, prodbrand, prodsize
prodID = Request.QueryString("recor
mySQL = "SELECT Products.*, ProductSize.size_id FROM Products INNER JOIN ProductSize ON Products.product_id = ProductSize.product_id WHERE (((Products.product_id)= " & prodID & "))"
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn, adOpenStatic
%>
<form name = "updaterecform" action="addrecord.asp?form
<input type = "hidden" name = "product_id" value="<%=prodID%>">
Product Name: <input type ="text" name="product_name" value="<%=myRS("product_na
Price: <input type ="text" name="product_price" value="<%=myRS("product_pr
Picture URL: <input type ="text" name="product_picture" value="<%=myRS("product_pi
Stocked:
<select name="product_stocked">
<option value="0"<% If myRS("product_stocked") ="False" then response.write(" selected ") end if%>>Not In Stock</option>
<option value="1" <% If myRS("product_stocked") ="True" then response.write(" selected ") end if%>>In Stock</option>
</select>
<%
prodcat = myRS("product_category_id"
prodsubcat = myRS("product_subcategory_
prodsize = myRS("Size_id")
myRS.Close
Set myRS = Nothing
%>
Category:
<%
mySQL = "SELECT * FROM Category"
Response.Write "<select name = 'product_category'>"
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn
While Not myRS.EOF
Cat = myRS("product_category_id"
Response.Write "<option value = " & myRS("product_category_id"
If prodcat = myRS("product_category_id"
Response.Write ">"
Response.Write Cat & "</option>"
myRS.MoveNext
Wend
Response.Write "</select>"
Response.Write "SubCategory: "
myRS.Close
Set myRS = Nothing
I am not sure if the above code is the best way of doing it but it is currently working fine, the problem is when I come to sizes. Sizes is dynamically populated with a checkbox and is a many to many relationship in the database, like so
products.product_id ----> productsize.product_id
productsize.size_id <---- sizes.size_id
sizes.sizes
I managed to dynamically display the sizes as checkbox and check the first size available for each product taken from the database. This is where I am stuck, since there are many sizes for the majority of products and I am not experienced with asp and not sure how to use arrays etc i am not sure how I would check the rest of the relevant fields not just the first one in the database. current section of code for what I am stuck on is
Response.Write "Sizes: "
Dim varSize1
mySQL = "SELECT * From Sizes"
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn
While Not myRS.EOF
varSize1 = myRS("sizes")
Response.Write "<input type='Checkbox' name='sizes' value = " & myRS("size_id") & ""
If prodsize = myRS("size_id") then Response.Write(" checked=true ") End If
Response.Write ">" & varSize1 & ""
myRS.MoveNext
Wend
Any help given will be greatly appreciated, thanks.
<%
Dim myRS, prodID, i, mySQL, prodcat, prodsubcat, Cat, prodbrand, prodsize
prodID = Request.QueryString("recor
mySQL = "SELECT Products.*, ProductSize.size_id FROM Products INNER JOIN ProductSize ON Products.product_id = ProductSize.product_id WHERE (((Products.product_id)= " & prodID & "))"
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn, adOpenStatic
%>
<form name = "updaterecform" action="addrecord.asp?form
<input type = "hidden" name = "product_id" value="<%=prodID%>">
Product Name: <input type ="text" name="product_name" value="<%=myRS("product_na
Price: <input type ="text" name="product_price" value="<%=myRS("product_pr
Picture URL: <input type ="text" name="product_picture" value="<%=myRS("product_pi
Stocked:
<select name="product_stocked">
<option value="0"<% If myRS("product_stocked") ="False" then response.write(" selected ") end if%>>Not In Stock</option>
<option value="1" <% If myRS("product_stocked") ="True" then response.write(" selected ") end if%>>In Stock</option>
</select>
<%
prodcat = myRS("product_category_id"
prodsubcat = myRS("product_subcategory_
prodsize = myRS("Size_id")
myRS.Close
Set myRS = Nothing
%>
Category:
<%
mySQL = "SELECT * FROM Category"
Response.Write "<select name = 'product_category'>"
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn
While Not myRS.EOF
Cat = myRS("product_category_id"
Response.Write "<option value = " & myRS("product_category_id"
If prodcat = myRS("product_category_id"
Response.Write ">"
Response.Write Cat & "</option>"
myRS.MoveNext
Wend
Response.Write "</select>"
Response.Write "SubCategory: "
myRS.Close
Set myRS = Nothing
I am not sure if the above code is the best way of doing it but it is currently working fine, the problem is when I come to sizes. Sizes is dynamically populated with a checkbox and is a many to many relationship in the database, like so
products.product_id ----> productsize.product_id
productsize.size_id <---- sizes.size_id
sizes.sizes
I managed to dynamically display the sizes as checkbox and check the first size available for each product taken from the database. This is where I am stuck, since there are many sizes for the majority of products and I am not experienced with asp and not sure how to use arrays etc i am not sure how I would check the rest of the relevant fields not just the first one in the database. current section of code for what I am stuck on is
Response.Write "Sizes: "
Dim varSize1
mySQL = "SELECT * From Sizes"
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn
While Not myRS.EOF
varSize1 = myRS("sizes")
Response.Write "<input type='Checkbox' name='sizes' value = " & myRS("size_id") & ""
If prodsize = myRS("size_id") then Response.Write(" checked=true ") End If
Response.Write ">" & varSize1 & ""
myRS.MoveNext
Wend
Any help given will be greatly appreciated, thanks.
ASKER
Thanks, tried it but now all the checkboxes are not ticked, and they also all have the same name, rather than being all the sizes listed from Sizes.sizes with only the boxes ticked which are related to a product in the database.
ASKER
sorry that should read all the checkboxes are now ticked and have the same name
dont understand clearly :)
does it returns records that you want?
If you need to check all of them
change this
'If prodsize = myRS("size_id") then Response.Write(" checked=true ") End If
to
Response.Write(" checked=true ")
does it returns records that you want?
If you need to check all of them
change this
'If prodsize = myRS("size_id") then Response.Write(" checked=true ") End If
to
Response.Write(" checked=true ")
ASKER
I'll try to explain more clearly :)
basically the field sizes in the table sizes consists of the different sizes available for clothing, Small, medium, large, and also shoes sizes such as 5, 6, 7 etc it is related to the product through a many to many relationship.
On the this page I am trying to get all the sizes from the sizes field in table sizes to be displayed but the only boxes I would like checked are those sizes which are related to product through the product_id in the productsize table. Basically it shows what sizes are currently available for a particular product, lists all sizes from the sizes field in the table field and checks the sizes currently available that are related to a product in the database.
Think thats even more confusing, if this explanation doesnt help, will attempt to be clearer after a cup of coffee :)
basically the field sizes in the table sizes consists of the different sizes available for clothing, Small, medium, large, and also shoes sizes such as 5, 6, 7 etc it is related to the product through a many to many relationship.
On the this page I am trying to get all the sizes from the sizes field in table sizes to be displayed but the only boxes I would like checked are those sizes which are related to product through the product_id in the productsize table. Basically it shows what sizes are currently available for a particular product, lists all sizes from the sizes field in the table field and checks the sizes currently available that are related to a product in the database.
Think thats even more confusing, if this explanation doesnt help, will attempt to be clearer after a cup of coffee :)
ASKER
To add to that, with the current code I have, all sizes are shown but only one of the checkboxes is ticked, for example lets say a trainer has sizes 6,7,8 available as shown in the productsize table, I am getting sizes small, medium, large, 6, 7, 8, 9 shown as i want but only size 6 would be checked when I aiming to get 6,7,8 checked as thats what is shown in database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Think its getting closer :) but I get the first size listed then get the error
ADODB.Recordset error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
on this line myRS.MoveNext
ADODB.Recordset error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
on this line myRS.MoveNext
ASKER
got it working and it seems to work perfectly, just go check properly and will let you know
ASKER
Excellent works perfectly, Thanks the helps appreciated
glad to help you
Dim varSize1
mySQL = "SELECT * From Sizes,productsize WHERE productsize.size_id = sizes.size_id AND productsize.size_id=" & prodsize
Set myRS = Server.CreateObject("ADODB
myRS.Open mySQL, objConn
While Not myRS.EOF
varSize1 = myRS("sizes")
Response.Write "<input type='Checkbox' name='sizes' value = " & myRS("size_id") & ""
If prodsize = myRS("size_id") then Response.Write(" checked=true ") End If
Response.Write ">" & varSize1 & ""
myRS.MoveNext
Wend