?
Solved

Populating a form with checkboxes from a database column with multiple values in ASP

Posted on 2011-05-12
2
Medium Priority
?
235 Views
Last Modified: 2012-05-11
I have a MSSQL Database with the following tables

[dbo].[tbl_clients](
      [IDTag] [nvarchar](255) NULL,
      [membershipnumber] [nvarchar](255) NULL,
      [username] [nvarchar](255) NULL,
      [password] [nvarchar](255) NULL,
      [dateoflastlogin] [datetime] NULL,
      [title] [nvarchar](255) NULL,
      [Gender] [nvarchar](255) NULL,
      [firstname] [nvarchar](255) NULL,
      [middlename] [nvarchar](255) NULL,
      [lastname] [nvarchar](255) NULL,
      [suffix] [nvarchar](255) NULL,
      [preferredcontactmethod] [nvarchar](255) NULL,
      [HomeAddress] [nvarchar](max) NULL,
      [HomeAddress1] [nvarchar](255) NULL,
      [HomeAddress2] [nvarchar](255) NULL,
      [HomeAddress3] [nvarchar](255) NULL,
      [POBoxHome] [nvarchar](255) NULL,
      [ZipcodeHome] [nvarchar](255) NULL,
      [Countrycode] [nvarchar](255) NULL,
      [Workaddress] [nvarchar](max) NULL,
      [WorkAddress1] [nvarchar](255) NULL,
      [WorkAddress2] [nvarchar](255) NULL,
      [WorkAddress3] [nvarchar](255) NULL,
      [Newsletter] [nvarchar](255) NULL,
      [POBoxWork] [nvarchar](255) NULL,
      [referall] [nvarchar](255) NULL,
      [status] [nvarchar](255) NULL,
      [ZipCodeWork] [nvarchar](255) NULL,
      [CountryCodeWork] [nvarchar](255) NULL,
      [preferredcontactnumber] [nvarchar](255) NULL,
      [dateofbirth] [datetime] NULL,
      [homephonecountrycode] [nvarchar](255) NULL,
      [homephoneareacode] [nvarchar](255) NULL,
      [homephonenumber] [nvarchar](255) NULL,
      [workphonecountrycode] [nvarchar](255) NULL,
      [workphoneareacode] [nvarchar](255) NULL,
      [workphonenumber] [nvarchar](255) NULL,
      [mobilecountrycode] [nvarchar](255) NULL,
      [mobilenumber] [nvarchar](255) NULL,
      [emailaddress] [nvarchar](255) NULL,
      [jobtitle] [nvarchar](255) NULL,
      [companyname] [nvarchar](255) NULL,
      [preferredaddress] [nvarchar](255) NULL,
      [mediacode] [nvarchar](255) NULL,
      [language] [nvarchar](255) NULL,
      [joindate] [datetime] NULL,
      [Industrytype] [nvarchar](255) NULL,
      [jobdesignationdetails] [nvarchar](255) NULL,
      [tier] [nvarchar](255) NULL,
      [ffpmail] [nvarchar](255) NULL,
      [enrollmethod] [nvarchar](255) NULL,
      [passportnumber] [nvarchar](255) NULL,
      [countryiatacode] [nvarchar](255) NULL,
      [nationalitycode] [nvarchar](255) NULL,
      [passporttype] [nvarchar](255) NULL,
      [issuedate] [datetime] NULL,
      [expirydate] [datetime] NULL,
      [placeofbirth] [nvarchar](255) NULL,
      [placeofissue] [nvarchar](255) NULL,
      [nationalid] [nvarchar](255) NULL,
      [passwordquestion] [nvarchar](255) NULL,
      [memberstatus] [nvarchar](255) NULL,
      [autoid] [int] IDENTITY(1,1) NOT NULL,
      [changepassword] [bit] NULL,
      [lastloginIP] [nvarchar](50) NULL,
      [datecreated] [datetime] NULL,
      [welcome_email_date] [datetime] NULL,
      [welcome_email] [bit] NULL,
      [specialinterests] [nvarchar](20) NULL


Also a special Interest table

[dbo].[tbl_clients_specialinterests](
      [autoid] [int] IDENTITY(1,1) NOT NULL,
      [specialinterest] [nvarchar](50) NULL


I have a form in the back end that should display ALL the sepcial interests for a particular client.

First I get all the values and populate variables, but how do I iterate through the part of the form containing the special interests.

See Form Below



<%


Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON


MySQL="Select * from tbl_clients where autoid=" & parentid


Set ObjRs=objDB.Execute(MySQL)


firstname = objRS("firstname")
lastname = objrs("lastname")
membershipnumber= objrs("membershipnumber")
emailaddress = objrs("emailaddress")
gender = objrs("gender")
title = objrs("title")
homeaddress = objrs("homeaddress")
zipcode = objrs("zipcodehome")
homephone = objrs("homephonenumber")
workphone = objrs("workphonenumber")
mobile = objrs("mobilenumber")
jobtitle = objrs("jobtitle")
companyname = objrs("companyname")
dateofbirth = objrs("dateofbirth")
countrycode = objrs("countrycode")
password = objrs("password")
username = objrs("username")


objRS.close()
Set objRS = Nothing

%>

<form action="<%=Request.ServerVariables("SCRIPT_NAME") %>" method="post" class="clean">
                <input type="hidden" name="action" value="UpdateUser" />
                <input type="hidden" name="parentid" value="<%=parentid %>" />
                <ol>
                  <li>
                    <fieldset>
                      <ol>
                        <li>
                          <label for="membershipnumber">Membership Number</label>
                          <input type="text" id="membershipnumber" name="membershipnumber" value="<%=membershipnumber%>" size="45" />
                        </li>
                        <li>
                          <label for="title">Title</label>
                          <select name="title">
                            <option value="Mr" <%if title = "Mr" then %> selected <%end if %>>Mr</option>
                            <option value="Mrs" <%if title = "Mrs" then %> selected <%end if %>>Mrs</option>
                            <option value="Ms" <%if title = "Ms" then %> selected <%end if %>>Ms</option>
                            <option value="Miss" <%if title = "Miss" then %> selected <%end if %>>Miss</option>
                            <option value="Dr" <%if title = "Dr" then %> selected <%end if %>>Dr</option>
                            <option value="Chief" <%if title = "Chief" then %> selected <%end if %>>Chief</option>
                            <option value="Chief Mrs" <%if title = "Chief Mrs" then %> selected <%end if %>>Chief Mrs</option>
                            <option value="Prof" <%if title = "Prof" then %> selected <%end if %>>Prof</option>
                            <option value="Alhaji" <%if title = "Alhaji" then %> selected <%end if %>>Alhaji</option>
                            <option value="Alhaja" <%if title = "Alhaja" then %> selected <%end if %>>Alhaja</option>
                          </select>
                        </li>
                        <li>
                          <label for="firstname">First Name</label>
                          <input type="text" id="firstname" name="firstname" value="<%=firstname %>" size="45" />
                        </li>
                        <li>
                          <label for="lastname">Surname</label>
                          <input type="text" id="lastname" name="lastname" value="<%=lastname %>" size="45" />
                        </li>
                        <li>
                          <label for="gender">Gender</label>
                          <select name="gender">
                            <option value="M" <%if gender = "M" then %> selected <%end if %> >Male</option>
                            <option value="F" <%if gender = "F" then %> selected <%end if %>>Female</option>
                          </select>
                        </li>
                        <li>
                          <label for="dob">Date of Birth[<%=dateofbirth %>]</label>
                          <input type="text" id="dob" name="dob" value="<%=dateofbirth %>"  />
                          <script type="text/javascript">
          // <![CDATA[
              $(function () {
                  $('#dob').datepicker({
                      skin: "dmxCalendar",
                      dateFormat: "dd/mm/yy",
                      yearRange: "-90:-18",
                      showStatus: true,
                      numberOfMonths: [1, 1],
                      showAnim: "slide",
                      showOptions:
            {
                direction: "up",
                easing: "easeOutBounce"
            },
                      duration: "slow",
                      showOn: "both",
                      buttonImage: "Styles/dmxCalendar/calendar_icon.gif",
                      buttonImageOnly: true,
                      defaultDate: ""
                  });
              })
          // ]]>
          </script>
                        </li>
                       <li>
                  <label for="countrycode">Country of Residence [<%=countrycode %>]</label>

                  <select name="countrycode">
                  <%
                 
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON

MySQL="Select * from tbl_countries order by country"

Set ObjRs=objDB.Execute(MySQL)

if objrs.eof then
%>


<%

else
Do While Not Objrs.EOF
%>
<option value="<%=objrs("countrycode") %>" <%if countrycode = objrs("countrycode") then %> selected <%end if %>><%=objrs("country") %></option>
<%
Objrs.MoveNext
Loop
end if


objRS.close()
Set objRS = Nothing


                 
                   %>
                  </select>
                </li>
                        <li>
                          <label for="homeaddress">Home Address</label>
                          <textarea id="homeaddress" name="homeaddress" cols=50 rows=4><%=homeaddress %></textarea>
                        </li>
                        <li>
                          <label for="zipcode">Zip Code</label>
                          <input type="text" id="zipcode" name="zipcode" value="<%=zipcode %>" size="45" />
                        </li>
                        <li>
                          <label for="homephone">Home Phone </label>
                          <input type="text" id="homephone" name="homephonenumber" value="<%=homephone %>" size="45" />
                        </li>
                        <li>
                          <label for="workphone">Work Phone</label>
                          <input type="text" id="workphone" name="workphonenumber" value="<%=workphone %>" size="45" />
                        </li>
                        <li>
                          <label for="mobile">Mobile</label>
                          <input type="text" id="mobile" name="mobilenumber" value="<%=mobile %>" size="45" />
                        </li>
                        <li>
                          <label for="emailaddress">Email Address</label>
                          <input type="text" id="emailaddress" name="emailaddress" value="<%=emailaddress %>" size="45" />
                        </li>
                        <li>
                          <label for="jobtitle">Job Title</label>
                          <input type="text" id="jobtitle" name="jobtitle" value="<%=jobtitle %>" size="45" />
                        </li>
                        <li>
                          <label for="companyname">Company Name</label>
                          <input type="text" id="companyname" name="companyname" value="<%=companyname %>" size="45" />
                        </li>
                         <li>
                          <label for="si">Special Interests</label>
                          <div>
                           <%
Dim NumberOfCells

NumberOfCells = 3

'Select the services from the database and then alternate the tables
           
MySQL="SELECT * "&_
      "FROM tbl_clients_specialinterests "&_
      "Order by specialinterest;"

Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON

Set ObjRs=objDB.Execute(MySQL)

if objrs.eof then
      'No Services to display
else
%>
      <table width="100%" border="0" cellspacing="2" cellpadding="2">
            <tr>
                  <%
                  count = 0
                  Do While Not Objrs.EOF
                  %>
                        <td align=left valign=middle width="<%=CInt(100 / NumberOfCells)%>%">
                          <input type=checkbox name="specialinterest" value="<%=objrs("autoid") %>" />&nbsp;<%=objrs("specialinterest") %></td>
                        <%
                        count = count + 1

                        Objrs.MoveNext
                       
                        If count Mod NumberOfCells = 0 and Not Objrs.EOF Then
                        %>
                              </tr><tr>
                        <%
                        End If
                        %>
                  <%
                  Loop
                  %>
            </tr>
      </table>
      <p>
<%
end if

objRS.close()
Set objRS = Nothing
%>
                          </div>
                         
                        </li>
                      </ol>
                    </fieldset>
                  </li>
                </ol>
                <p style="text-align:right;">
                  <input type="reset" value="CANCEL" />
                  <input type="submit" value="UPDATE <%=UCASE(firstname & " " & lastname) %>" />
                </p>
              </form>

Your help much appreciated.
0
Comment
Question by:souldj
2 Comments
 
LVL 54

Accepted Solution

by:
Scott Fell,  EE MVE earned 2000 total points
ID: 35747510
I think what you are missing is there should be a table of just specialinterests.  You have a tbl_clients_specialinterests.    On the form where you have your checkbox's you should be showing ALL special interests regardless if they are client_specialinterests.

Create a table
[dbo].[tbl_specialinterests]( ' THIS IS ALL interests
      [autoid] [int] IDENTITY(1,1) NOT NULL,
      [specialinterest] [nvarchar](50) NULL

Your table
[dbo].[tbl_clients_specialinterests](
       [autoid] [int] IDENTITY(1,1) NOT NULL, 'unique id
      [interestid] [int] NOT NULL  ' to match the tbl_specialinterests.autoid
      [clientid] [int] NOT NULL  ' match client.autoid

You will create your checkboxs from the tbl_specialinterests and inside that that loop, check to see if there is a match with the tbl_clients_specialinterests.  There are multiple ways to do this.  Here I show creating the recorset inside of your loop or you could create the recordset outside and simply do a filter, rs.filter="ID = '"&somevalue&"'"   or hard coded rs.filter="ID = 'somevalue'".   Many times for these types of items, instead of creating a second table, you could also create a large text field in your client record.  When you check each off the boxs they get stored as a comma field like,   interest1,interest2, interest35.  Then you would use the split function to break that field apart when you want to use y our checkbox.

 
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>

<%


Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON


MySQL="Select * from tbl_clients where autoid=" & parentid


Set ObjRs=objDB.Execute(MySQL)


firstname = objRS("firstname")
lastname = objrs("lastname")
membershipnumber= objrs("membershipnumber")
emailaddress = objrs("emailaddress")
gender = objrs("gender")
title = objrs("title")
homeaddress = objrs("homeaddress")
zipcode = objrs("zipcodehome")
homephone = objrs("homephonenumber")
workphone = objrs("workphonenumber")
mobile = objrs("mobilenumber")
jobtitle = objrs("jobtitle")
companyname = objrs("companyname")
dateofbirth = objrs("dateofbirth")
countrycode = objrs("countrycode")
password = objrs("password")
username = objrs("username")


objRS.close()
Set objRS = Nothing

%>

<form action="<%=Request.ServerVariables("SCRIPT_NAME") %>" method="post" class="clean">
                <input type="hidden" name="action" value="UpdateUser" />
                <input type="hidden" name="parentid" value="<%=parentid %>" />
                <ol>
                  <li>
                    <fieldset>
                      <ol>
                        <li>
                          <label for="membershipnumber">Membership Number</label>
                          <input type="text" id="membershipnumber" name="membershipnumber" value="<%=membershipnumber%>" size="45" />
                        </li>
                        <li>
                          <label for="title">Title</label>
                          <select name="title">
                            <option value="Mr" <%if title = "Mr" then %> selected <%end if %>>Mr</option>
                            <option value="Mrs" <%if title = "Mrs" then %> selected <%end if %>>Mrs</option>
                            <option value="Ms" <%if title = "Ms" then %> selected <%end if %>>Ms</option>
                            <option value="Miss" <%if title = "Miss" then %> selected <%end if %>>Miss</option>
                            <option value="Dr" <%if title = "Dr" then %> selected <%end if %>>Dr</option>
                            <option value="Chief" <%if title = "Chief" then %> selected <%end if %>>Chief</option>
                            <option value="Chief Mrs" <%if title = "Chief Mrs" then %> selected <%end if %>>Chief Mrs</option>
                            <option value="Prof" <%if title = "Prof" then %> selected <%end if %>>Prof</option>
                            <option value="Alhaji" <%if title = "Alhaji" then %> selected <%end if %>>Alhaji</option>
                            <option value="Alhaja" <%if title = "Alhaja" then %> selected <%end if %>>Alhaja</option>
                          </select>
                        </li>
                        <li>
                          <label for="firstname">First Name</label>
                          <input type="text" id="firstname" name="firstname" value="<%=firstname %>" size="45" />
                        </li>
                        <li>
                          <label for="lastname">Surname</label>
                          <input type="text" id="lastname" name="lastname" value="<%=lastname %>" size="45" />
                        </li>
                        <li>
                          <label for="gender">Gender</label>
                          <select name="gender">
                            <option value="M" <%if gender = "M" then %> selected <%end if %> >Male</option>
                            <option value="F" <%if gender = "F" then %> selected <%end if %>>Female</option>
                          </select>
                        </li>
                        <li>
                          <label for="dob">Date of Birth[<%=dateofbirth %>]</label>
                          <input type="text" id="dob" name="dob" value="<%=dateofbirth %>"  />
                          <script type="text/javascript">
          // <![CDATA[
              $(function () {
                  $('#dob').datepicker({
                      skin: "dmxCalendar",
                      dateFormat: "dd/mm/yy",
                      yearRange: "-90:-18",
                      showStatus: true,
                      numberOfMonths: [1, 1],
                      showAnim: "slide",
                      showOptions:
            {
                direction: "up",
                easing: "easeOutBounce"
            },
                      duration: "slow",
                      showOn: "both",
                      buttonImage: "Styles/dmxCalendar/calendar_icon.gif",
                      buttonImageOnly: true,
                      defaultDate: ""
                  });
              })
          // ]]>
          </script>
                        </li>
                       <li>
                  <label for="countrycode">Country of Residence [<%=countrycode %>]</label>

                  <select name="countrycode">
                  <%
                  
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON

MySQL="Select * from tbl_countries order by country"

Set ObjRs=objDB.Execute(MySQL)

if objrs.eof then
%>


<%

else
Do While Not Objrs.EOF
%>
<option value="<%=objrs("countrycode") %>" <%if countrycode = objrs("countrycode") then %> selected <%end if %>><%=objrs("country") %></option>
<% 
Objrs.MoveNext
Loop
end if


objRS.close()
Set objRS = Nothing


                  
                   %>
                  </select>
                </li>
                        <li>
                          <label for="homeaddress">Home Address</label>
                          <textarea id="homeaddress" name="homeaddress" cols=50 rows=4><%=homeaddress %></textarea>
                        </li>
                        <li>
                          <label for="zipcode">Zip Code</label>
                          <input type="text" id="zipcode" name="zipcode" value="<%=zipcode %>" size="45" />
                        </li>
                        <li>
                          <label for="homephone">Home Phone </label>
                          <input type="text" id="homephone" name="homephonenumber" value="<%=homephone %>" size="45" />
                        </li>
                        <li>
                          <label for="workphone">Work Phone</label>
                          <input type="text" id="workphone" name="workphonenumber" value="<%=workphone %>" size="45" />
                        </li>
                        <li>
                          <label for="mobile">Mobile</label>
                          <input type="text" id="mobile" name="mobilenumber" value="<%=mobile %>" size="45" />
                        </li>
                        <li>
                          <label for="emailaddress">Email Address</label>
                          <input type="text" id="emailaddress" name="emailaddress" value="<%=emailaddress %>" size="45" />
                        </li>
                        <li>
                          <label for="jobtitle">Job Title</label>
                          <input type="text" id="jobtitle" name="jobtitle" value="<%=jobtitle %>" size="45" />
                        </li>
                        <li>
                          <label for="companyname">Company Name</label>
                          <input type="text" id="companyname" name="companyname" value="<%=companyname %>" size="45" />
                        </li>
                         <li>
                          <label for="si">Special Interests</label>
                          <div>
                           <%
Dim NumberOfCells

NumberOfCells = 3

'Select the services from the database and then alternate the tables
           
MySQL="SELECT * "&_
      "FROM tbl_specialinterests "&_  ' these are all specialinterest
      "Order by specialinterest;"

Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON

Set ObjRs_All=objDB.Execute(MySQL)

if ObjRs_All.eof then
      'No Services to display
else
%>
      <table width="100%" border="0" cellspacing="2" cellpadding="2">
            <tr>
                  <%
                  count = 0
                  Do While Not ObjRs_All.EOF
				  
				  ' Check if client has the interest
				  MySQLclient="SELECT * "&_
    			  "FROM tbl_client_specialinterests "&_  ' 
				  "WHERE clientid = "&parentid&" AND interestid = "&ObjRs_All("autoid") ' if they are the same we have
     				Set objDB_client = Server.CreateObject("ADODB.Connection")
					objDB_client.Open DB_CON
				  Set ObjRs_client=objDB_client.Execute(MySQL)
				  if not ObjRs_client.bof or not ObjRs_client.eof then 'if match
				  	checkedvalue=" checked=""checked"" "
					else
					checkedvalue="" ' nothing
				  end if
				  
                  %>
                        <td align=left valign=middle width="<%=CInt(100 / NumberOfCells)%>%">
                          <input name="specialinterest" type=checkbox value="<%=ObjRs_All("autoid") %>" <%=checkedvalue%> />
                          &nbsp;<%=objrs("specialinterest") %></td>
                        <%
                        count = count + 1

                        ObjRs_All.MoveNext
                       
                        If count Mod NumberOfCells = 0 and Not ObjRs_All.EOF Then
                        %>
                              </tr><tr>
                        <%
                        End If
                        %>
                  <%
                  Loop
                  %>
            </tr>
      </table>
      <p>
<%
end if

ObjRs_All.close()
Set ObjRs_All = Nothing
%>
                          </div>
                          
                        </li>
                      </ol>
                    </fieldset>
                  </li>
                </ol>
                <p style="text-align:right;">
                  <input type="reset" value="CANCEL" />
                  <input type="submit" value="UPDATE <%=UCASE(firstname & " " & lastname) %>" />
                </p>
              </form>
</body>
</html>

Open in new window


One note on this code, it is only showing you how to make the checkbox's "checked" if there is a match.  You will need to add code to accept the checked values and update your table.
0
 
LVL 1

Author Comment

by:souldj
ID: 35778836
Thanks A lot, I thought it all through and did as you said and wrote code that deleted all the current data and then added the new data when an update for the client was being done.!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses

829 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