Link to home
Start Free TrialLog in
Avatar of raymurphy
raymurphy

asked on

Fancy Formatting of recordset output

OK, I'm building my own Schema Browser for my database - and it's all working fine, but I'm looking for a way of doing some slightly fancier formatting of my recordset output (pages currently in ASP with VBScript) ...

On Page1, I get the user to select a tablename from a list of table names from the database - this tablename is then passed into Page2 (the page that I want to reformat). Given a specific table name, I show the user a list of column names for that table - with a checkbox alongside each column name so to allow the user to indicate which columns they want to include in a subsequent SQL SELECT statement. Please note that this all works OK, my question is related to how I present the list of column names to the user .....

The relevant code snippet is as follows :
<CODE SNIPPET BEGINS ...>
  TableName = Request.QueryString("TableName")
  Set cn = Server.CreateObject("ADODB.Connection")
  cn.Open "DSN"
  Set TableInfo = Server.CreateObject("ADODB.Recordset")

  ' Just access the required tablename to pull out all of the column names
  TableInfo.Open TableName, cn, adOpenForwardOnly, _
               adLockReadOnly, adCmdTable

  ' TableInfo will now contain all of the column names from the TableName
  ' Create an HTML checkbox for each field in the RecordSet (ie column name
  ' in the table) for the table name passed in.
  ' Note that TableInfo.Fields.count will give you the number of columns ..
  ' Each column name (objField.Name) can be up to 24 characters long
  For Each objField in TableInfo.Fields

       Response.Write "<INPUT TYPE=CHECKBOX NAME=" & _
                                 vbQuot & "Field" & vbQuot & _
                                 " VALUE=" & _
                                 vbQuot & objField.Name & vbQuot & _
                                 " ID=" & _
                                 vbQuot & objField.Name & ">" & _
                                 "<LABEL FOR=" & _
                                 vbQuot & objField.Name & vbQuot & _
               ">" & objField.Name & "</LABEL>" & "<BR>"
  Next
<CODE SNIPPET ENDS ...>

  Produces a list like
  [check] firstcol
  [check] secondcol
  [check] thirdcol .... and so on vertically down the screen

  I'd ideally like to have these column names nicely formatted horizontally
  across the screen as in :

  [check] firstcol       [check] secondcol     [check] thirdcol      
  [check] fourthcol    [check] fifthcol          [check] sixthcol
 and so on .....

Ideally, I'd like to see the output formatted so that the checkboxes are nicely aligned under each other - it should be noted that each column name can be
a maximum of 24 characters.

Hopefully' I've explained this requirement clearly enough - so I'd love to see a workable solution .....
Avatar of ap_sajith
ap_sajith

Use this code..

' TableInfo will now contain all of the column names from the TableName
 ' Create an HTML checkbox for each field in the RecordSet (ie column name
 ' in the table) for the table name passed in.
 ' Note that TableInfo.Fields.count will give you the number of columns ..
 ' Each column name (objField.Name) can be up to 24 characters long
 Response.write "<Table width=100% cellpadding=2 cellspacing=2>"
 tdCount=0
 For Each objField in TableInfo.Fields
      If tdCount=3 Then
            response.write "</TR>"  & vbCRLF & "<TR ALIGN='LEFT'>" & vbCRLF
            tdCount=0
      End if
      Response.Write "<TD WIDTH='20px' ALIGN='LEFT'>&nbsp;</TD>" & vbCRLF
      Response.Write "<TD WIDTH='2px' ALIGN='LEFT'>"
    Response.Write "<INPUT TYPE=CHECKBOX NAME=" & _
                              vbQuot & "Field" & vbQuot & _
                              " VALUE=" & _
                              vbQuot & objField.Name & vbQuot & _
                              " ID=" & _
                              vbQuot & objField.Name & "></TD>" & _
                              "<TD CLASS='regular' ALIGN='LEFT'>&nbsp;<LABEL FOR=" & _
                              vbQuot & objField.Name & vbQuot & _
              ">" & objField.Name & "</LABEL></TD>"
      tdCount =  tdCount + 1
 Next
 Response.write "</Table>"

Cheers!!
ASKER CERTIFIED SOLUTION
Avatar of ap_sajith
ap_sajith

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of raymurphy

ASKER

Excellent stuff, ap_sajith, your code gives me exactly what I was after - so thanks a lot for that !!!
Glad to help .. Thanks for the points..

Cheers!!