?
Solved

Dynamically populate checkbox from database with appropriate boxes checked

Posted on 2005-03-01
11
Medium Priority
?
344 Views
Last Modified: 2011-10-03
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("recordid")

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.Recordset")
myRS.Open mySQL, objConn, adOpenStatic
%>

<form name = "updaterecform" action="addrecord.asp?formtype="updaterec" method="post">
<input type = "hidden" name = "product_id" value="<%=prodID%>">
Product Name: <input type ="text" name="product_name" value="<%=myRS("product_name")%>">

Price: <input type ="text" name="product_price" value="<%=myRS("product_price")%>">

Picture URL: <input type ="text" name="product_picture" value="<%=myRS("product_picture")%>">

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_id")
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.Recordset")
myRS.Open mySQL, objConn
While Not myRS.EOF
      Cat = myRS("product_category_id") & "-" & myRS("product_category")
      Response.Write "<option value = " & myRS("product_category_id") &""
      If prodcat = myRS("product_category_id") then Response.Write(" selected ") End If
      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.Recordset")
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.
0
Comment
Question by:dchid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 13

Expert Comment

by:nurbek
ID: 13431959
try to create a sql command that gets matched reocrds (matched sizes)

Dim varSize1
mySQL = "SELECT * From Sizes,productsize WHERE productsize.size_id = sizes.size_id AND productsize.size_id=" & prodsize

Set myRS = Server.CreateObject("ADODB.Recordset")
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
0
 

Author Comment

by:dchid
ID: 13432069
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.
0
 

Author Comment

by:dchid
ID: 13432076
sorry that should read all the checkboxes are now ticked and have the same name
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:nurbek
ID: 13432151
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 ")
0
 

Author Comment

by:dchid
ID: 13432237
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 :)
0
 

Author Comment

by:dchid
ID: 13432264
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.
0
 
LVL 13

Accepted Solution

by:
nurbek earned 2000 total points
ID: 13432546
first try to get productid

<%
prodcat = myRS("product_category_id")
prodsubcat = myRS("product_subcategory_id")
prodsize = myRS("Size_id")
productid = myRS("product_id")
myRS.Close
Set myRS = Nothing
%>

then
try this
Response.Write "Sizes: "
Dim varSize1
mySQL = "SELECT * From Sizes"

Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, objConn
While Not  myRS.EOF
     varSize1 = myRS("sizes")
     Response.Write "<input type='Checkbox' name='sizes' value = " & myRS("size_id") & ""
       sSQL = "SELECT * From productsize WHERE productsize.size_id ="  & myRS("size_id") & " AND productsize.product_id=" & productid
       Set sRS = objConn.Execute(sSQL)
       If Not sRS.Eof Then
     Response.Write(" checked=true ")
       End If
       Set sRS = Nothing
      
Response.Write ">" & varSize1 & ""
myRS.MoveNext
Wend

0
 

Author Comment

by:dchid
ID: 13432747
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
0
 

Author Comment

by:dchid
ID: 13432798
got it working and it seems to work perfectly, just go check properly and will let you know
0
 

Author Comment

by:dchid
ID: 13432858
Excellent works perfectly, Thanks the helps appreciated
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13432879
glad to help you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question