• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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("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
dchid
Asked:
dchid
  • 7
  • 4
1 Solution
 
nurbekCommented:
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
 
dchidAuthor Commented:
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
 
dchidAuthor Commented:
sorry that should read all the checkboxes are now ticked and have the same name
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nurbekCommented:
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
 
dchidAuthor Commented:
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
 
dchidAuthor Commented:
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
 
nurbekCommented:
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
 
dchidAuthor Commented:
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
 
dchidAuthor Commented:
got it working and it seems to work perfectly, just go check properly and will let you know
0
 
dchidAuthor Commented:
Excellent works perfectly, Thanks the helps appreciated
0
 
nurbekCommented:
glad to help you
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now