Single Field across two columns?

[nick0123]

Hopefully someone can assist me with this, I've searched all over and other than an inkling that maybe a counter is required I've drawn a blank.

Basically from an access database comes a list of schools and their details. At the top of the asp page is the list of all schools with links to the details further down the page.

Rather than have the school names at the top run on from each other, I'd like them to be in two columns but am unsure how to split a single field (school_name) into two columns.

<table>
<tr><td width="50%">
<a href="#<%=(rs.Fields("school_name").Value)%>">
<%=(rs.Fields("school_name").Value)%></a></td>
<td></td></table>

<%
 rs.MoveNext
loop%>

Any assistance would be gratefully received. Many thanks :)

[CertGuard]

It seems that a simple 'colspan=2' would do the trick.

<td colspan=2>Some Text</td>

no?

[nick0123]

Unfortunately no - I want to do the opposite of colspan.

I have currently a single column of names that I want to split into two columns, whereas colspan would work if it were the other way around.

Thanks for the suggestion though - any advice has got to be good :)

[CertGuard]

OHHhhh! Ok, I understand what you're getting at.

Well, there are a few ways to tackle that problem.

But here is my best solution. Get the recordset, divide the recordcount by two, fill two dynamic arrays, then fill the table.

strSQL = "SELECT Name FROM [TABLE]"
'open the recordset (however you do it)

'get the count
RCount = RS.RECORDCOUNT
5RCount = (RCount/2)

' Dimension the dynamic arrays
reDim array1(5RCount)
reDim array2(5RCount)

' Fill the arrays
For a = 1 to 5RCount
  array1(a) = RS("Name")
  RS.MOVENEXT
next

For b = 5RCount + 1 to RCount
  array1(a) = RS("Name")
  RS.MOVENEXT
next

'Show the data
%>
<table>
<%
for i = 1 to ubound(array1)
%>
  <tr>
    <td><% =array1(i) %></td><td><% =array2(i) %></td>
  </tr>
<%
</table>

NOTE: For recordsets with ODD numbers, you may need to do some other manipulations to get the data show up correctly.

HTH, Good Luck!!

[nick0123]

Thanks so much for your help and the code.

It certainly looks logical and I can follow what it's meant to be doing, but I'm getting errors all over the place when I try and implement it so after several hours of attempting to get it working I'm conceding defeat.

I can use static html to fill in what I need, given the limited number of schools and the fact that it's unlikely too many more will open in years to come. Disappointed I couldn't get it to work though as it looked like a great solution.

Thanks again Certguard

[CertGuard]

Wow, yeah, I really blew that one out of the water, didn't I ?!

My apologies. That'll be the last time I punch out a block of code in a hurry. Don't give up yet though, if it's one thing I've learned about computers (and this is the reason I'm a programmer) it's that you can make a computer do anything you want it to (within reason, of course)

I may as well explain the mistakes made earlier, but first, here is a working block of code.

<%
dbpath = server.mappath("myDatabase.mdb")

strSQL = "SELECT Name FROM [table] ORDER BY Name"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open strSQL, Conn, 1, 3

'get the count
RCount = RS.RECORDCOUNT
R5Count = cint(RCount/2)

' Dimension the dynamic arrays
reDim array1(R5Count)
reDim array2(R5Count)

' Fill the arrays
For a = 1 to R5Count
  array1(a) = RS("Name")
  RS.MOVENEXT
next

For b = 1 to R5Count
  array2(b) = RS("Name")
  RS.MOVENEXT
next

'Show the data
%><table border = 1><%
for i = 1 to ubound(array1)
%><tr>
    <td><% =array1(i) %></td><td><% =array2(i) %></td>
  </tr><%
Next
%></table>

THAT, I'm hoping will get you the results you're looking for. Here is a working example with data from my DB: http://www.certguard.com/splitColumn.asp

Now to explain the errors I created with the earlier mistakes.

1. Variables cannot start with numbers
2. Close all FOR loops with a Next statement
3. HTML Tags belong on the right of %>

[SheSaidImaPregy]

What you could also do which requires not using two different arrays and using your current code:

<%
response.write("<table><tr>")

Dim i = 0
Do While Not rs.EOF
  if i = 2 then
    response.write("</tr><tr>")
    i = 0
  end if
  response.write("<td width=""50%"">")
  response.write("<a href=""#" & rs.Fields("school_name").Value """>")
  response.write(rs.Fields("school_name").Value)
  response.write("</a></td>")
  i = i + 1
  rs.MoveNext
Loop

if i = 1 then response.write("<td></td>")
response.write("</tr></table>")
%>

What this does is first write the beginning of the table, then does the loop twice. When the loop then goes a third time, it writes a new row and sets the integer back to zero to redo the counts. If you have an odd number of records, it will write another column block then close the table. Simple and effective. Enjoy

[CertGuard]

Good solution. A few minor errors, but nothing as bad as mine was. ;)

The only difference in the final result is the way the list is presented.

A, C
B, D

versus

A, B
C, D

[SheSaidImaPregy]

yup. This all depends on how you would like it, and whether it matters. If you wish to have it listed vertically, then follow the previous example. Horizontally, follow mine. If it doesn't matter, find out what works for you

[nick0123]

Much kudos to you both for your most excellent suggestions - they now work so well (I even managed to work out how to fix data with apostrophes in it myself).

Having tried and tested both solutions I'm very happy with the result - I'm surprised there wasn't really anything like this already on the internet (at least from Google searches) as I'm sure others must have come across this issue too - of course, they could be better at programming than I!!

Thanks again CertGuard and SheSaidImaPregy - A+++++

[SheSaidImaPregy]

No problem. Glad to help. you know where we are if you need more solutions

[CertGuard]

Yes, you're very welcome nick. Glad to help!