Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: Pulling data from MS Access

    A database "Country"
    A Table "Country" two Fileds, "Country" with Countries Names and "Cities" With three or four cities separated by comma.

    I have this code:

    <table>
    <%
    Response.Write "<tr >"
    Response.Write "<td align=center>" & "Number" & "</td>"
    Response.Write "<td align=center>" & "Country" & "</td>"
    Response.Write "<td align=center>" & "Cities" & "</td>"
    Response.Write "</tr>"

    SQL = "SELECT * FROM Country"
    SET RS = CreateObject("ADODB.RECORDSET")
    RS.Open SQL, "Country"
    i = 1

    WHILE NOT RS.EOF
    Country = RS("Country")
    Cities = RS("Cities")

    Response.Write "<tr >"
    Response.Write "<td>" & i & "</td>"
    Response.Write "<td>" & Country & "</td>"
    Response.Write "<td>" & Cities & "</td>"
    Response.Write "</tr>"
    End If

    RS.MOVENEXT
    i=i+1
    WEND
    %>
    </table>

    This displays all the Countries and all the cities, Now I want to write codes to display All the cities in alphabetical order, I am confused becuase Cities are listed in one filed and they have comma in between.

    Database looks like this:

    Country: USA
    Cities: New York, Los Angeles, Chicago, Washington DC, Philadelphia.

    Country: Canada
    Cities: Toronto, Montreal, Ottawa, Vancouver

    Country: Australia
    Cities: Sydney, Melbourne, Brisbane, Canberra

    and so on...

    Thanks
    Last edited by AM5786; 09-20-06 at 13:52.

  2. #2
    Join Date
    Mar 2006
    Location
    south jersey, usa
    Posts
    53
    i suggest creating an array for the cities. Populate the array with the country's cities. Resort the array alphabeticallly. write the country and the array. rinse and repeat.
    "They say Moses split the Red Sea
    I split the blunt and rolled the fat one, I'm deadly"
    -- Tupac 'Blasphemy'

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    Even before sorting it I am trying to display a list, tell me what I am doing wrong.
    Thanks
    eg.
    Cities: New York, Los Angeles, Chicago, Washington DC, Philadelphia.

    pos = InStr(Cities, ",") ' defining first comma position, pos will return 8 in our example.
    While pos <> 0
    Cities = Mid (Cities, 1, pos-1) ' moving city name from position 1 to 7, means Value New York.
    Response.Write(Cities &"<br>") ' Displaying first City name, which in our example is New York.
    Cities = Mid (Cities, pos+1, Len(Cities) - pos) ' Moving 2nd city name from current position which is 8+1 to 59-8.
    pos = InStr(Cities, ",") ' defining second position.
    Wend
    Last edited by AM5786; 09-22-06 at 14:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •