Destiny Amana
asked on
Populating a form with checkboxes from a database column with multiple values in ASP
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_special interests] (
[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 .Connectio n")
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.ServerV ariables(" SCRIPT_NAM E") %>" 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">Mem bership 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</la bel>
<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/calend ar_icon.gi f",
buttonImageOnly: true,
defaultDate: ""
});
})
// ]]>
</script>
</li>
<li>
<label for="countrycode">Country of Residence [<%=countrycode %>]</label>
<select name="countrycode">
<%
Set objDB = Server.CreateObject("ADODB .Connectio n")
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("countryco de") %>" <%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_specialinteres ts "&_
"Order by specialinterest;"
Set objDB = Server.CreateObject("ADODB .Connectio n")
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") %>" /> <%=objrs("speciali nterest") %></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.
[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_special
[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
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.ServerV
<input type="hidden" name="action" value="UpdateUser" />
<input type="hidden" name="parentid" value="<%=parentid %>" />
<ol>
<li>
<fieldset>
<ol>
<li>
<label for="membershipnumber">Mem
<input type="text" id="membershipnumber" name="membershipnumber" value="<%=membershipnumber
</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</la
<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/calend
buttonImageOnly: true,
defaultDate: ""
});
})
// ]]>
</script>
</li>
<li>
<label for="countrycode">Country of Residence [<%=countrycode %>]</label>
<select name="countrycode">
<%
Set objDB = Server.CreateObject("ADODB
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("countryco
<%
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_specialinteres
"Order by specialinterest;"
Set objDB = Server.CreateObject("ADODB
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") %>" /> <%=objrs("speciali
<%
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER