?
Solved

Country & Region Table from DB

Posted on 2013-01-09
5
Medium Priority
?
602 Views
Last Modified: 2013-01-10
Hello,

I am trying to create a footer for a villa company and ave an issue, which I think is easily solvable, but I simply can't get my head around it today...!

I have a table of Country Names in one field and the next field with regions, so my data looks like this:

CountryName    RegionName
France                 Cote d'Azur
France                 Dordogne
Spain                   Costa Blanca

etc.

I am trying to create a table that will list 5 colums and flow from one to the next, so the table is even in length across the page - i.e. the first column would start with the first country name, then list the regions of that country below.

I have taken the number of lines in the DB and divided by 5, so i know how many lines each column needs to be.

I have created a table with 5 columns and put a variable in each column: i.e. <%Column1Data%>, <%Column2Data%> etc. and I'm using a counter to know when a column is full and so to build up the next list, but I can't seem to assign the Column1Data variable to my DB loop...

I know it's something stupid as I've completely forgotten how to do this...

Here's my code and please don't laugh (too loudly!):

Set RS = DB.Execute("Select COUNT(ID) as NumRecs from CountryList where NumberOfProperties > 0")
NumRecs = RS("NumRecs")
RS.Close
Set RS = Nothing

Response.Write(NumRecs & "<BR>")

NumRecs = CInt(NumRecs / 5)

Response.Write(NumRecs & "<BR>")

i = 0
j = 1

ListName = "CountryList" & j
response.Write(ListName & "<BR>")

Set RS = DB.Execute("Select Distinct(Country) from CountryList Order By Country")
	While Not RS.EOF
	i = i + 1
	if i < NumRecs then
		"CountryList" & J = ("CountryList" & J) & RS("Country") & "<BR>"
		Response.Write(RS("Country") & "<BR>")
		Set RS2 = DB.Execute("Select * from CountryList where Country = '" & RS("Country") & "'")
			While Not RS2.EOF
			if i < NumRecs then
			"CountryList" & J = ("CountryList" & J) & "&nbsp;&nbsp;&nbsp;&nbsp;" & RS2("Region") & "<BR>"
			Response.Write(RS2("Region") & "<BR>")
			i = i + 1
			RS2.MoveNext
			else
			j = j + 1
			i = 0
			ListName = "CountryList" & j
			response.Write(ListName & "<BR>")
			end if
			Wend
		RS2.Close
		Set RS2 = Nothing
	RS.MoveNext
	else
	j = j + 1
	i = 0
	ListName = "CountryList" & j	
	response.Write(ListName & "<BR>")
	end if
	Wend

Open in new window


Help please!

Thanks very much!
0
Comment
Question by:Nico2011
[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
  • 2
  • 2
5 Comments
 
LVL 31

Assisted Solution

by:Wayne Barron
Wayne Barron earned 150 total points
ID: 38761716
OK, lets take a step back, and see what you are doing in the database first off.

Do you have a single table, or are you using 2 tables?
1 Table = Country
1 Table = Regions
Or, is it all in 1 single table?
0
 
LVL 53

Accepted Solution

by:
Scott Fell,  EE MVE earned 1350 total points
ID: 38761724
This could be a lot easier. Since you know there are 5 columns of data.  Just  make  a div that will have enough width to repeat 5 times before wrapping.     I am assuming you want to end up with something like this:
http://jsbin.com/ahajew/1/edit
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%

Set RS = DB.Execute("Select Country, Region from tblCountries ORDER BY Country, Region") ' make up your own sql.  We just need the 2 fields.

if not rs.eof or not rs.bof then
	arrData=rs.getrows() 'fast
end if



%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Untitled Document</title>
<style type="text/css">
.footer {
	width: 600px;
}
  div.country{
    width:600px;
    font-size:16px;
	font-weight: bold;
 }
div.region {
	
	float: left;
	width: 110px;
	margin-right: 10px;
}
</style>
</head>

<body>

<div class="footer">
<%
currCountry=""
 For r = LBound(arrData, 2) To UBound(arrData, 2)
    	Country = arrData(0, r)
		Region = arrData(1, r)
		
		if Country<>currCountry then
			response.write "<div class=""country"">"&Country&"</div>"
			currCountry=Country' 
		end if
		
		response.write "<div class=""region"">"&Region&"</div>"
next
		
%>

</div>
</body>
</html>

Open in new window

0
 

Author Closing Comment

by:Nico2011
ID: 38762526
Thanks Both.  I have given Padas more points as he came back with a full solution, although I am not using his answer, he did suggest an alternate layout for me, which I am going to use.
0
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38762946
Thank you.  If you need to have this in an actual table, just substitute divs for td and tr.  Below works if you actually have regions divisible by 5.  I would just add some logic that tests for the count of columns and if the region is changing and there are less then 5 columns, add a <td colspan=<%=theDifferenceNeeded%>> to keep the tables in line.
From there you can give the td's some class and use css.

<table>
<%
currCountry=""
colCounter=0

 For r = LBound(arrData, 2) To UBound(arrData, 2)
          Country = arrData(0, r)
            Region = arrData(1, r)
            
            if Country<>currCountry then
                       
                  response.write "<tr><td colspan=""5"" class=""country"">"&Country&"</td></tr>"
                  currCountry=Country'
                        colCounter=0
            end if
            colCounter=colCounter+1
               
                 if colCounter=1 then
                      response.write "<tr>"
                 end if
                 if colCounter>5 then
                       response.write "</tr>"
                        colCounter=0
                 end if
                   response.write "<td class=""region"">"&Region&"</td>"
next
      
%>

</table>
0
 

Author Comment

by:Nico2011
ID: 38762962
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

770 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